Select an item which has all of a set of categories

Hi guys,

This is a crosspost from stack overflow btw. Figured I'd post here as well considering this is like the go-to laravel location :P http://stackoverflow.com/questions/41051514/laravel-eloquent-selecting-an-item-which-has-all-any-categories

Basically I am making a quiz site, I want the user to be able to select a number of categories, and then a random question will be selected from questions which have all of the selected categories.

(there will be an option to select questions that have any of the categories, but that should be similar to this answer)

Basically I know how to get all the categories a question has, as well as all the questions a category has... but I'm not sure how to get all the questions that have several categories.

I don't really have much code to post here as I'm sort of stalling as to what commands to use, as it's dealing with pivot tables. I've tried the wherePivot, but that seems to only apply to a single question or single category.

davielee

9 years ago

Best Answer

Level 11

So there might be a way straight from the database, but I couldn't figure it out. So with a bit of Collection filtering you should be able to get what you're looking for.

public function index()
{
    $categories = [1, 2];

    $questions = App\Question::with([
        'categories' => function ($query) use ($categories) {
            $query->wherePivotIn('category_id', $categories);
        }
    ])->get();

    $filtered = $questions
        ->filter(function ($question) use ($categories) {
            return $question->categories
                ->pluck('pivot.category_id')
                ->intersect($values)
                ->count() >= count($values);
        });
}

Hope that helps you until/if you can figure it out straight through the DB.

1 like

Please or to participate in this conversation.