php - Laravel Eloquent Join vs Inner Join? -

so having trouble figuring out how feed style mysql call, , don't know if eloquent issue or mysql issue. sure possible in both , in need of help.

so have user , go feed page, on page shows stuff friends (friends votes, friends comments, friends status updates). have tom, tim , taylor friends , need of votes, comments, status updates. how go this? have list of friends id number, , have tables each of events (votes, comments, status updates) have id stored in link user. how can of information @ once can display in feed in form of.

tim commented "cool"

taylor said "woot first status update~!"

taylor voted on "best competition ever"

edit @damiani after doing model changes have code this, , return correct rows

$friends_votes = $user->friends()->join('votes', 'votes.userid', '=', 'friend.friendid')->orderby('created_at', 'desc')->get(['votes.*']); $friends_comments = $user->friends()->join('comments', 'comments.userid', '=', 'friend.friendid')->orderby('created_at', 'desc')->get(['comments.*']); $friends_status = $user->friends()->join('status', 'status.userid', '=', 'friend.friendid')->orderby('created_at', 'desc')->get(['status.*']); 

but them happen @ once, because mysql sorting thousands of records in order 100x faster php taking 3 lists, merging them , doing it. ideas?

i'm sure there other ways accomplish this, 1 solution use join through query builder.

if have tables set this:

users     id     ...  friends     id     user_id     friend_id     ...  votes, comments , status_updates (3 tables)     id     user_id     .... 

in user model:

class user extends eloquent {     public function friends()     {         return $this->hasmany('friend');     } } 

in friend model:

class friend extends eloquent {     public function user()     {         return $this->belongsto('user');     } } 

then, gather votes friends of user id of 1, run query:

$user = user::find(1); $friends_votes = $user->friends()     ->with('user') // bring along details of friend     ->join('votes', 'votes.user_id', '=', 'friends.friend_id')     ->get(['votes.*']); // exclude details friends table 

run same join comments , status_updates tables. if votes, comments, , status_updates in 1 chronological list, can merge resulting 3 collections 1 , sort merged collection.

---

edit

to votes, comments, , status updates in 1 query, build each query , union results. unfortunately, doesn't seem work if use eloquent hasmany relationship (see comments question discussion of problem) have modify queries use where instead:

$friends_votes =      db::table('friends')->where('friends.user_id','1')     ->join('votes', 'votes.user_id', '=', 'friends.friend_id');  $friends_comments =      db::table('friends')->where('friends.user_id','1')     ->join('comments', 'comments.user_id', '=', 'friends.friend_id');  $friends_status_updates =      db::table('status_updates')->where('status_updates.user_id','1')     ->join('friends', 'status_updates.user_id', '=', 'friends.friend_id');  $friends_events =      $friends_votes     ->union($friends_comments)     ->union($friends_status_updates)     ->get(); 

at point, though, our query getting bit hairy, polymorphic relationship , table (like definiteintegral suggests below) might better idea.