squeel中的嵌套查询 [英] Nested query in squeel

问题描述

简短版本：如何使用squeel编写此查询？

Short version: How do I write this query in 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 是为模型添加简单标记的瑰宝。它添加了方法 tagged_with 。假设我的模型是 User ，具有ID和名称，我可以调用 User.tagged_with ['admin'，'sales'] 。在内部，它使用以下代码：

---

Long version: rocket_tag is a gem that adds simple tagging to models. It adds a method tagged_with. Supposing my model is User, with an id and name, I could invoke User.tagged_with ['admin','sales']. Internally it uses this squeel code:

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抱怨：

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

我相信写查询的一种更可接受的方式是：

I believe a more agreeable way to write the query would be:

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，但是您看到的错误可以修复通过升级PostgreSQL。

I wouldn't know about Squeel, but the error you see could be fixed by upgrading PostgreSQL.

一些RDBMS对此感到满意，但postgres抱怨：

Some RDBMSs are happy with this, but postgres complains:

错误：列 users.name必须出现在GROUP BY子句中，或者必须在聚合函数中使用

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

从PostgreSQL 9.1开始，一旦您在GROUP BY中列出了主键，就可以跳过该表的其他列，并仍在SELECT列表中使用它们。 版本9.1的发行说明告诉我们：

Starting with PostgreSQL 9.1, once you list a primary key in the GROUP BY you can skip additional columns for this table and still use them in the SELECT list. The release notes for version 9.1 tell us:

在GROUP BY子句中指定主
键时，允许查询目标列表中的非GROUP BY列

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

---

BTW，您的替代查询可以得到简化，另外需要 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

这篇关于squeel中的嵌套查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！