Laravel multiple query (PHP/MySQL)

Question

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:

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) {
    $query->select('v.id')
          ->from('variants', 'v')
          ->where('v.id_product', $assignations->id);
})->orderBy('id_product', 'Asc')->get();

Please, I hope someone can help me. Thanks

Pejman Zeynalkheyri · Accepted Answer · 2021-10-22 08:37:31Z

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;

Edit:

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();

edited Oct 22, 2021 at 8:37

answered Oct 21, 2021 at 13:18

Pejman Zeynalkheyri

4,93810 gold badges35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

martibellot Over a year ago

This is not correct because I want all products that are not assigned to a specific category, and with your first query, you are not taken into account that some products could have not been assigned to any category yet.

martibellot · Accepted Answer · 2021-10-21 16:12:41Z

0

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;
}

answered Oct 21, 2021 at 16:12

martibellot

831 silver badge11 bronze badges

Collectives™ on Stack Overflow

Laravel multiple query (PHP/MySQL)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related