php 如何在 Laravel 中执行此操作，子查询 where

How to do this in Laravel, subquery where in

提问by Marc Buurke

How can I make this query in Laravel:

如何在 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.

我也可以通过连接来做到这一点，但我需要这种格式来提高性能。

回答by lukaserat

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

回答by drewjoh

Have a look at the advanced wheres documentation for Fluent: http://laravel.com/docs/queries#advanced-wheres

查看 Fluent 的高级 wheres 文档：http: //laravel.com/docs/queries#advanced-wheres

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
)

回答by Ramesh

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

您可以通过使用关键字“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();

回答by Aditya Singh

The following code worked for me:

以下代码对我有用：

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

            })
->get();

回答by Philipe

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

你可以在不同的查询中使用 Eloquent，让事情更容易理解和维护：

$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.

这将生成您在问题中编写的相同查询。

回答by Madan Sapkota

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

该脚本在 Laravel 5.x 和 6.x 中进行了测试。static在某些情况下，闭包可以提高性能。

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

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

回答by a3rxander

using a variable

使用变量

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

回答by LC Yoong

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

Laravel 4.2 及更高版本，可以使用尝试关系查询：-

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

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

回答by panqingqiang

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

回答by liquid207

Please try this online tool sql2builder

请试试这个在线工具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();