Rails 6 应用中DISTINCT搭配关联表字段排序触发PG报错问题

• 这是PostgreSQL的语法约束导致的错误，和Rails版本无关：使用SELECT DISTINCT查询时，所有排序用到的表达式必须出现在SELECT的返回字段列表中。
• 你原查询调用.distinct生成了SELECT DISTINCT "tables".*，仅返回tables表的全部字段，而排序使用的occupants.status不在返回字段内，数据库无法判断去重后的行该按哪个值排序，因此抛出校验错误。
• 生成的计数SQL将整个查询套入子查询执行COUNT(*)，内层子查询仍遵守上述约束，错误会直接触发。

根据你的业务场景（需要关联occupants表排序、同时保证table不重复），推荐以下两种实现：

方案1：使用PostgreSQL原生DISTINCT ON语法（适配现有JOIN逻辑） #

DISTINCT ON是PG专属语法，可按指定字段（这里是tables.id）对结果去重，仅保留每个table的第一条匹配行，同时满足DISTINCT的字段要求。
替换原查询末尾的.distinct.order('occupants.status')为以下代码：

.select(
  "DISTINCT ON (tables.id) tables.*, occupants.status"
)
# 排序需先按去重字段排序，再按业务需要的状态排序
.order("tables.id, occupants.status")

修改后完整查询示例：

@tables = policy_scope(Table).active
                             .where(private: false)
                             .left_outer_joins(:occupants)
                             .or(current_user.active_tables
                             .left_outer_joins(:occupants))
                             .or(policy_scope(Table)
                             .active
                             .where(private: true)
                             .left_outer_joins(:occupants)
                             .where(occupants: current_user.current_seated_occupant))
                             .or(policy_scope(Table)
                             .active
                             .where(private: true)
                             .left_outer_joins(:occupants)
                             .where(occupants: current_user.occupants.invited))
                             .select(
                               "DISTINCT ON (tables.id) tables.*, occupants.status"
                             )
                             .order("tables.id, occupants.status")

注意：PostgreSQL要求DISTINCT ON的字段必须是ORDER BY子句的最左前缀，因此必须先按tables.id排序，再按业务需要的状态排序。如果需要按特定优先级的occupant状态排序（比如优先显示已就坐的桌台），可以调整第二个排序字段的顺序，比如order("tables.id, occupants.status DESC")。

方案2：关联子查询取当前用户的occupant状态（性能更优，避免行膨胀） #

如果你的业务需求是仅按当前用户关联的occupant状态排序，不需要其他用户的occupant数据，推荐用关联子查询替代JOIN后的字段引用，避免多个occupant导致的行重复，同时减少查询开销：

@tables = policy_scope(Table).active
                             .where(private: false)
                             .left_outer_joins(:occupants)
                             .or(current_user.active_tables
                             .left_outer_joins(:occupants))
                             .or(policy_scope(Table)
                             .active
                             .where(private: true)
                             .left_outer_joins(:occupants)
                             .where(occupants: current_user.current_seated_occupant))
                             .or(policy_scope(Table)
                             .active
                             .where(private: true)
                             .left_outer_joins(:occupants)
                             .where(occupants: current_user.occupants.invited))
                             # 直接子查询取当前用户的occupant状态作为排序字段，使用参数绑定避免注入风险
                             .select(
                               "DISTINCT tables.*, " \
                               "(SELECT status FROM occupants WHERE table_id = tables.id AND user_id = ? LIMIT 1) AS occupant_status",
                               current_user.id
                             )
                             .order("occupant_status")

• 原查询中如果current_user.current_seated_occupant为nil，会生成"tables"."id" = nil的无效条件，建议拼接OR逻辑前先做判空处理，避免无意义的查询开销。
• 不要直接使用.group('tables.id')解决该问题，PostgreSQL要求GROUP BY时所有非聚合的SELECT字段都要加入GROUP BY子句，反而会引入其他校验错误。

内容的提问来源于stack exchange，提问作者Risky leopard