Laravel combination query of two tables-CodePudding

I have the following tables:

What I want is to get the product_types_values ptv where their ptv.type_id == 1 and not exist any variant v where their v.id_type == 1 and its v.value == ptv.value

In SQL it will be like:

SELECT *
FROM products_types_values ptv
WEHRE ptv.type_id = 1 and ptv.value not in (SELECT v.value
                                            FROM variants v
                                            WEHRE v.type_id = 1)

For example, in the tables of the image, the result I want to get is the products_types_values 8, 9, 10, 11, 12 and 13.

In the controller I get some variants and product_types_values, and store them on $variants and $products_types_values. Maybe the solution is to work with these variables or to make another query.

My code is:

$variants = Variant::where('id_product', $id)->orderBy('id', 'Asc')->paginate(10);
$products_types_values = ProductTypeValue::where('type_id', $product->type_id)->orderBy('value', 'Asc')->get();

CodePudding user response：

ProductTypeValue::where('type_id', $product->type_id)
                ->whereNotIn('value', function ($query) use ($product) {
                    $query->select('v.value')
                          ->from('variants', 'v')
                          ->where('v.type_id', $product->type_id);
                })
                ->get();