Laravel Eloquent: Reduce number of queries

Question

Let's say I have 250 users in users table and each user has one or many books, and each book has one or many chapters. Now I would like to print the user names, with their book names.

Controller:

$users = User::all();

in blade:

@foreach($users as $user)
  <tr>
      <td>{{ $user->id }}</td>
      <td>{{ $user->name }}</td>
      <td>
        @foreach($user->books as $book)
          {{ $book->name }},
          @endforeach
      </td>
  </tr>
@endforeach

# of queries 252

Now to overcome the n+1 problem, the query should be

$users = User::with('books')->get();

Now the # of queries are only 2.

I want to print the book names with number of chapters like this-> BookName(# of chapters). So in my blade

@foreach($users as $user)
      <tr>
          <td>{{ $user->id }}</td>
          <td>{{ $user->name }}</td>
          <td>
            @foreach($user->books as $book)
              {{ $book->name }} ({{ $book->chapters->count() }}),
              @endforeach
          </td>
      </tr>
    @endforeach

so for 750 books with 1500 chapters the # of queries are about 752 and it increases if chapter number increases.

Is there any better Eloquent way to reduce it or should I go for raw SQL queries?

with supports nested relationships. You should be able to use with('books.chapters') — maiorano84
– maiorano84, Commented Jan 9, 2017 at 4:47
wow.. thanks.. it works.. can you provide any documentation to read? — Noob Coder
– Noob Coder, Commented Jan 9, 2017 at 4:49

Alexey Mezenin · Accepted Answer · 2017-01-09 04:53:22Z

10

You don't need to load all chapters data and then manually count each collection. Use withCount() instead:

$users = User::with('books')->withCount('chapters')->get();

If you want to count the number of results from a relationship without actually loading them you may use the withCount method, which will place a {relation}_count column on your resulting models.

edited Jan 9, 2017 at 4:53

answered Jan 9, 2017 at 4:50

Alexey Mezenin

164k27 gold badges309 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

maiorano84 Over a year ago

This is likely a more efficient answer, as each chapter object is not being loaded into memory. Upvoted.

Noob Coder Over a year ago

yes. thanks to alexey mezenin also. I learned two new things. voted up

Noob Coder Over a year ago

Though I understood the point, but there is no direct relationship of users and chapters. the relationship in models are-> user hasMany books, book belongs to user and book hasMany chpaters. Chapter belongs to book. So the line User::with('books')->withCount('chapters')->get(); returns bad method call exception Call to undefined method Illuminate\Database\Query\Builder::chapters(); any idea about how to define this user to chapter relationship?

Alexey Mezenin Over a year ago

In this case you can create hasManyThrough relationship or add withCount() to the books relation (or better create separate relationship for this like booksWithChaptersCounted).

EffectiX Over a year ago

You pass a closure to with('books') so it looks liek with(['books' => function($query) {...}]) and then within that closure you add $query->withCount('chapters'); This should return your desired chapter count on each book that belongs to a user. Using a hasManyThrough will give you a global count of the chapters adding ALL the books that belong to the user. I understand that, if you want a global count, that's the way to go, if you want a per-book count, you'd use this closure to execute withCount() on the books query to add the chapter count to each book.

Community · Accepted Answer · 2020-06-20 09:12:55Z

6

From the Eloquent Documentation:

Nested Eager Loading

To eager load nested relationships, you may use "dot" syntax. For example, let's eager load all of the book's authors and all of the author's personal contacts in one Eloquent statement:

$books = App\Book::with('author.contacts')->get();

In your case, you can retrieve the nested relationships you need with the following:

User::with('books.chapters')->get();

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 9, 2017 at 4:51

maiorano84

12.1k3 gold badges38 silver badges52 bronze badges

Collectives™ on Stack Overflow

Laravel Eloquent: Reduce number of queries

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related