SQL request to find the n last records of each one of a user's friends

Question

Hi,

I have a User model which has friends (User.friends with a user_id/friend_id join table called followings). Each user has events (the events table has a user_id column).

For an activity feed, I want to get an array with the last 20 events of the friends of the user. Right now I'm calling the last 20 events for each friend, sorting everything by date and just keeping the last 20 events.

How can I do that directly into the database so that I only need one request instead of one per friend?

Is there a way I can do that directly with Rails? Or should I use an SQL request?

Thank you,

Kevin

Answer 1

select e.* from
(select rownum r , e.* from events e where user_id in (our target friends) order by date desc)    
where r < 20 ;

I don't know if rownum is called 'rownum' in postgres. rownum - is number of row. if e.* is not working, try to write every field manually. out target friends is smth like select user_id2 from friend_connections where user_id1 = targetId union select user_id1 from friend_connections where user_id2 = targetId. Yes, i understand that is is very ugly.

Answer 2

I'd do it in a SQL request like this:

select Events.Summary from Events
join Followings
    on Events.User_Id = Followings.Friend_Id
where Followings.User_Id = @User_Id
order by Events.Date desc
limit 20;

limit 20 is MySql - is it the same in PostgreSql?