Laravel relationship query with 'LIKE' issue

user_8294675 0
Viewed 72

I've got following LARAVEL self relationship on User model:

public function matchesSet(){
        return $this->belongsToMany(self::class, 'matches', 'user_id', 'match_id')->withPivot('confirmed');
}

Then I run:

User::first()->matchesSet->where('pivot.confirmed', true)->where('name','LIKE', 'John')->count()

or

User::first()->matchesSet->where('pivot.confirmed', true)->where('name','John')->count()

and I get the expected result: "1" in this case.

BUT if I add % to the LIKE query, in any of this variants:

User::first()->matchesSet->where('pivot.confirmed', true)->where('name','LIKE', '%John%')->count()

User::first()->matchesSet->where('pivot.confirmed', true)->where('name','LIKE', '%{John}%')->count()

User::first()->matchesSet->where('pivot.confirmed', true)->where('name','LIKE', "%John%")->count()

User::first()->matchesSet->where('pivot.confirmed', true)->where('name','LIKE', "%{John}%")->count()

then I get wrong result, in all cases: "0".

What's wrong with my LIKE queries?

Thank you!

laravel relationship
2 Answers

You need to call the matchesSet as a function to trigger a query and use wherePivot to query the pivot attribute:

User::first()->matchesSet()->wherePivot('confirmed', true)->where('name', 'like', '%John%')->count();

Otherwise, you are simply referring to the where function of Illuminate\Database\Eloquent\Collection.

For more information, look under Filtering Relationships Via Intermediate Table Columns: https://laravel.com/docs/7.x/eloquent-relationships#many-to-many

user_55564400

Try this code,

$q = 'John'
User::first()
->matchesSet
->where('pivot.confirmed', true)
->where('name', 'LIKE', '%' . $q . '%')->count()
user_104964490
Related