2

My user table and item table are related by a many to many relationship, the item table has some data in foreign tables, example color table I have a pivot table that relates the two. My question is, if I want to retrieve all the items associated to a user with a color how do I get them with eager loading?

I know that $user->item will retrieve all the items for that user.

But if I want to eager load all the items for that user with the color attributes in one query, how do I do it? At the moment I am looping through the items associated to the user and lazy loading every data I need, for example

foreach($user->item as $i){

      echo($i->item->color)

}

This means every cycle makes a new query...

here the models:

User model:

public function item(){
        return $this->belongsToMany('App\item')->withTimestamps();
    }

item model:

public function user(){
        return $this->belongsToMany('App\User');
    }

and this the schema for the pivot table

Schema::create('item_user', function(Blueprint $table) {

            $table->unsignedInteger('user_id')->unsigned()->index();
            $table->foreign('user_id')
                ->references('id')
                ->on('users')
                ->onDelete('cascade');

            $table->unsignedInteger('item_id')->unsigned()->index();
            $table->foreign('item_id')
                ->references('id')
                ->on('items')
                ->onDelete('cascade');

            $table->timestamps();
        });
Chriz74
  • 1,410
  • 3
  • 23
  • 47

1 Answers1

3

You can simply use a Nested Eager Loading

Eager loading is done using with(): in your instance you could use the following:

public function item(){
    return $this->belongsToMany('App\item')->withTimestamps()->with('color');
}

This will eager load the color on the "item". However you can also eager load immediately on the User model in your controller:

User::with('items.color')->find(1);

I'm not sure based on your code that the relations/model is like this, but you get the point I guess.

Luceos
  • 6,629
  • 1
  • 35
  • 65
  • Thanks, I modified the model as you suggested. Apparently now $user->items will get all the user items + color in 3 queries (?) – Chriz74 Feb 24 '16 at 13:49
  • Can you post your `items` relation? I only modified the `item` relation which is a belongsToMany, your `items` relation is probably a `hasMany` ? – Luceos Feb 24 '16 at 13:53
  • I already posted the relations in the main question. Is that what you are asking? – Chriz74 Feb 24 '16 at 14:06
  • You're calling the plural `items` method on the `User` model, but the only method you've posted is the singular `item` which is a belongsToMany. If you'd had a plural items method it would have been a `hasMany` relation probably.. – Luceos Feb 24 '16 at 14:34
  • I have a many to many relationship between user and item. The code is only an example. – Chriz74 Feb 25 '16 at 09:36