如何在同一张桌子上多次加入? [英] 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屋！