如何在同一张桌子上多次联接？ [英] How to join on the same table multiple times?

问题描述

我要查询给定两个用户的共同朋友。下面的查询在大多数情况下应该可以解决问题，而友谊表应该是不言而喻的，其中包含 user_id 和 friend_id 。

I'm querying for the mutual friends of a given two users. The query below should do the trick for the most part and the friendship table should be self-evident, containing a user_id and friend_id.

SELECT `users`.* FROM `users`
INNER JOIN `friendships` `a` ON `users`.`id` = `a`.`friend_id`
INNER JOIN `friendships` `b` ON `users`.`id` = `b`.`friend_id`
WHERE `a`.`user_id` = 1 AND `b`.`user_id` = 2

什么让我感到困惑是如何编写此语义 ActiveRecord 的方法。使用ActiveRecord，您可以加入关联，但只能加入一次。那么，如何在 ActiveRecord 中尽可能简单地编写此代码？

What's got me confused is how to write this semantic ActiveRecord. With ActiveRecord you can join on an association, but only once. So how do you go about writing this as plainly as possible in ActiveRecord?

推荐答案

我使用 joins 的字符串参数来做到这一点：

I do it with string arguments to joins:

User.
  joins("INNER JOIN friendships a ON users.id = a.friend_id").
  joins("INNER JOIN friendships b ON users.id = b.friend_id").
  where("a.user_id" => 1, "b.user_id" => 2)

我不知道使用Active Record进行这种联接的更高级方法。

I'm not aware of a higher-level way to do such a join with Active Record.

这篇关于如何在同一张桌子上多次联接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！