Ransack, Postgres - Sort on Column from Associated Table with Distinct: True

using order_by expression and distinct true in rails 6 with postgresql

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.

Ransack sort on count of HABTM or HMT associated records

Given you have your entities queried with the above scope, for example your index query always has:

# controller
@search = Theme.ransack(params[:q])
@themes = @search.result(distinct: true).with_quotes_count

Have a try of:

# model
ransacker :quotes_count_sort do
    Arel.sql('quotes_count')
end

And use the name of the sort in sort_link?

Make an OR with ransack

Try the following:

Position.search( {:description_or_code_cont => field}).result(:distinct => true).to_sql

---

Related Topics