CODEWITHSUNDEEP
sqllaravellaravel-5
Ashok Adhikari
Ashok Adhikari

Reputation: 203

How to write conditional query with combination of two columns as a single query in Laravel?

I have three tables products, product_meta_keys, and product_meta_values. Product table stores product's general information like name and product_meta_keys stores the dynamic features/specification key of product like product brand whereas product_meta_values table stores the corresponding value for given product and given meta_key. Here are sample table structure:

Product Table

enter image description here

Product Meta Key table

enter image description here

Product meta value table

enter image description here

In this table product_id and meta_key_id is foreign key from product and product_meta_key table.

Now, What I want is, to query into product_meta_values table to find the product ids using the conditional query with the combination of two column (meta_key_id and meta_value) as a single condition.

Here is the query that I have tried (Ignore the join part for now):

\DB::table('product_meta_values')
    ->select('product_id as product')
    ->where(function($query){
        $query->where('meta_key_id', 1)
              ->where('meta_value', 'MValue 1');
    })
    ->where(function($query){
        $query->where('meta_key_id', 2)
              ->where('meta_value', 'MValue 2');
    })
    ->distinct()
    ->get();

enter image description here

What I expect is

+---------+ | product | +---------+ | 1 | +---------+

But got Empty set. Any idea?

Thanks in Advance.

Upvotes: 2

Views: 626

Answers (2)

Nakarmi
Nakarmi

Reputation: 453

SELECT
x1.product_id
FROM
(
    SELECT
        product_id
    from
        meta_values
    where
        (
            meta_key_id = 1
            AND meta_value = 'MValue1'
        )
) as x1 ,
(
    SELECT
        product_id
    from
        meta_values
    where
        (
            meta_key_id = 2
            AND meta_value = 'MValue2'
        )
) as x2
 where
  x1.product_id = x2.product_id

The problem with your query was using AND with multiple column on a single row, what AND does is strictly search for matching results in a single row. Which is kind of tricky. Try using subqueries for these kinds of problem.

Upvotes: 2

JorenV
JorenV

Reputation: 352

You should use joins for these kinds of problems:

Product::
join('pmv', 'pmv.product_id', '=', $product->id)->
join('mk', 'pmv.meta_value', '=', 'mk.id')->
where( //add conditions here// )->where('key', '=', 'value')->
select('products.name', 'pmv.field1', 'mk.fieldX')->
get();

Where pmv is product_meta_values and mk is meta_keys. Note that you should add the name of the table before you query any field, since you need to tell the query in what table to look for the field.

Upvotes: 0

Related Questions