untymage's avatar

N+1 when using 'wherePivot'

I have polymorphic many-to-many between Tag and other models and have a extra column in pivot table with name of type , So when i try to get tags with specific type i get into n+1 problem:

class Thread extends Model
{
    protected $with = ['tags'];

    public function tags()
    {
        return $this->morphToMany(Tag::class, 'taggable')
            ->withPivot('type');
    }

    public function getMainTags()
    {
        return $this->tags()->wherePivot('type', 1)->get();
    }


}

class ThreadTagsController extends Controller
{

    public function show(Thread $thread)
    {
        return ThreadResource::collection($thread->getMainTags());
    }
}

it perform 70 queries : $thread->getMainTags()

but it only have 5 queries: $thread->tags

what did i miss here ? I also added protected $with = ['tags']; at the top.

0 likes
6 replies
ahmeddabak's avatar

in your Thread model write your code like this

    public function getMainTags()
    {
        return $this->tags()->with('pivot')->wherePivot('type', 1)->get();
    }

i think it should work

untymage's avatar

Doesnt work

Call to undefined relationship [pivot] on model [App\Thread].

untymage's avatar

@ahmeddabak there is nothing noticeable in that, Just wondering if you visit the Thread and it have three tags on it (pivot table), Is it ok the $thread->getMainTags performs 3 queries ?

ahmeddabak's avatar
ahmeddabak
6 years ago
Best Answer
Level 47

No it is not, it should be loaded with one query, but when you call a relation like a property $this->tags it gets loaded once and cached and every time you call again laravel will use the cached value.

but if you use $this->tags() to call the relation, every time you call laravel will make a new query. so it can be that the queries are not being made on the tags or threads table, but on some other relation that you are referencing in ThreadResource

Please or to participate in this conversation.