Ransack，Postgres - 对关联表中的列进行排序，distinct: true [英] Ransack, Postgres - sort on column from associated table with distinct: true

问题描述

我有一个使用 Ransack gem 的应用程序，我正在将它从 Mysql 转换为 Postgres.

在排序列来自关联表并且 distinct 选项设置为 true 的实例中，Postgres 抛出此错误:

PG::InvalidColumnReference: 错误:对于 SELECT DISTINCT，ORDER BY 表达式必须出现在选择列表中

Ransack github 页面说，在这种情况下，你要靠自己."

什么是最好的 - 任何！- 处理这种情况的策略?

q = Contact.includes(:contact_type).searchq.sorts = ['contact_type_name asc']q.result(不同:真实)PG::InvalidColumnReference: 错误:对于 SELECT DISTINCT，ORDER BY 表达式必须出现在选择列表中

谢谢！

解决方案

有一个更简单的方法来解决这个问题.使用 ActiveRecord 连接查询或选择查询添加所需的列，例如:

q = Contact.searchq.sorts = ['contact_type_name asc']q.result(不同:真实).包括(:contact_type).加入(:contact_type)

或者，如果您只想选择几列，您可以这样做:

q = Contact.searchq.sorts = ['contact_type_name asc']q.result(不同:真实).选择('联系人.*，contact_type.name')

我已经完成了一个拉取请求来更新 Ransack 的自述文件.>

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!

解决方案

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.

这篇关于Ransack，Postgres - 对关联表中的列进行排序，distinct: true的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！