Replace subquery with join

Question

I have two tables

flats

   id - primary key

   title - text

flats_reservations

   id - primary key

   flat_id - foreign key to Flats(id)

   creator_id - foreign key to users(id)

   is_reserved - boolean

   created_at - current_timestamp

If flats_reservations has these rows

id=1, flat_id=12, creator_id=5, is_reserved=1, created_at=2016-10-10 00:00:00

id=2, flat_id=12, creator_id=5, is_reserved=0, created_at=2016-10-10 00:00:05

id=3, flat_id=12, creator_id=4, is_reserved=1, created_at=2016-10-10 00:00:10

Then only user with id=4 can remove reservation (e.g. insert following row (is_reserved=0)

id=4, flat_id=12, creator_id=4, is_reserved=0, created_at=2016-10-10 00:00:10

No other user can make this.

How can I select list of all flats with their current (e.g. latest) reservation status?

Simple, but not effective way - for every flat

select is_reserved from flat_reservations where flat_id={id} order by created_at desc limit 1

This can be done with subquery too

select flats.id, flats.title, (select is_reserved from flat_reservations where flat_id=flats.id order by created_at desc limit 1) as is_reserved from flats;

But how can I make this more effective?

Answer 1

This is essentially your query:

select f.id, f.title,
       (select is_reserved
        from flat_reservations fr
        where fr.flat_id = f.id
        order by fr.created_at desc
        limit 1
       ) as is_reserved
from flats f;

This is a fine way to do what you want. You want an index on flat_reservations(flat_id, created_at, is_reserved) if performance is an issue.