If we do not properly use Laravel’s ORM library, we can easily fall into the N+1 problem
class Book extends Model {
public function author()
{
return $this->belongsTo(‘AppAuthor’);
}
}
Now we will iterate all the books as follows
foreach (Book::all() as $book)
{
echo $book->author->name;
}
The above code will first retrieve all the books, then in each loop it queries the author table to get the names of the authors. So if there are 25 books in the database, it will query the database a total of 26 times (including 1 time it gets book:all). OK, that’s not good. Now to overcome this problem, laravel provides us with a technique as follows
foreach (Book::with(‘author’)->get() as $book)
{
echo $book->author->name;
}
For the above loop, it only executes 2 queries
select * from books
select * from authors where id in (1, 2, 3, 4, 5, …)
If there are many tables to be retrieved, we can also write as follows
$books = Book::with(‘author’, ‘publisher’)->get();
If the tables have relationship constraints, for example we need to get the author’s contact information but it is in another table, then the following statement will retrieve both author and contact tables.
$books = Book::with(‘author.contacts’)->get();
If you need to specify more conditions in the query, do the following:
$users = User::with([‘posts’ => function($query)
{
$query->where(‘title’, ‘like’, ‘%first%’);
}])->get();
Or you can add sorting conditions
$users = User::with([‘posts’ => function($query)
{
$query->orderBy(‘created_at’, ‘desc’);
}])->get();
