Laravel Query builder where() for when Product has to have multiple tags (with product

I am new to Laravel and I got a complicated query to build. I managed to sort it except for when a user asks for multiple tags (tags = 1, 2, 3). Product shown has to have all tags that the user asks (not only one or two but all of them).

I have the query in SQL (this example is two tags, I would switch it to different numbers based on how many tags are passed):

SELECT  m.*
FROM  meals m
WHERE EXISTS (
    SELECT 1
    FROM meals_tags t
    WHERE m.id = t.meals_id AND
        t.tags_id IN (227,25)
    HAVING COUNT(1) = 2
);

This one works perfectly, but I have an issue when translating it to an Eloquent query builder where method.

I have another where method included in the same query so I want to attach this one as well.

I have tried this:

DB::table('meals')
    ->select('id')
    ->where(function ($query) use ($parameters) {
        if (isset($parameters['tags'])) {
            $array = explode(',', $parameters['tags']);
            $query->select(DB::raw(1))
                ->from('meals_tags')
                ->where('meals.id', '=', 'meals_tags.meals_id')
                ->whereIn('meals_tags.tags_id', $array)
                ->having(DB::raw('COUNT(1)'), '=', count($parameters['tags']));
        }
    });

But I can't find a way. New to Laravel and PHP.

Let's say I have table meals and tags with meals_tags to connect them (many to many).

$paramaters are comming from GET (...?tags=1,2,3&lang=en&another=something...), they are an array of key-value pairs (['tags' => '1,2,3', 'lang' => 'en'])

$parameters['tags'] is of type string with comma separated numbers (positive integers greater than 0) so that I have the option to explode it if needed somewhere.

CodePudding user response：

Assuming that you have defined belongsToMany (Many-to-Many) meal_tags relationship on the Meal model, you can try

Meal::select('id')
    ->when(
        $request->has('tags'),
        function($query) use ($request) {
            $requestedTagIds = explode(',', $request->tags);

            return $query->whereHas(
                'meal_tags', 
                fn ($query) => $query->whereIn('tags_id', $requestedTagIds),
                '=', 
                count($requestedTagIds)
            );
        }
    )
    ->get();