How to form a WHERE condition matching multiple ORDER BY items?

Question

I have a model Trade that has columns traded_at, price, amount.

And there is a default_order for it like this.

scope :default_order, -> {
  order(traded_at: :desc, price: :asc, amount: :desc)
}

I want to filter the trades by using this order clause.

This is my code for it without order clause.

scope :newer_than, ->(trade) {
  where(<<-SQL)
    traded_at > '#{trade.traded_at.to_s(:db)}' OR
    (traded_at = '#{trade.traded_at.to_s(:db)}' AND price < #{trade.price}) OR
    (traded_at = '#{trade.traded_at.to_s(:db)}' AND price = #{trade.price} AND amount > #{trade.amount})
  SQL
}

When I add one more ORDER BY item, I need to add 4 conditions in WHERE clause. Etc. The number grows binomially.
I think it's not efficient. Is there a more efficient way to translate multiple ORDER BY expressions into a matching WHERE clause?

Erwin Brandstetter · Accepted Answer · 2019-05-06T17:29:48.683

Postgres supports the concept of ROW values.

SELECT * FROM tbl WHERE (a, b, c) > (1, 2, 3)  -- not for you!

But there is a hitch: every field in the row is compared in the same sense of ordering. Your case has mixed sort order (ASC / DESC), which is a showstopper. But if price is a numeric data type (seems like a safe bet) - which has a negator defined, there is a workaround:

... WHERE (a, -b, c) > (1, -2, 3)  -- for you

So:

where(<<-SQL)
    (traded_at, -price, amount)
  > ('#{trade.traded_at.to_s(:db)}', #{trade.price} * -1 , #{trade.amount})
  SQL

Or pass trade.price readily negated and drop * -1.

Can even be supported with a multicolumn expression index!

Note: None of this works properly with NULL values since those never qualify in your WHERE clause.

How to form a WHERE condition matching multiple ORDER BY items?

1 Answers1