Menu
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
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?
447
As you may know, it is used to join and combine data from two or more tables into one common data set. In this article, I'm going to discuss special types of joins? in which you combine the same table twice—including joining a table to itself, also known as the self join.
You use self-join to create a result set that joins the rows with the other rows within the same table. Because you cannot refer to the same table more than one in a query, you need to use a table alias to assign the table a different name when you use self-join.
Multiple joins can be described as a query containing joins of the same or different types used more than once, thus giving them the ability to combine multiple tables.
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)
I'm not aware of a higher-level way to do such a join with Active Record.
59
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
