Ransack, Postgres - sort on column from associated table with distinct: true

Question

I have an app that uses the Ransack gem and I'm converting it from Mysql to Postgres.

In an instance where the sort column is from an associated table and the distinct option is set to true, Postgres throws this error:

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

The Ransack github page says that, in a case like this, "you're on your own."

What's the best - any! - strategy for handling this scenario?

q = Contact.includes(:contact_type).search
q.sorts = ['contact_type_name asc']
q.result(distinct: true)
PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

Thanks!

Apothecary

Answer 1

There is a simpler way to solve this problem. Use an ActiveRecord joins query or select query to add the columns needed, for example:

q = Contact.search
q.sorts = ['contact_type_name asc']
q.result(distinct: true).
  includes(:contact_type).
  joins(:contact_type)

Alternatively, if you only want to select a few columns, you could do:

q = Contact.search
q.sorts = ['contact_type_name asc']
q.result(distinct: true).
  select('contacts.*, contact_type.name')

I've done a pull request to update Ransack's readme.

Beautiful

Thanks. What if in addition to sorting on this, I am also searching on contact_type, using a different property. I get the following error. missing FROM-clause entry for table . How would I go about solving that? – Chord

Even 5 years later, you still make people save hundreds hour of work. Thx. – Murine

Answer 2

I've just faced with the same problem and a quick and dirty fix which works for single column sorting would be adding an initializer as follows. This monkey patch adds the missing sorting column into select statement.

module Ransack
  module Adapters
    module ActiveRecord
      class Context < ::Ransack::Context
        def evaluate(search, opts = {})
          viz = Visitor.new
          relation = @object.where(viz.accept(search.base))
          if search.sorts.any?
            _vaccept = viz.accept(search.sorts)
            table  = _vaccept.first.expr.relation.name
            column = _vaccept.first.expr.name
            relation = relation.except(:order).reorder(_vaccept).select("#{@default_table.name}.*, #{table}.#{column}")
          end
          opts[:distinct] ? relation.distinct : relation
        end
      end
    end
  end
end

Guardado

If I add this fix, I get "NoMethodError - undefined method `paginate' for #<Arel::Nodes::Distinct:0xa3a0ebc>" when I try to paginate the results... – Phellem

If I replace relation.distinct with relation.uniq, everything seems to work fine... (Rails 3.2) – Phellem

I literally dropped this in and it worked. Rails 4.2.6. Thanks Eren! You should submit a PR. – Shulock

Answer 3

One of the workarounds:

class Contact < ApplicationRecord
   belongs_to :contact_type
   scope :sort_by_contact_type_name_asc, lambda { select('contacts.*, contact_types.name').left_joins(:contact_type).order(СontactType.arel_table[:name].asc) }
   scope :sort_by_contact_type_name_desc, lambda { select('contacts.*, contact_types.name').left_joins(:contact_type).order(СontactType.arel_table[:name].desc) }
end
 
q = Contact.search
q.sorts = ['contact_type_name asc']
q.result(distinct: true)

Sources:

https://github.com/activerecord-hackery/ransack/issues/429 https://activerecord-hackery.github.io/ransack/getting-started/simple-mode/#:~:text=class%20Person%20%3C%20ActiveRecord%3A%3ABase

Kolinsky