Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Laravel Eloquent Join vs Inner Join?

Tags:

So I am having some trouble figuring out how to do a feed style mysql call, and I don't know if its an eloquent issue or a mysql issue. I am sure it is possible in both and I am just in need of some help.

So I have a user and they go to their feed page, on this page it shows stuff from their friends (friends votes, friends comments, friends status updates). So say I have tom, tim and taylor as my friends and I need to get all of their votes, comments, status updates. How do I go about this? I have a list of all the friends by Id number, and I have tables for each of the events (votes, comments, status updates) that have the Id stored in it to link back to the user. So how can I get all of that information at once so that I can display it in a feed in the form of.

Tim commented "Cool"

Taylor Said "Woot first status update~!"

Taylor Voted on "Best competition ever"

Edit @damiani So after doing the model changes I have code like this, and it does return the 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 I would like them all to happen at once, this is because mysql sorting thousands of records in order is 100x faster then php taking 3 lists, merging them and then doing it. Any ideas?

like image 652
CMOS Avatar asked Sep 22 '14 22:09

CMOS


People also ask

Can we use join in eloquent laravel?

Eloquent power joinsAdd some Laravel magic to your Eloquent joins. If you have some experience using databases, it is very likely you have used joins at least once in your career. Joins can be used for a bunch of different reasons, from selecting data from other tables to limiting the matches of your query.

How use inner join in laravel eloquent?

Laravel eloquent or query builder join method do the same as MySQL inner join function. Laravel join accepts multiple parameters to the function and first parameter as table name and rest are columns constraints. DB::table('users') ->join('contacts', 'users.id', '=', 'contacts.

What is advantage of eloquent in laravel?

CRUD with Eloquent. CRUD operations under the Eloquent object-relational mapper (ORM) make it easier for Laravel developers to work with multiple databases. It performs create, retrieve, update, and delete (CRUD) operations, as well as maps object models to database tables.


2 Answers

I'm sure there are other ways to accomplish this, but one solution would be to use join through the Query Builder.

If you have tables set up something like this:

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

In your User model:

class User extends Eloquent {     public function friends()     {         return $this->hasMany('Friend');     } } 

In your Friend model:

class Friend extends Eloquent {     public function user()     {         return $this->belongsTo('User');     } } 

Then, to gather all the votes for the friends of the user with the id of 1, you could run this query:

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

Run the same join for the comments and status_updates tables. If you would like votes, comments, and status_updates to be in one chronological list, you can merge the resulting three collections into one and then sort the merged collection.


Edit

To get votes, comments, and status updates in one query, you could build up each query and then union the results. Unfortunately, this doesn't seem to work if we use the Eloquent hasMany relationship (see comments for this question for a discussion of that problem) so we have to modify to queries to 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 this point, though, our query is getting a bit hairy, so a polymorphic relationship with and an extra table (like DefiniteIntegral suggests below) might be a better idea.

like image 165
damiani Avatar answered Oct 07 '22 18:10

damiani


Probably not what you want to hear, but a "feeds" table would be a great middleman for this sort of transaction, giving you a denormalized way of pivoting to all these data with a polymorphic relationship.

You could build it like this:

<?php  Schema::create('feeds', function($table) {     $table->increments('id');     $table->timestamps();     $table->unsignedInteger('user_id');     $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');     $table->morphs('target');  }); 

Build the feed model like so:

<?php  class Feed extends Eloquent {     protected $fillable = ['user_id', 'target_type', 'target_id'];      public function user()     {         return $this->belongsTo('User');     }      public function target()     {         return $this->morphTo();     } } 

Then keep it up to date with something like:

<?php  Vote::created(function(Vote $vote) {     $target_type = 'Vote';     $target_id   = $vote->id;     $user_id     = $vote->user_id;      Feed::create(compact('target_type', 'target_id', 'user_id')); }); 

You could make the above much more generic/robust—this is just for demonstration purposes.

At this point, your feed items are really easy to retrieve all at once:

<?php  Feed::whereIn('user_id', $my_friend_ids)     ->with('user', 'target')     ->orderBy('created_at', 'desc')     ->get(); 
like image 35
DefiniteIntegral Avatar answered Oct 07 '22 18:10

DefiniteIntegral