ruby-on-rails PG：：无效列引用：错误：对于SELECT DISTINCT，ORDER BY表达式必须出现在选择列表中

7fyelxc5 于 2022-12-01 发布在 Ruby

关注(0)|答案(3)|浏览(247)

我有一长串的关联、连接、order by等等，最终都是从我的一个Rails模型中选择。在一天结束的时候，我需要结果是唯一的和排序的。我不在乎SELECT语句中使用了什么列，ORDER BY中出现了什么，等等。（这些都根据用户选择的过滤选项而改变），我只关心查询中的顶级模型/表是唯一的（基于id）。
作为背景，widgets是主表，我们正在与widget_steps连接，这是在Rails 3中（公司正试图尽快升级，但这是他们目前所坚持的）
下面是生成的查询和错误：

PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ...completed_at" IS NULL)) ORDER BY sequential DESC, widget_s...
                                                             ^
: SELECT  DISTINCT "widgets".* FROM "widgets" INNER JOIN "widget_steps" ON "widget_steps"."widget_id" = "widgets"."id" INNER JOIN "widget_steps" "active_steps_widgets" ON "active_steps_widgets"."id" = "widgets"."active_widget_step_id" WHERE "widgets"."account_id" = 1 AND "widgets"."completed_at" IS NULL AND (("widgets"."sequential" = 't' AND "widget_steps"."assigned_to" = 5 AND "widget_steps"."id" = "widgets"."active_widget_step_id" AND "widget_steps"."completed_at" IS NULL) OR ("widgets"."sequential" = 'f' AND "widget_steps"."assigned_to" = 5 AND "widget_steps"."completed_at" IS NULL)) ORDER BY sequential DESC, widget_steps.name ASC LIMIT 10 OFFSET 0
Completed 500 Internal Server Error in 52.3ms

ActiveRecord::StatementInvalid - PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ...completed_at" IS NULL)) ORDER BY sequential DESC, widget_s...
                                                             ^
: SELECT  DISTINCT "widgets".* FROM "widgets" INNER JOIN "widget_steps" ON "widget_steps"."widget_id" = "widgets"."id" INNER JOIN "widget_steps" "active_steps_widgets" ON "active_steps_widgets"."id" = "widgets"."active_widget_step_id" WHERE "widgets"."account_id" = 1 AND "widgets"."completed_at" IS NULL AND (("widgets"."sequential" = 't' AND "widget_steps"."assigned_to" = 5 AND "widget_steps"."id" = "widgets"."active_widget_step_id" AND "widget_steps"."completed_at" IS NULL) OR ("widgets"."sequential" = 'f' AND "widget_steps"."assigned_to" = 5 AND "widget_steps"."completed_at" IS NULL)) ORDER BY sequential DESC, widget_steps.name ASC LIMIT 10 OFFSET 0:

为什么这是一个东西？postgres认为什么是如此模棱两可？为什么像这样的查询在MySQL中总是工作正常，但使postgres窒息。
我试过：
1.在链的末尾指定.select([everything mentioned in the order by]).uniq
1.在链的末尾指定.uniq而不执行自定义选择
1.编写一些自定义AREL，尝试将所有这些内容嵌入子查询中，然后在其外部执行.uniq或.order（无法使其正常工作）
1.在postgres外部执行.uniq（这会因为分页而中断......由于删除了重复项，您可能会得到一些只有1或2个项目的页面）
1.哭泣

ruby-on-rails

来源：https://stackoverflow.com/questions/42846286/pginvalidcolumnreference-error-for-select-distinct-order-by-expressions-mus

3条答案

按热度按时间

fzwojiic1#

您需要将widget_steps.name添加到所选列的列表中：

SELECT DISTINCT "widgets".*,
                "widget_steps.name"
FROM "widgets"
INNER JOIN "widget_steps" ON "widget_steps"."widget_id" = "widgets"."id"
INNER JOIN "widget_steps" "active_steps_widgets" ON "active_steps_widgets"."id" = "widgets"."active_widget_step_id"
WHERE "widgets"."account_id" = 1
  AND "widgets"."completed_at" IS NULL
  AND (("widgets"."sequential" = 't'
        AND "widget_steps"."assigned_to" = 5
        AND "widget_steps"."id" = "widgets"."active_widget_step_id"
        AND "widget_steps"."completed_at" IS NULL)
       OR ("widgets"."sequential" = 'f'
           AND "widget_steps"."assigned_to" = 5
           AND "widget_steps"."completed_at" IS NULL))
ORDER BY sequential DESC,
         widget_steps.name ASC
LIMIT 10
OFFSET 0

这不会更改查询的逻辑，并且可以正常工作。
在Rails中，您可以使用select方法来设置所选列的列表：

Widget.select('"widgets".*, "widget_steps.name"')

希望这能有所帮助。

赞(0）回复(0）举报 2022-12-01

huwehgph2#

出现此错误的另一个选项可能适用于某些情况，例如has_and_belongs_to_many类型关系，该选项是取消顺序的作用域：

class Resource < ApplicationRecord
  has_and_belongs_to_many :things
  scope :with_blue_things, -> { joins(:things).where(:things => {:color => :blue}).unscope(:order).distinct }

这应该可以让你做所有这些

Resource.with_blue_things
Resource.with_blue_things.count
Resource.with_blue_things.order(:name).count

赞(0）回复(0）举报 2022-12-01

wsewodh23#

建议：
您可以在Widget类中添加has_many :steps，并使用Widget.includes（：steps）执行带有Order子句的查询，该子句按widget_steps列排序

赞(0）回复(0）举报 2022-12-01

我来回答

ruby-on-rails PG：：无效列引用：错误：对于SELECT DISTINCT，ORDER BY表达式必须出现在选择列表中

3条答案

相关问题

热门标签

最新问答