Search code examples
sqlrubysequel

How to group count and join in sequel?

I've looked through all the documentation and I'm having an issue putting together this query in Sequel.

select a.*, IFNULL(b.cnt, 0) as cnt FROM a LEFT OUTER JOIN (select a_id, count(*) as cnt from b group by a_id) as b ON b.a_id = a.id ORDER BY cnt

Think of table A as products and table B is a record indicated A was purchased.

So far I have:

A.left_outer_join(B.group_and_count(:a_id), a_id: :id).order(:count)

Essentially I just want to group and count table B, join it with A, but since B does not necessarily have any records for A and I'm ordering it by the number in B, I need to default a value.

Solution 
  • DB[:a].
  left_outer_join(DB[:b].group_and_count(:a_id).as(:b), :a_id=>:id).
  order(:cnt).
  select_all(:a).
  select_more{IFNULL(:b__cnt, 0).as(:cnt)}