How to filter tags boundly?

Hello. Want to do tags table, which can help sort of films with special parameters. The reason of it to show all films that have all of checked tags.

This is controller, which show all films checked by tags, he works:

` public function show(Request $request): View { $tag = $request->tags;

    $films = Film::whereHas('tags', function ($query) use ($tag) {
        $query->whereIntegerInRaw ('id', $tag);
    })->paginate(20)->withQueryString();

    return view('films.tagresult', [
        'films' => $films
    ]);
}

But he show all the films where has at least one of checked tags.

Let's some example. Tags connected via foreign table like:

film_id tag_id 419 | 13 443 | 8 639 | 10 443 | 13 and so on...

And if I done query with tags 8 and 13 I want to display only those movies that have both tags (film with id 443, NOT 419), or three selected tags which have in one film, and so on.

Something like: SELECT * FROM film_tagsWHEREtag_id = 13 AND 19; but this query work how I described above. This query show films with at least of checked tags

Exclusively so, and so that the results do not include films in which there is only one tag from the request.

Is it possible to solve my question and how to make at least a SQL request?

Sinnbeck

3 years ago

Level 102

I assume it's mysql as it won't work with postgres

$films = Film::whereHas('tags', function ($query) use ($tag) {
        $query->whereIntegerInRaw ('id', $tag);
    })->withCount(['tags' => function ($query) use ($tag) {
        $query->whereIntegerInRaw ('id', $tag);
    }])->having('tags_count', '=', count($tag)->paginate(20)->withQueryString();

1 like

tykus

3 years ago

Best Answer

Level 104

whereHas Builder method takes an optional count as the fourth argument - set this as the count of Tags to be matched:

 public function show(Request $request): View
{
    $tag = $request->tags;

    $films = Film::query()
        ->whereHas(
            'tags',
            fn ($query) => $query->whereIn('id', $tags),
            '=',
            count($tags)
        )->paginate(20)->withQueryString();

    return view('films.tagresult', [
        'films' => $films
    ]);
}

1 like

Uladzimir

3 years ago

Level 1

@tykus thank you. looks like a good)

tykus

3 years ago

Level 104

@Uladzimir if it solves you issue, please mark the thread solved 👍

Sinnbeck

3 years ago

Level 102

@tykus Oh interesting. I recall this having some issue back in the day, but apparently not. I will switch to this solution in the future :) Thanks

Please or to participate in this conversation.