这是我的基本查询:
$base_query = TableOne::join('table_two as p1', 'p1.order_id', '=', 'table_ones.id')
->join('table_threes as j1', 'p1.id', '=', 'j1.partner_order_id')
->select('table_ones.*')
->groupBy('table_ones.id', 'j1.status');
当有人需要过滤一些数据(如表table_two partner_id(时,我们会添加一些额外的列,如下所示,
$base_query->where(function ($query) {
$query->whereNull('p1.cancelled_at');
$query->orWhere('p1.cancelled_at', '=', DB::select(DB::raw("SELECT MAX(p2.cancelled_at) FROM partner_orders p2 WHERE p2.order_id = p1.order_id")));
$query->whereNotExists(function ($query) {
DB::select(DB::raw("SELECT * FROM partner_orders p3 WHERE p3.order_id = p1.order_id AND p3.cancelled_at IS NULL"));
});
});
但是运行此查询后,出现错误
SQLSTATE[42S22]:找不到列:1054 未知列"p1.order_id" 在"WHERE 子句"(SQL:从中选择 MAX(p2.cancelled_at( partner_orders p2 其中 p2.order_id = p1.order_id(
我认为,该查询存在一些问题。
$base_query->where(function ($query) {
$query->whereNull('p1.cancelled_at');
$query->orWhere('p1.cancelled_at', '=', DB::select(DB::raw("SELECT MAX(p2.cancelled_at) FROM partner_orders p2 WHERE p2.order_id = p1.order_id")));
$query->whereNotExists(function ($query) {
DB::select(DB::raw("SELECT * FROM partner_orders p3 WHERE
p3.order_id = p1.order_id AND p3.cancelled_at IS NULL"));
});
});
'
DB::select()
直接执行查询。
在orWhere()
的情况下,只使用原始表达式。
$query->orWhere('p1.cancelled_at', '=', DB::raw("(SELECT MAX(p2.cancelled_at) [...])"));
在whereNotExists()
的情况下,使用whereRaw()
:
$query->whereRaw("NOT EXISTS(SELECT * [...])");
在这两种情况下,您还可以使用闭包并手动构建查询:
$query->orWhere('p1.cancelled_at', '=', function($query) {
$query->from('partner_orders')->select([...])->where([...]);
})
$query->whereNotExists(function($query) {
$query->from('partner_orders as p3')->where([...]);
})