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?

CodePudding user response：

Try something like this

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

CodePudding user response：

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."bookings".*, BB."status" AS "last_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."last_status" IN ('PENDING', 'APPROVED')