gpt4 book ai didi

ruby-on-rails - squeel 中的嵌套查询

转载 作者:数据小太阳 更新时间:2023-10-29 08:05:29 28 4
gpt4 key购买 nike

简短版:如何在 squeel 中编写此查询?

SELECT OneTable.*, my_count
FROM OneTable JOIN (
SELECT DISTINCT one_id, count(*) AS my_count
FROM AnotherTable
GROUP BY one_id
) counts
ON OneTable.id=counts.one_id

长版:rocket_tag是一个向模型添加简单标记的 gem。它添加了一个方法 tagged_with。假设我的模型是 User,具有 ID 和名称,我可以调用 User.tagged_with ['admin','sales']。在内部它使用这个 squeel 代码:

select{count(~id).as(tags_count)}
.select("#{self.table_name}.*").
joins{tags}.
where{tags.name.in(my{tags_list})}.
group{~id}

生成此查询:

SELECT count(users.id) AS tags_count, users.*
FROM users INNER JOIN taggings
ON taggings.taggable_id = users.id
AND taggings.taggable_type = 'User'
INNER JOIN tags
ON tags.id = taggings.tag_id
WHERE tags.name IN ('admin','sales')
GROUP BY users.id

一些 RDBMS 对此很满意,但 postgres 提示:

ERROR: column "users.name" must appear in the GROUP BY
clause or be used in an aggregate function

我认为编写查询的更合适的方式是:

SELECT users.*, tags_count FROM users INNER JOIN (
SELECT DISTINCT taggable_id, count(*) AS tags_count
FROM taggings INNER JOIN tags
ON tags.id = taggings.tag_id
WHERE tags.name IN ('admin','sales')
GROUP BY taggable_id
) tag_counts
ON users.id = tag_counts.taggable_id

有什么方法可以用 squeel 表达吗?

最佳答案

我不知道 Squeel,但您看到的错误可以通过升级 PostgreSQL 来修复。

Some RDBMSs are happy with this, but postgres complains:

ERROR: column "users.name" must appear in the GROUP BY clause or be used in an aggregate function

从 PostgreSQL 9.1 开始,一旦您在 GROUP BY 中列出了一个主键,您就可以跳过该表的其他列,并仍然在 SELECT 列表中使用它们。 release notes for version 9.1告诉我们:

Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause


顺便说一句,您的替代查询可以简化,额外的 DISTINCT 将是多余的。

SELECT o.*, c.my_count
FROM onetable o
JOIN (
SELECT one_id, count(*) AS my_count
FROM anothertable
GROUP BY one_id
) c ON o.id = counts.one_id

关于ruby-on-rails - squeel 中的嵌套查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/9763538/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com