在 Rails 中查询属性组合 - 如何将 9 个查询变成 1 个？

Question

有什么办法可以将这些查询压缩为一个查询吗？

我正在查询 1 个表的 4 个属性：author_id、status、category_id 和 site_id。目标是，我想找到每种情况下的最新记录（原因是增加报告计数器）：

# author_id
SELECT * FROM posts WHERE posts.author_id = 10 ORDER BY created_at desc LIMIT 1

# author_id + status
SELECT * FROM posts WHERE posts.author_id = 10 AND posts.status = 'draft' ORDER BY created_at desc LIMIT 1
SELECT * FROM posts WHERE posts.author_id = 10 AND posts.status = 'published' ORDER BY created_at desc LIMIT 1

# author_id + site_id
SELECT * FROM posts WHERE posts.author_id = 10 AND posts.site_id = 1 ORDER BY created_at desc LIMIT 1

# author_id + status + site_id
SELECT * FROM posts WHERE posts.author_id = 10 AND posts.status = 'draft' AND posts.site_id = 1 ORDER BY created_at desc LIMIT 1
SELECT * FROM posts WHERE posts.author_id = 10 AND posts.status = 'published' AND posts.site_id = 1 ORDER BY created_at desc LIMIT 1

# author_id + site_id + category_id
SELECT * FROM posts WHERE posts.author_id = 10 AND posts.site_id = 1 AND posts.category_id = 7 ORDER BY created_at desc LIMIT 1

# author_id + status + site_id + category_id
SELECT * FROM posts WHERE posts.author_id = 10 AND posts.status = 'draft' AND posts.site_id = 1 AND posts.category_id = 7 ORDER BY created_at desc LIMIT 1
SELECT * FROM posts WHERE posts.author_id = 10 AND posts.status = 'published' AND posts.site_id = 1 AND posts.category_id = 7 ORDER BY created_at desc LIMIT 1

这是 9 个查询。我正在 Rails 中执行此操作，因此目标是通过一次 Post.all(:conditions => x) 调用获得 9 个 Post 模型，然后我可以过滤在 ruby​​ 代码中通过它们。这可能吗？这里最好的方法是什么？

更新

以下大致是我用来生成这些查询的代码：

https://gist .github.com/965233

Answer 1

从 SQL 端，您可以使用 UNION 来组合具有匹配字段列表的查询。添加一个字段对它们进行排序，并将其全部包装在另一个查询中以按其排序。然后您可以迭代结果集。

SELECT * FROM 
(    
  SELECT 1 QNO, * FROM SOMETABLE WHERE A=1
  UNION ALL
  SELECT 2 QNO, * FROM SOMETABLE WHERE A=1 AND B=2
  UNION ALL
  SELECT 3 QNO, * FROM SOMETABLE WHERE A=2 AND B=4 AND C=11
) 
ORDER BY QNO;