如何检索“common"通过单个 SQL 查询在同一数据库表中的记录? [英] How to retrieve "common" records in the same database table through a single SQL query?

问题描述

我正在使用 Ruby on Rails(版本 3.2.2)和 mysql2(版本 0.3.11 - MySQL 版本 5.2.38).在讨论时如何通过关联表检索一组用户在公共"中拥有的关联对象/记录"*我发现自己处于特定"情况，我必须找到关联对象/记录"**通过在关联数据库表上运行 single - 出于性能原因 - SQL 查询.特别是，我想这样做是为了仅检索前面提到的关联对象/记录"，其中 user_id(代表一个 外键指向"一个User对象)和article_id(代表一个外键"指向Article 对象)列值相同(即，article_id 和 user_id 是常见").

I am using Ruby on Rails (version 3.2.2) and mysql2 (version 0.3.11 - MySQL version 5.2.38). While discussing on "How to retrieve associated objects / records that a set of users have in "common" through a association table"* I found myself in a "specific" situation in which I have to find "associated objects / records"** by running a single - for performance reasons - SQL query on a association database table. Particularly, I would like to make that in order to retrieve only previously mentioned "associated objects / records" where user_id (representing a foreign key "pointing" to a User object) and article_id (representing a foreign key "pointing" to a Article object) column values are the same (that is, where article_id and user_id are "common").

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

How could / should I retrieve "associated objects / records" (maybe, by using some facility in a Ruby on Rails and / or SQL / database "way" / "context") in my case?

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

* Specifically, after @Andrew Marshall posted his / her answer.

** 注意:那些关联对象/记录"与 has_many :through Ruby on Rails ActiveRecord::Associations 在 链接问题.

** Note: those "associated objects / records" are related to a has_many :through Ruby on Rails ActiveRecord::Associations described in the linked question.

推荐答案

我认为这是一种原始的 sql 方式来提取您想要的内容:

This is a raw-sql way to pull out what you want, I think:

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 的位置:

Where you can substitute user ids in Rails:

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])

或者对于多个 ID::

Or for multiple 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]])

因此从样本数据(来自您的其他问题):

So from sample data (from your other questions):

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:

Or for multiple user-ids:

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 方法 - 但几乎可以肯定有一种非常笨拙的方法可以做到这一点……但这应该可以帮助您入门，您可以从这里开始重构它.

You've asked for a sql way - but there's almost certainly a railsy way to do this... but this should get you started, and you can refactor it down from here.

这篇关于如何检索“common"通过单个 SQL 查询在同一数据库表中的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！