SQL how to use condition on multiple column as one

March 10, 2022

My Table is

product_uuid                          key_name      key_value
95be9cf4-7121-492b-8725-762e6353ac51  categories    Starter
95be9cf4-7121-492b-8725-762e6353ac51  print_order   1
95be9cf4-7121-492b-8725-762e6353ac51  available     1
95be9cf4-7121-492b-8725-762e6353ac52  categories    Starter
95be9cf4-7121-492b-8725-762e6353ac52  print_order   2
95be9cf4-7121-492b-8725-762e6353ac52  available     1

Here i want to query all the product uuid which has category "Starter" and which has print_order "1" and available "1".

"?categories=Starter&print_order=1"

I Tried something like

$request = [
  "categories": "Starter",
  "print_order": "1"
]


$p = DB::table('product_properties');
foreach ($request->all() as $key => $value) {
    if($key === 'provider_uuid') {
       continue;
    }
    $p->Where([['key_name', '=', $key], ['key_value', '=', $value]]);
}
return $p->get();

But it doesnot gives the result

My Expected Result is

product_uuid
95be9cf4-7121-492b-8725-762e6353ac51

>Solution :

SELECT product_uuid
FROM tablename
WHERE (key_name, key_value) IN ( ('categories' , 'Starter'),
                                 ('print_order', '1'      ),
                                 ('available'  , '1'      ) )
GROUP BY product_uuid
HAVING COUNT(DISTINCT key_name) = 3