using order_by expression and distinct true in rails 6 with postgresql

i am trying to achieve a very specific query and can't seem to find out how.

I am using ransack gem for filtering, and i have (distinct: true) set, because i want to Filter and Sort at the same time, which brings me problems i am trying to fix.

I have a model called A, A has a column called 'status'. 'status' can be 'approved', 'approved by manager', 'pending' or 'rejected'.

I want keep the database order default, but wanted to return it following the custom order

STATUS_ORDER = ['approved_by_leader', 'pending', 'approved', 'rejected']

for which i used this piece of code:

  def self.order_by_status
    ret = "CASE"
    STATUS_ORDER.each_with_index do |s, i|
      ret << " WHEN state LIKE '#{s}' THEN #{i}"
    end
    ret << " END ASC"
  end

Then, i just defined the scope:

scope :by_status_priority, -> { order(order_by_status) }

And this is the scope i use in my controller. This would work, except i want to keep (distinct: true) in ransack, to avoid duplicate results when using queries.

The error i get goes:

"PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list\nLINE 1:

I could use some suggestion in how to handle this. I need to keep (distinct: true) or have an alternative way of avoiding duplicates.

I will also be using nested sorting later on, which is something (distinct: true) can't work with, but that can be fixed by using includes and joins in my controller, so that won't be a problem. More about this here: Ransack, Postgres - sort on column from associated table with distinct: true

Solution

My initial suggestion is to create a Status model and table with status and priority columns and then create a formal relationship with your existing model as this will allow simple addition and reordering in the future without any code change.

I lieu of this, the error is fairly clear:

for SELECT DISTINCT, ORDER BY expressions must appear in select list

This means that when using SELECT DISTINCT you can only order by columns that appear in the SELECT clause

To solve this I would recommend the following:

remove "ASC" from the self.order_by_status method (so that we can reuse the CASE statement
then update the scope as follows

scope :by_status_priority, -> { 
  select(arel_table[Arel.star],Arel.sql(order_by_status).as('status_order') )
    .order(Arel.sql(order_by_status).asc) 
}

TL;DR Explanation of ActiveRecord and Arel

You will see Arel a few times in the above. Arel is the underlying query assembler for rails and offers a significant amount of flexibility allowing for much more composable queries.

Every ActiveRecord object exposes its Arel::Table via a method called arel_table. This part arel_table[Arel.star] will be assembled as "table_name"."*" (select everything)

This part Arel.sql(order_by_status) will return an Arel::Nodes::SqlLiteral which allows us to chain aliasing (as in as('status_order')) as well as ordering (as in .asc).

We could even build your entire CASE statement using Arel via

def self.order_by_status
  STATUS_ORDER.each_with_index.inject(Arel::Nodes::Case.new) do |cassette, (s, i)|
    cassette.when(arel_table[:status].eq(s)).then(i)
  end
end

This will allow you to get rid of the Arel.sql wrappers in by_status_priority (which are for raw sql strings) so the scope can now become

scope :by_status_priority, -> { 
  select(arel_table[Arel.star],order_by_status.as('status_order'))
    .order(order_by_status.asc) 
}

ActiveRecord provides a lot of convenience methods to expose the Arel query interface e.g. select, where, order, joins, etc. but it would be impossible to expose everything in such a simple way as to provide an easy top level DSL; however almost everyone of these "top-level" methods will accept Arel arguments without issue allowing you to build any query you could possibly want. If it is valid SQL then Arel can construct it.