Struggling with SQL query


#1

I’m struggling with an SQL query and looking for advice. Hopefully my intention is obvious …

Order.query()
    .transacting(trx)
    .with("order_segments")
    .with("order_segments.order_lines", ( builder ) => {

        builder.where('status', 'ROUTING_OK')
    })
    .with("order_segments.order_lines.customisations")
    .where("id", orderId)
    .first();

My problem is that order_lines are not being filtered by the nested where clause. Can anybody please advise on my syntax issue?


#2

How about

Order.query()
    .transacting(trx)
    .with("order_segments")
    .with("order_segments.order_lines", ( builder ) => {
        builder
          .where('status', 'ROUTING_OK')
          .with("customisations")
    })
    .where("id", orderId)
    .first();

#3

Thanks for the quick response @virk, worked like a charm.

You’re a star.