Laravel 查询生成器 - 选择另一个表具有其 ID 的所有位置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32117205/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel query builder - Select all where another table has their ID
提问by Josh Undefined
Basically, I'm trying to select everything from a table using Query Builder if there's a record in another table that meets some conditions. At the moment I have the code below, however it's very slow on a table with 100K rows.
基本上，如果另一个表中有满足某些条件的记录，我会尝试使用查询生成器从表中选择所有内容。目前我有下面的代码，但是在有 100K 行的表上它非常慢。
$result= [];
$customers = (array)DB::table('customers')->where('lastName','LIKE', Input::get('letter').'%')->orderBy('lastName','ASC')->get();
foreach($customers as $k => $v)
{
if(DB::table('orders')->where('disabled','=','')->where('customerId','=',$v->id)->where('status','!=',0)->count() > 0)
{
array_push($result, $v);
}
}
Any suggestions would be much appreciated! At the moment this times out after 5 minutes.
我们欢迎所有的建议！目前这在 5 分钟后超时。
回答by jedrzej.kurylo
At the moment you're running one query to fetch customers and then a query for every customer to fetch related orders. If you have many customers it will result in a high number of queries you need to execute.
目前，您正在运行一个查询来获取客户，然后为每个客户运行一个查询以获取相关订单。如果您有很多客户，这将导致您需要执行大量查询。
You can do this with a single query by joining those 2 tables.
您可以通过加入这两个表来使用单个查询来完成此操作。
This will do the trick:
这将解决问题：
//get all customers
$results = DB::table('customers')
//filter customers by lastName
->where('customers.lastName','LIKE', Input::get('letter').'%')
//take only customers that have orders matching criteria below
->join('orders', function($query) {
//link customer to their orders
$join->on('orders.customerId', '=', 'customers.id');
//consider only enabled orders
$join->where('orders.disabled','=','');
//consider only orders where status != 0
$join->where('orders.status','!=',0);
})
//if customer has multiple orders matching criteria
//they will be returned multiple time, so you need to group by
//customers.id to get only one row per customer
->groupBy('customers.id')
//order by customers last name
->orderBy('customers.lastName','ASC')
->get();
回答by mdamia
you can try something like this
你可以试试这样的
$data = Customer::with('Order') -> where('lastName','like',$name) -> whereExists(function($query){
$query->select(DB::raw(1)) -> from('orders')
->whereRaw('orders.customer_id= customer.id')
->where('disabled','=','')
->where('status','!=',0);
})-> orderBy('lastname','ASC') -> get() ->toArray();
