How to do this in Laravel, subquery where in

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.

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();

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
)

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();

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.

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` = ?

The following code worked for me:

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

            })
->get();

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();

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

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();
	

using a variable

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

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();