My target is to get collection of books with count of matches book.names in another table without relation
I get collection like this
$books = Books::paginate(20);
What I need now is to get the count of matches like this
SELECT COUNT(*) FROM `posts` WHERE `body` LIKE '%book.name%'
How can I do this with one query and avoiding unnecessary queries for each model, like eager loading
You can do it with eager loading without loading all post. There is a method called withCount
Books.php
public function posts() {
return $this->hasMany(Post::class, 'post_id', 'id');
}
One way to find all post related to book is
$books = Books::withCount(['posts' => function ($query) use($searchTerm) {
$query->where('body', 'like', $searchTerm);
}])->get();
//How to retrieve
$book = $books->first()->posts_count.
You can find more information about withCount on laravel documentation website.
Approach 2: Without Eager Loading
$books = Books::select('*')->addSelect(DB::raw("SELECT COUNT(*) as post_count FROM `posts` WHERE `body` LIKE '%book.name%' ")->get(); //This will return collection with post_count.
Note: Not tested
