CODEWITHSUNDEEP
sqlpostgresqlactiverecord
Dan Tappin
Dan Tappin

Reputation: 3032

Order on SUM with left outer join

I have a Rails app and I am using a scope for a sort like this:

group('"expenditures"."id"').order('SUM("expenditure_items"."amount") ' +  direction)

There is an outer_left_join(:expenditure_items) before this. The issue is that when there are no expenditure_items the SUM sorts the other non zero sum items first. It looks like this:

I want the $0 items first.

Upvotes: 1

Views: 48

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176034

You could use COALESCE:

group('"expenditures"."id"').order('COALESCE(SUM("expenditure_items"."amount"),0) '+ direction)

or NULLS FIRST/LAST clause:

SUM("expenditure_items"."amount")  NULLS FIRST

Upvotes: 2

Related Questions