php – Laravel Eager Loading for relationship model – unable to reduce queries unless i return null

exception or error:

I am trying to make reduce my database queries when retrieving from the database using Eloquent.

I’m trying to take advantage of Eager Loading to reduce the queries and not get the N+1 issue.. I’ve got some relationships in my models too.

I have noticed (and I’m not sure if this is a red herring) that if I just return null from my controller method then I get 6 queries. Which would indicate Eager Loading working. But I get 492 queries when I return the result of the eloquent variable. EG. In my controller:

    public function getJobItems()
    {

        $userId = auth()->user()->id;


        $jobs = JobItem::with(['project', 'user'])
            ->whereHas('project', function ($query) use ($userId) {
                $query->where('user_id', '=', $userId);
            })
            ->get();

        return $jobs;

    }

If I change return $jobs; to just return; then I can see only 6 queries (shown with Laravel Telescope). I’m not sure how outputting the $jobs var changes the queries made to the DB. Here is what I get from Telescope query inspector for those 6 queries:

update `sessions` set `payload` =...        
select `users`.*, `projects`.`user_id` as `laravel_through_key` from `users` inner join `projects` on...    
select * from `projects` where `projects`.`id` in (5, 9, 15, 111, 116, 117, 118, 119, 123, 124) and...  
select * from `job_items` where exists (select * from `projects` where `job_items`.`project_id` =...    
select * from `users` where `id` = 1 and `users`.`deleted_at`
select * from `sessions` where `id` = 'acx7zPzeeRVrtI5FTUGUbULm1bEVQ0T5AVylFIf6'

When I use return $jobs; I get 492 queries and telescope explains 484 are duplicates. For instance variations on:

select * from `projects` where `projects`.`id` = 15 and `projects`.`deleted_at` is null limit 1

In my JobItem Model I have the following:


    public function project()
    {
        return $this->belongsTo('App\Project', 'project_id');
    }


    public function user()
    {

        return $this->hasOneThrough(
            User::class,
            Project::class,
            'user_id',
            'id',
            'project_id',
            'id'
        );
    }

I have the following tables:

table: job_items
columns: id, project_id

table: projects
columns: id, user_id

table: users
column: id

I want to output all users connected to JobItems

In my JobItem.php model I have tried

class JobItem extends Model
{

    public function user()
    {


        return $this->hasOneThrough(
            Project::class,
            User::class,
            'project_id',
            'id',
            'belongs_to',
            'user_id'
        );

    }
}

Any ideas on how to reduce my queries with these models would be appreciated. Thanks

How to solve:

Leave a Reply

Your email address will not be published. Required fields are marked *