Laravel multiple query (PHP/MySQL)-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

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