基本上，我试图使用查询生成器从表中选择一切，如果在另一个表中有符合某些条件的记录。现在我有下面的代码，但是它在一个有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);
}
}
任何建议都将非常感谢!
目前，您正在运行一个查询来获取客户，然后为每个客户运行一个查询来获取相关订单。如果你有很多客户，这将导致你需要执行大量的查询。
您可以通过连接这两个表来实现单个查询。
可以这样做:
//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();
你可以尝试这样做
$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();