[SOLVED] Is it possible to define a single SQL query that draws a set of permissible foreign_keys from one table and then uses them to filter another?

Is it possible to define a single SQL query that draws a set of permissible foreign_keys from one table and then uses them to filter another?

Specifically, I'm trying to figure out if it's possible to generate SQL that does what I want to feed into Ruby-on-Rails' find_by_sql method.

Imagine there are Users, who are joined cyclically to other Users by a join table Friendships. Each User has the ability to create Comments.

I'd like a SQL query to return the latest 100 comments created by any friends of a given user so I can display them all in one convenient place for the user to see.

This is tricky, since essentially I'm looking to filter the comments by whether their foreign keys for their author are contained in a set of keys obtained derived from the user's friends' primary keys.

Edit: Clarifying the setup. I'm not exactly sure how to write a schema definition, so I'll describe it in terms of Rails.

class User
  has_many :friends, :through => :friendships
  has_many :comments
end
class Friendship
  belongs_to :user
  belongs_to :friend, :class_name => "User", :foreign_key => "friend_id"
end
def Comment
  has_one :User
end

Solution

It's not that tricky, you just use joins. To get just the comments you only need to join the Friendships table and the Comments table, but you probably also want some information from the Users table for the person who wrote the comment.

This would get the last 100 comments from people who are friends with the user with id 42:

select top 100 c.CommentId, c.CommentText, c.PostDate, u.Name
from Friendships f
inner join Users u on u.UserId = f.FriendUserId
inner join Comments c on c.UserId = u.UserId
where f.UserId = 42
order by c.PostDate desc