Eager loading with constraints generates too many queries. How can I alleviate that?

Viewed 416

I have 4 tables that are related in the database and in models as follows:

Users (hasMany(communities), hasMany(comments), hasMany(submissions))
Communities (belongsTo(user), hasMany(submissions))
Submissions (belongsTo(community), belongsTo(user), hasMany(comments))
Comments (belongsTo(user) , belongsTo(submission))

Now. If I use the relations to get, say the 25 latest submissions, like this:

$submissions = Submission::simplePaginate(25);

I'm met with 76 queries run, after using foreach to loop through the results.

If I use eager loading with the following

$submissions = Submission::with(['user', 'community', 'comments'])->simplePaginate(25);

I'm met with 4 queries which is optimal (I think).

Now, my problem is that, on communities, I have a database field called active which accepts a 1 for an active community, and a 0 for an inactive. As you might have guessed, the previous query returns everything, including inactive communities.

I thought about eager loading constraints, so I used the following:

$submissions = Submission::with([
      'community' => function($query) {
        $query->active();
      }
    ]  
  )->with(['user', 'comments'])->simplePaginate(25);

This still does that job with 4 queries, but now I run into another problem. This method filters the inactive communities, but still loads the posts, users and comments. And now I'm left with a result where I get posts from inactive communities.

Finally, I tried, upon reading this community, the following:

   $submissions = Submission::whereHas(
    'community', function($query) {
      $query->active();
    }  
  )->with(['user', 'comments'])->simplePaginate(20);

So, instead of using ->with() I used ->whereHas() as someone suggested. However, while this produces the correct results, i.e. it gives me posts, comments and users from the active communities only, it now does that in 23 queries, as per the feedback I get from Laravel Debugbar.

Is this normal? Is this the only way I can do this through Eloquent and without writing custom queries with joins? Am I blowing 23 queries out of proportion?

Any help would be greatly appreciated.

2 Answers
Related