Ransack, Postgres - sort by column from linked table with excellent: true

Question

I have an application that uses the Ransack gem and I convert it from Mysql to Postgres.

In the case when the sort column is associated with the corresponding table, and for a separate parameter is set to true, Postgres generates this error:

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

The Ransack github page says that in this case, "yourself."

What is the best - any! - a strategy to handle 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!

Answer 1

There is an easier way to solve this problem. Use the ActiveRecord query to add the required columns, for example:

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

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

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

I made a pull request to update the Ransack readme.

Answer 2

I ran into the same problem, and a quick and dirty fix that works for sorting by one column will add an initializer as follows. This monkey patch adds the missing sort column to the 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