mysql - 如何通过单个 SQL 查询检索同一个数据库表中的 "common"条记录？

Question

我正在使用 Ruby on Rails(版本 3.2.2)和 mysql2(版本 0.3.11 - MySQL 版本 5.2.38)。在讨论“How to retrieve associated objects / records that a set of users have in "common" through a association table”* 时，我发现自己处于“特定”情况下，我必须通过运行 单个 来查找“关联对象/记录”** - 出于性能原因 - SQL 查询在关联数据库表上。特别是，我想这样做是为了检索仅之前提到的“关联对象/记录”，其中user_id(代表外键“指向”User 对象)和article_id(表示外键)指向”Article 对象)列值相同(也就是说，article_id 和 user_id 是“常见”)。

在我的案例中，我如何/应该检索“关联对象/记录”(也许，通过使用 Ruby on Rails 和/或 SQL/数据库“方式”/“上下文”中的某些工具)？

---

* 具体来说，在 @Andrew Marshall 发布了他/她的回答之后。

** 注意:那些“关联对象/记录”与 has_many :through Ruby on Rails ActiveRecord::Associations 相关在 linked question 中描述.

Answer 1

我认为这是一种提取所需内容的原始 SQL 方法:

select * from articles_users where article_id in (select articles.id from articles inner join articles_users on articles_users.article_id = articles.id where user_id = 1) and user_id = 2;

在 Rails 中可以替换用户 ID 的地方:

ArticlesUser.find_by_sql(["select * from articles_users where article_id in (select articles.id from articles inner join articles_users on articles_users.article_id = articles.id where user_id = ?) and user_id = ?", @user1.id, @user2.id])

或多个 ids::

ArticlesUser.find_by_sql(["select * from articles_users where article_id in (select articles.id from articles inner join articles_users on articles_users.article_id = articles.id where user_id = ?) and user_id IN (?)", @user1.id, [@user2.id,@user3.id]])

因此，根据示例数据(来自您的其他问题):

mysql> select * from articles_users;
+----+---------+------------+
| id | user_id | article_id |
+----+---------+------------+
|  1 |       1 |          1 |
|  2 |       1 |          2 |
|  3 |       1 |          3 |
|  4 |       2 |          1 |
|  5 |       2 |          2 |
|  6 |       3 |          1 |
|  7 |       3 |          3 |
|  8 |       4 |          4 |
+----+---------+------------+
8 rows in set (0.00 sec)

它将返回如下值:

mysql> select * from articles_users where article_id in (select articles.id from articles inner join articles_users on articles_users.article_id = articles.id where user_id = 1) and user_id = 2;
+----+---------+------------+
| id | user_id | article_id |
+----+---------+------------+
|  4 |       2 |          1 |
|  5 |       2 |          2 |
+----+---------+------------+
2 rows in set (0.00 sec)

mysql> select * from articles_users where article_id in (select articles.id from articles inner join articles_users on articles_users.article_id = articles.id where user_id = 1) and user_id = 3;
+----+---------+------------+
| id | user_id | article_id |
+----+---------+------------+
|  6 |       3 |          1 |
|  7 |       3 |          3 |
+----+---------+------------+
2 rows in set (0.00 sec)

或者对于多个用户 id:

mysql> select * from articles_users where article_id in (select articles.id from articles inner join articles_users on articles_users.article_id = articles.id where user_id = 1) and user_id in (2,3);
+----+---------+------------+
| id | user_id | article_id |
+----+---------+------------+
|  4 |       2 |          1 |
|  5 |       2 |          2 |
|  6 |       3 |          1 |
|  7 |       3 |          3 |
+----+---------+------------+
4 rows in set (0.00 sec)

您已经要求使用 sql 方式 - 但几乎可以肯定有一种 railsy 方式可以做到这一点...但这应该让您开始，并且您可以从这里重构它。