Search code examples
laraveleloquent

Laravel eloquent, how to query in relationtionship table with Or Where

I have a Game model, with relations to Location model.

I wanna search in the game's name field, as well as the game's location's name field. And the game's start time must be greater than today's date.

$games = Game::with('user', 'location')
    ->when($this->searchterm !== '', fn(Builder $query)
        => $query->where('name', 'like', '%'. $this->searchterm .'%')->orWhere('location.name', 'like', '%'. $this->searchterm .'%')
    )
    ->orderBy('start_time', 'ASC')
    ->whereDate('end_time', '>', Carbon::now())
    ->paginate(100);

This doesn't work. It throws the following error:

Column not found: 1054 Unknown column 'location.name' in 'where clause'

How can I fix this problem?

Solution

  • When Eloquent loads relationships (In your case, loading the Game's user and location relationships via with('user', 'location')), it does not use joins on the main query, meaning you cannot simply use where/orWhere like what you've tried.

    Instead, you need to specifically use Eloquent methods to query the relationship's existence

    $games = Game::query()
    ->with('user', 'location')
    ->when(
        $this->searchterm !== '',
        fn (Builder $query) => $query
            ->where(function (Builder $sql) {
                $sql->where('name', 'like', "%{$this->searchterm}%")
                    ->orWhereRelation('location', 'name', 'like', "%{$this->searchterm}%");
            })
    )
    ->orderBy('start_time', 'ASC')
    ->whereDate('end_time', '>', Carbon::now())
    ->paginate(100);