Jul 12, 2023

Level 63

Relationship and performance

Hello,

Here are 2 simple examples.

Example 1

$authors = Author::get();
...
foreach ($authors as $author) {
		foreach ($author->books as $book) {
				...
		}
}

Example 2

$authors = Author::with('book')->get();
...
foreach ($authors as $author) {
		foreach ($author->books as $book) {
				...
		}
}

Just to be sure.

Example 1 => N+1 problem, for each $user->books there is a query to the database to retrive the users' books.

Example 2 => the books are already loaded and there is no query to the database to retrieve the books in the loop.

But what happens when I need to check datas via a policy ?

$authors = Author::with('book')->get();
...
foreach ($authors as $author) {
		if (auth()->user()->can('view', $author)) {
				foreach ($author->books as $book) {
						...
				}
		}
}
...
// Policy
public function view(User $user, Author $author)
{
		// for any reason, here I need to access the author's books
		$books = $author->books;
}

Is there a new query to the database in the policy or the books are automatically loaded when calling the policy ?

Thanks for your answer.

Vincent

LaryAI

2 years ago

Level 58

In the given code, when you access $author->books in the policy, it will not automatically load the books. It will result in a new query to the database to retrieve the books for each author.

To avoid the N+1 problem and improve performance, you can use eager loading in the policy as well. Here's an updated version of the code:

$authors = Author::with('books')->get();
...
foreach ($authors as $author) {
    if (auth()->user()->can('view', $author)) {
        foreach ($author->books as $book) {
            ...
        }
    }
}
...
// Policy
public function view(User $user, Author $author)
{
    $author->load('books'); // Eager load the books
    $books = $author->books;
    ...
}

By using $author->load('books'), you can eager load the books for each author in the policy, which will prevent additional queries to the database when accessing $author->books inside the loop.

This way, you can ensure that the books are already loaded and there won't be any extra queries to the database in the loop.

jlrdw

2 years ago

Level 75

Many times an extra query when needed is actually more efficient.

An example is an accounts receivable report, I usually double paginate, meaning I show the current company and below a paginated list of their receivables.

I have a next company link. Going to the next company will be a new query.

I usually do not like loading thousands or tens of thousands of Records in advance.

In fact PDO can be very efficient at this sort of thing.

Basically query what's needed when it is needed.

Also eloquent is not for everything, many times writing regular SQL with PDO is much better.

2 likes

vincent15000

2 years ago

Level 63

@jlrdw What you are trying to tell me is that I should load the missing relationships directly inside the policy rather than loading them in the controller ?

jlrdw

2 years ago

Level 75

@vincent15000 the only true way to learn this stuff and how to query it is to set up a database with a few hundred companies, then underneath the companies have a dummy table of receivables, then practice searching and doing queries on the data and work out how to do it efficiently.

I can tell you now no one is going to paginate through everything, so of course have a search to narrow down results.

In your case use authors and books and make up thousands of Records and see how it goes.

Remember under one author you might have five books, under another author he might have 10,000 books.

Yes that's kind of exaggerated for learning purposes.

I would never load every possible book in advance, I would query as needed.

NoLAstNamE

2 years ago

Level 8

@jlrdw

An example is an accounts receivable report, I usually double paginate, meaning I show the current company and below a paginated list of their receivables.

I don't quite get this but I am really interested in it and might be worth learning. Could you give a detailed or a deeper dive into it? Thank you.

tykus

2 years ago

Level 104

@vincent15000

Is there a new query to the database in the policy or the books are automatically loaded when calling the policy ?

No. In the example you have shown, there is no query for the Author's books inside the Policy; the books are not accessed in the Policy

1 like

vincent15000

2 years ago

Level 63

@tykus The books are NOT accessed in the policy ?

public function view(User $user, Author $author)
{
    $author->load('books'); // Eager load the books
    $books = $author->books;
    ...
}

Do I not access to the books ? Hmmm perhaps I don't understand what you mean.

tykus

2 years ago

Level 104

@vincent15000 as I see it you don't need to access the Collection of books in the Policy; you want only to check if the authenticated user is authorized to view the Author? You do access the books later in the view template (after the authorization check).

1 like

vincent15000

2 years ago

Level 63

@tykus Not really ... it's not an application with authors and books, here I have only posted an simple example, but in the real project, I do need to access the collection inside the policy.

krisi_gjika

2 years ago

Level 14

@vincent15000 in your example

public function view(User $user, Author $author)
{
	// query the DB to load the books,
    // even if they are already loaded,
    // can use ->loadMissing('books')
    // to load only if `$author->books` does not exist on the model
    $author->load('books'); 
    $books = $author->books;
    ...
}

however it is very likely you do not want to load the actual models in a policy. usually on a policy you only care about an existence / absence, ex: $author->books()->where('user_id', $user->id)->exists(). loading all the books models and checking something for each of them can be slow an inefficient.

1 like

vincent15000

2 years ago

Level 63

@krisi_gjika I agree with you, but I don't have any other choice because I have to adapt the code from an first version of an application which was quite bad developed.

Furthermore there are some very complex rights to manage in the application and the only logical place to manage these rights are the policies. I have thought about managing them in the controllers, in a dedicated place for the rights, ... but no matter where I could manage them, I necessarily need to loop in the books to check some specific property.