Search multiple tables for a search term

You need three separate queries I think and then potentially list the results separately as well

eg, if the search term matches an artist, do you just want to list the artist or all their albums and tracks

If the term matches the track name, you want to show the album and artist?

You would not need to use with here, using callbacks with with method filters only the relationship i.e User not the Model itself i.e Album. Instead, whereHas is probably what you need. You can read about it in docs

Here is how you can get desired results:

Album::where('title', 'LIKE', '%' .$input. '%') //Give me this album if its title matches the input
    // I need this album if any of its user's name matches the given input
    ->orWhereHas('user', function($q) use ($input) {
        return $q->where('name', 'LIKE', '%' . $input . '%');
    })
    // I need this album if any of its tracks' title matches the given input
    ->orWhereHas('tracks', function($q) use ($input) {
        return $q->where('title', 'LIKE', '%'. $input . '%');
    })->get();

This should work for you. I have not tested this, let me know if it works or not.

Update

I am assuming you have proper relatioships setup, maybe like this:

• An album belongs to a user (many to one)
• An album has many tracks (one to many).

But even if its not the case with you, you have got the idea now.

@ rumm.an

It hasn't worked so far. I did have to change it a little seeing "$input" variable's scope wasn't accessible within the query builder anonymous function.

$result = Tape::where('name','like','%'.$keyword.'%')
               ->orWhereHas('user', function($q) use ($keyword){
                    return $q->where('name','like','%'. $keyword . '%');
               })
               ->orWhereHas('tracks', function($q) use ($keyword){
                    return $q->where('name','like','%'. $keyword . '%');
               })->get();
          echo $result[0]->name . ' ... Artist: ' . $result[0]->user->name . ' ... tracks: ' . $result[0]->tracks[0]->name; die();

My mistake. It does work. I wasn't looping through all the records when I was doing QA on the query.

$keyword = 'jo';
          $result = Tape::where('name','like','%'.$keyword.'%')
               ->orWhereHas('user', function($q) use ($keyword){
                    return $q->where('name','like','%'. $keyword . '%');
               })
               ->orWhereHas('tracks', function($q) use ($keyword){
                    return $q->where('name','like','%'. $keyword . '%');
               })->get();
          foreach($result as $res) {
               echo $res->name . ' ... Artist: ' . $res->user->name . ' ... '. '
';
               foreach($res->tracks as $track) {
                    echo '--------------track: ' . $track->name . '
';
               }
               echo '

';
          }

@rumm.an I selected your answer as the best answer. Please make the necessary adjustments to it because the "$input" or "$keyword" is not accessible within the query builder anonymous function.

@Both of you Thanks for replying so quickly. I'm surprised by how helpful this community is.

@akLearn I always forget that! Anyways, I have updated. I'm glad it helped you. :)

Hi all, have you guys tried to have a general search and it will search in 2 more table in the db and these table has no relationship to each other. I am making like when the user type it will auto suggest based on the keywords that the user typing in and that result should be come from different table.