Laravel SearchByKeyword Scope for Relation?

Question

I've found this function on Stackoverflow. It allows to define a keyword, and the function checks if it is part of different columns in my database.

 public function scopeSearchByKeyword($query, $keyword)
    {
        if ($keyword != '') {
            $query->where(function ($query) use ($keyword) {
                $query->where("reference", "LIKE","%$keyword%")
                    ->orWhere("title", "LIKE", "%$keyword%")
                    ->orWhere("description", "LIKE", "%$keyword%");
            });
        }
        return $query;
    }

Now, I want to add a ->orWhere() to search for a village. However in my Model is only a field called village_id which is in a relationship to my villages-table.

So if I would do ->orWhere("village_id", "LIKE", "%$keyword%"); it would search in a columns only containing id's. How can I tell the Scope it should search inside of <RelationName> -> name?

Answer 1

You could use orWhereHas like this:

public function scopeSearchByKeyword($query, $keyword)
{
    if ($keyword != '') {
        $query->where(function ($query) use ($keyword) {
            $query->where("reference", "LIKE","%$keyword%")
                ->orWhere("title", "LIKE", "%$keyword%")
                ->orWhere("description", "LIKE", "%$keyword%")
                ->orWhereHas('villages', function($q) use($keyword) {
                    $q->where('column_name', 'LIKE', "%$keyword%"); // <----- Do your stuff here.
                });
        });
    }
    return $query;
}

Hope this helps!

Answer 2

You can use orWhereHas methods to put where conditions on your has queries. This method allow you to add customized constraints to a relationship constraint, such as checking the content of a village as:

->orWhereHas('villages', function($q) use($keyword) {
    $q->where("title", "LIKE", "%$keyword%")
});

So your final query will be as:

$query->where(function ($query) use ($keyword) {
    $query->where("reference", "LIKE","%$keyword%")
        ->orWhere("title", "LIKE", "%$keyword%")
        ->orWhere("description", "LIKE", "%$keyword%")
        ->orWhereHas('villages', function($q) use($keyword) {
            $q->where("title", "LIKE", "%$keyword%")
        });
});

Docs