How to do this in Laravel, subquery where in

Question

How can I make this query in Laravel:

SELECT 
    `p`.`id`,
    `p`.`name`, 
    `p`.`img`, 
    `p`.`safe_name`, 
    `p`.`sku`, 
    `p`.`productstatusid` 
FROM `products` p 
WHERE `p`.`id` IN (
    SELECT 
        `product_id` 
    FROM `product_category`
    WHERE `category_id` IN ('223', '15')
)
AND `p`.`active`=1

I could also do this with a join, but I need this format for performance.

Alisander

Answer 1

Consider this code:

Products::whereIn('id', function($query){
    $query->select('paper_type_id')
    ->from(with(new ProductCategory)->getTable())
    ->whereIn('category_id', ['223', '15'])
    ->where('active', 1);
})->get();

Novikoff

Accepted this answer, question is out of date as it was concerning Laravel 3, and the answers coming in are for Laravel 4, answer below will work for 4 aswell. – Alisander

@Novikoff the query in question is applying the AND p.active=1 check on products tables whereas your query is applying it to table of ProductCategory....right?? or is there something I am missing..? – Yettie

@Yettie yes, it is referring to ProductCategory. – Novikoff

Nice approach with the table name. It's a plus for me – Standford

Work fine for laravel 5.5 – Woaded

Answer 2

Have a look at the advanced where clause documentation for Fluent. Here's an example of what you're trying to achieve:

DB::table('users')
    ->whereIn('id', function($query)
    {
        $query->select(DB::raw(1))
              ->from('orders')
              ->whereRaw('orders.user_id = users.id');
    })
    ->get();

This will produce:

select * from users where id in (
    select 1 from orders where orders.user_id = users.id
)

Iny

That comes close, and i've been puzzeling with similar queries for some time now. But where_in (laravel 3) requires 2 arguments, the second one being an array. Any idea how to get this right? Also, I don't think laravel 3 supports the from method. – Alisander

Ah, Laravel3... Yeah, that's going to be difficult then. And I think in Laravel3 you use the table() method instead of the from(). I haven't had that situation in L3, sorry! – Iny

I can't see a whereIn method which takes a lambda in Illuminate\Database\Query\Builder has it been renamed whereSub? – Althaalthea

Answer 3

You can use variable by using keyword "use ($category_id)"

$category_id = array('223','15');
Products::whereIn('id', function($query) use ($category_id){
   $query->select('paper_type_id')
     ->from(with(new ProductCategory)->getTable())
     ->whereIn('category_id', $category_id )
     ->where('active', 1);
})->get();

Gothart

Answer 4

Here is my approach for Laravel 8.x gathered from multiple answers here:

• Use the query builder and don't write SQL directly.
• Use the models and determine everything from there. Don't use a hardcoded table name, or any name (columns, and so on) for that matter.

Product::select(['id', 'name', 'img', 'safe_name', 'sku', 'productstatusid'])
    ->whereIn('id', ProductCategory::select(['product_id'])
        ->whereIn('category_id', ['223', '15'])
    )
    ->where('active', 1)
    ->get();

Tat

Answer 5

You can use Eloquent in different queries and make things easier to understand and mantain:

$productCategory = ProductCategory::whereIn('category_id', ['223', '15'])
                   ->select('product_id'); //don't need ->get() or ->first()

and then we put all together:

Products::whereIn('id', $productCategory)
          ->where('active', 1)
          ->select('id', 'name', 'img', 'safe_name', 'sku', 'productstatusid')
          ->get();//runs all queries at once

This will generate the same query that you wrote in your question.

Sagesagebrush

Answer 6

The script is tested in Laravel 5.x and 6.x. The static closure can improve performance in some cases.

Product::select(['id', 'name', 'img', 'safe_name', 'sku', 'productstatusid'])
            ->whereIn('id', static function ($query) {
                $query->select(['product_id'])
                    ->from((new ProductCategory)->getTable())
                    ->whereIn('category_id', [15, 223]);
            })
            ->where('active', 1)
            ->get();

generates the SQL

SELECT `id`, `name`, `img`, `safe_name`, `sku`, `productstatusid` FROM `products` 
WHERE `id` IN (SELECT `product_id` FROM `product_category` WHERE 
`category_id` IN (?, ?)) AND `active` = ?

Nephridium

Is there any way to use a scope of ProductCategory? – Binion

@SergeiKrivosheenko Yes, look at my answer to this question. – Tat

Answer 7

The following code worked for me:

$result=DB::table('tablename')
->whereIn('columnName',function ($query) {
                $query->select('columnName2')->from('tableName2')
                ->Where('columnCondition','=','valueRequired');

            })
->get();

Rehearsal

Answer 8

Laravel 4.2 and beyond, may use try relationship querying:-

Products::whereHas('product_category', function($query) {
$query->whereIn('category_id', ['223', '15']);
});

public function product_category() {
return $this->hasMany('product_category', 'product_id');
}

Bromate

Answer 9

Product::from('products as p')
->join('product_category as pc','p.id','=','pc.product_id')
->select('p.*')
->where('p.active',1)
->whereIn('pc.category_id', ['223', '15'])
->get();

Coyote

Answer 10

using a variable

$array_IN=Dev_Table::where('id',1)->select('tabl2_id')->get();
$sel_table2=Dev_Table2::WhereIn('id',$array_IN)->get();

Cabezon

This will result in two querys and not just one. You can insert the first query builder into the second one, without getting the result of the first quary. See Philipes answer for more details. – Tat

Answer 11

You use DB::raw to set subquery.

Example

DB::raw('(
    SELECT 
    `product_id` 
    FROM `product_category`
    WHERE `category_id` IN ('223', '15') as `product_id`
')

Metaphosphate

Answer 12

Please try this online tool sql2builder

DB::table('products')
    ->whereIn('products.id',function($query) {
                            DB::table('product_category')
                            ->whereIn('category_id',['223','15'])
                            ->select('product_id');
                        })
    ->where('products.active',1)
    ->select('products.id','products.name','products.img','products.safe_name','products.sku','products.productstatusid')
    ->get();

Penang