Laravel multiple query (PHP/MySQL)

I have a table named assignations where I store the id of the category and the id of the product assigned to this category. Like this:
Laravel multiple query (PHP/MySQL)

The query that I am looking for is to get all the products that are not assigned to a specific category. For example, if the category is id=1, the query would have to return the products 4, 5 and 6.

This is the code I have tried but I can’t achieve what I want.

$assignations = Assignation::where('category_id', $id)->orderBy('position', 'Asc')->get();
$no_variants_assigned = Variant::whereNotIn('id', function ($query) use ($assignations) {
          ->from('variants', 'v')
          ->where('v.id_product', $assignations->id);
})->orderBy('id_product', 'Asc')->get();

Please, I hope someone can help me.


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Try this code :

$id[] = 1;

$assigns =  array_unique(Assignation::whereNotIn('category_id', $id)->pluck('product_id')->toArray());
$products = Product::whereIn('id', $assigns)->orderBy('id', 'Asc')->get();

return $products;


Added checking the category if exists before getting data, so the code should be:

$category = Category::findOrFail($id);
$assigns =  array_unique(Assignation::where('category_id', $id)->pluck('product_id')->toArray());
$variants = Variant::whereNotIn('id', $assigns)->orderBy('id_product', 'Asc')->get();

Method 2

Te @Pejman Kheyri answer is not what I want, but he helps me to find the solution:

public function getCategoryVariantAssignation($id){
        $category = Category::findOrFail($id);
        $assignations = array_unique(Assignation::where('category_id', $id)->pluck('product_id')->toArray());
        $no_variants_assigned = Variant::whereNotIn('id', $assignations)->orderBy('id_product', 'Asc')->get();
        $data = ['category' => $category, 'variants' => $no_variants_assigned];
        return $data;

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x