CODEWITHSUNDEEP
phpmysqllaravel-4eager-loading
Maarten00
Maarten00

Reputation: 704

Laravel 4.1 Eager Loading working, but queries still executed in view

I have a straightforward "Projects" model. A project can have many tasks, and each task belongs to an employee. I have a page that shows +- 100 projects, and all of the associated (unique) employees. This resulted in 500+ queries being executed, so page loads times skyrocketed to an unacceptable level. So I gave eager loading a try:

$projects->with(array('tasks' => function($query)
{
    $query->select('project_id', 'employee_id')->distinct()->get();
}));

As far as I can tell through debugging, this gives me the result I want. For each project, the tasks are retrieved correctly, with no duplicate employees.

However. When I try to load the tasks in the view, Laravel is still executing 400+ queries to retrieve tasks for each project.

@foreach ($projects as $project)
   @foreach($project->tasks as $task)
       @if($task->employee_id != 0 )
           <img src="{{UserHelper::getGravatarUrl($task->employee->id)}}" alt="{{{ $task->employee->name }}}"
        @endif
    @endforeach
@endforeach

I have tried using tasks() and tasks()->get() instead, but to no avail. How do I access the eager loaded attributes? I prefer not to use dirty raw queries, as Eloquent should be up to the task.

Upvotes: 1

Views: 937

Answers (4)

Maarten00
Maarten00

Reputation: 704

In addition to the above answers, I will add my own query which works perfectly to eager load all tasks for each project, as well as the employee and the profiles for the employee. 

$projects->with(array('tasks' => function($query)
{ 
   $query->groupBy('project_id', 'employee_id')->with(array('employee.profiles'));
}));

I am accessing these like this in my view: 

@foreach($projects as $project)    
    @foreach($project->tasks as $task)
        @if($task->employee_id != 0 )
            <img src="{{UserHelper::getGravatarUrl($task->employee)}}" alt="{{{ $task->employee->name }}}">
        @endif
    @endforeach
@endforeach

This works great, and has reduced my amount of queries greatly.

Upvotes: 0

The Alpha
The Alpha

Reputation: 146201

According to that, your relationship should be built using id field of the projects table and project_id field of the tasks table. Also make sure you have called the get() method at the end.

You may use a select in eager loading but make sure you have selected the relation builder keys, for example, if your Project has many Task and you tasks table contains the project_id as foreign key then you need to select that foreign key in the select. So make sure that, project_id is available in the tasks table as foreign key to build the relationship with projects table. For example, check this:

$r = Role::with(['users' => function($q) {
    $q->select('role_id', 'username');
}])->get();

I have users table that contains role_id as foreign key to build relationship between roles and users table and has a one-to-many relationship declared in the Role model so I can use select() to eager load related User models (only role_id and 'username' will be loaded in the related User model) but without the role_id in the select I can't load the related User models.

Upvotes: 2

azngunit81
azngunit81

Reputation: 1604

I'm going to add a bit to WereWolf's answer:

1) The proper way to get your objects is:

$projects->with(array('tasks' => function($query)
{
    $query->select('project_id', 'employee_id')->distinct();
}))->get();

the get is OUTSIDE, not inside for Eager Loading.

2) your function task (within projects) should have a one to MANY relationship that points to a Task Model with a foreign key as mentioned by WereWolf

3) Eager Loading enables you to load the N+1 ahead of time for One to One for example if you need to reference the Employee inside projects so can see which tasks goes to which employee instead of running a 

SELECT * FROM employee WHERE id = ?

on each $task->employee()->name that you run in order to fetch data from the relationship

So eager loading would SELECT * FROM employee first - then within tasks would eager load with a WHERE IN (?,?,?) (please correct me if I'm wrong)

4) Brings me to my 4th point would be to eager load employee which will reduce the queries. Make a function relationship of one-to-many inside tasks.

Upvotes: 2

Andreas
Andreas

Reputation: 8029

You cannot rely on select() working for eager loading as Eloquent potentially needs all the model's attributes to correctly eager load. Either get rid of the select or try adding more columns to the select statement.

Upvotes: 0

Related Questions