[Question] Join a subquery

[shlajin]

First of all, that's an awesome gem and I'm super happy I found it 🔥 🔥 🔥

The "parent" gem (squeel) has an ability of joining a subquery, example from their readme:

subquery = OrderItem.group(:orderable_id).select { [orderable_id, sum(quantity * unit_price).as(amount)] }
Seat.joins { [payment.outer, subquery.as('seat_order_items').on { id == seat_order_items.orderable_id}.outer] }.
              select { [seat_order_items.amount, "seats.*"] }
# => SELECT "seat_order_items"."amount", seats.*
#    FROM "seats"
#    LEFT OUTER JOIN "payments" ON "payments"."id" = "seats"."payment_id"
#    LEFT OUTER JOIN (
#      SELECT "order_items"."orderable_id",
#             sum("order_items"."quantity" * "order_items"."unit_price") AS amount
#      FROM "order_items"
#      GROUP BY "order_items"."orderable_id"
#    ) seat_order_items ON "seats"."id" = "seat_order_items"."orderable_id"

I wonder if there's an ability to achieve the same thing with baby squeel?

[Looooong]

I believe this can be worked out without subquery:

# You don't seem to use `payment` so I ommit it
Seat.joining { order_items.outer }.grouping { [id, order_items.orderable_id] }
.selecting { [sum(order_items.quantity * order_items.unit_price).as('amount'), 'seats.*'] }

This requires OrderItem to be Seat's association - either with polymorphic behaviour or with explicit foreign key.

[shlajin]

Provided example is taken from the squeel readme for illustration purposes. I can't deny that many SQL queries can be transformed from subquery based to non-subquery based, but in many cases subquery is just easier to write. Would be awesome to have that ability / choice :)

[rzane]

Yeah, I agree that it would be nice for baby squeel to provide this functionality, but I'm having trouble finding the time to add new features. I'd be thrilled to receive a pull request!