CODEWITHSUNDEEP
mysqllaraveleloquentmany-to-manypivot-table
Hillcow
Hillcow

Reputation: 969

Efficient MySQL/Eloquent query to filter posts by more than 1 tag

The setup consists of two tables (threads, tags) plus a pivot table (tag_thread) to allow the many-to-many relationship: One thread can have many tags, one tag can be used for many threads.

Attributes of the tables:

treads: id, title

tags: id, name

threads: id, thread_id, tag_id

Now I would like to filter through threads using not one but many tags. So I want to get all threads that are associated with all the given tags? Using only one I can easily do it via Laravel Eloquent using models like:

$filteredThreads = $tag->threads()->get()

I have managed to do it with two tags, but I feel like this is a very dirty and inefficent way to do it (especially considering that it goes through the whole list of ids to compare):

$threadIds = Thread::select('th.id')
        ->from('threads AS th')
        ->join('tag_thread AS tt', 'tt.thread_id', 'th.id')
        ->join('tags AS ta', 'tt.tag_id', 'ta.id')
        ->where('ta.name', $tag)
        ->pluck('id')
        ->toArray();

return $filteredThreads->whereIn('threads.id', $threadIds);

Can you think of a better solution? Thank you!

Upvotes: 1

Views: 124

Answers (1)

Alexey Mezenin
Alexey Mezenin

Reputation: 163788

If you want to get threads that have tag with one of the names defined in $tagsNames array:

$tagsNames = ['popular', 'cool'];
Thread::whereHas('tags', function($q) use($tagsNames) {
    $q->whereIn('name', $tagsNames);
})->get();

Upvotes: 3

Related Questions