How to use column derived from SELECT subquery elsewhere in SQL query

I have two database tables that look something like this:

+----------+      +------------------+
| Bookings |----->| Booking_Statuses |
+----------+      +------------------+

Context:

• Booking_Statuses is used to retain a history of the various stages that a booking can go through, such as PENDING or ACCEPTED, so I can construct a timeline of events for the booking. One Booking has many Booking_Statuses.
• I’m using PostgreSQL.

I am trying to query the bookings for a particular user, along with its latest status:

SELECT "bookings".*,
       (
            SELECT "status"
            FROM "booking_statuses"
            WHERE "bookings"."id" = "booking_statuses"."booking_id"
            ORDER BY "created_at" DESC
            LIMIT 1
       ) AS "last_status"
FROM "bookings"
WHERE "user_id" = $1
AND "last_status" IN ('PENDING', 'APPROVED')

It all works great, until I add the last line – AND "last_status" IN ('PENDING', 'APPROVED'), at which point I get the error: column "last_status" does not exist.

How can I use last_status outside of the SELECT subquery?

Answer

Can you use something like this? You find your latest status id and then join it to bookings and then you can get the fields you need.

SELECT B.*, BB."status"
FROM "bookings" B
JOIN "booking_statuses" BB ON BB.id = (SELECT id
            FROM "booking_statuses"
            WHERE B."id" = "booking_statuses"."booking_id"
            ORDER BY "created_at" DESC
            LIMIT 1)
WHERE "user_id" = $1
AND BB."status" IN ('PENDING', 'APPROVED')