Subqery on lucid model

[melokki]

Hello everyone

I have the following query writen in sql that returns me what I want

select *
  from order_statuses os
       inner join (
            select order_id, max(updated_at) as updated_at from order_statuses group by order_id
       ) tbl
      on os.order_id = tbl.order_id and os.updated_at = tbl.updated_at
  where status = 'PLACED'
  order by os.order_id;

I have no idea on how us this query using Lucid models

I could use it as it is, but I would like to also preload some other relationships and it would be easier if I am using Lucid.

Any suggestions?

[melokki]

I think I cracked it

I wrote something like this:

await Model.query()
        .joinRaw(
          Database.raw(
            `inner join (select order_id, max(updated_at) as updated_at from order_statuses group by order_id) tbl on order_statuses.order_id = tbl.order_id and order_statuses.updated_at = tbl.updated_at`
          )
        )
        .preload('order', (query) => {
          query.preload('user')
        })
        .paginate(request.input('page', 1), request.input('limit', 25))

and now I am able to use the full power of Lucid models :)