Laravel eager loading combine where for multiple tables

1
votes

I have problem with Laravel eager loading, I want to search many to one related tables.

I have two models, Book and Author. I have relation belongsTo (in Book model) and hasMany (in Author model).

Books: id, title, description, author, tags, type
Author: id, name, birth, tags, type

(this is not the real columns, I have a lot of columns, this is only for example)

I must to use models for searching, I can't use DB or raw queries for the relations.

So, my question is how can I search by parent table into Model->with() function ?

Model->with('author', function($query) use($search) {
    $query->where('author.name', '=', $search);
    $query->orWhere('book.title', '=', $search);
});

The important thing is that I need to get Books, and I must to be able to use $book->author (or on $results->toArray() to get all Book data with Author data in ->author property)

I hope that you understand me what I want to ask.

Best regards

2
sqllaraveleloquenteager-loadinglaravel-eloquent
Did you try joining the two tables? - Taha Paksu
Yes, but in that case I don't know how to convert the results to be standard Book model. After getting the results I need to be able to use Model features. - Damjan Krstevski
So you want to get all Books (and their authors), where either author.name matches $search or book.title matches $search? - DevK
If you are querying with \App\Book::where, you'll get a `\App\Book` collection from your query. - Taha Paksu

2 Answers

2
votes

You can use whereHas() to search by author name and orWhere() to also search by book title:

Book::whereHas('author', function($q) use($search) {
        $q->where('name', 'like', '%'.$search.'%');
    })
    ->orWhere('title', 'like', '%'.$search.'%')
    ->get();

So, this query will find all books with defined title or author's name. If you also need to load the author data, add ->with('author') to the query.

1
votes

Use with to eager load the author and whereHas to filter the records.

Book::with('author', function($query) use($search) {
    $query->where('name', '=', $search);
})->whereHas('author', function($query) use($search) {
    $query->where('name', '=', $search);
})
 ->where('title', '=', $search)
 ->get();