一、使用if条件限制查询条件
$query = Db::table('wip_operation_plan')
->alias('d')
->join(['wip_jobs_all' => 'a'], 'a.wip_entity_name = d.wip_entity_name')
->join(['sf_item_no' => 'c'], 'a.primary_item=c.item_no')
->field('
d.*,c.item_no as item_no,c.item_name as item_name,c.units as uom,
d.creation_date creation_date,a.creation_date as wip_creation_date
')
->where([
'd.wip_entity_name' => ['like', '%' . $like_info . '%']
])
->where('d.begin_quantity > d.output_quantity+d.bad_quantity');
//添加条件
if (!empty($search_itemno)) {
$query->where(['c.item_no' => ['like', '%' . $search_itemno . '%']]);
}
if (!empty($search_itemname)) {
$query->where(['c.item_name' => ['like', '%' . $search_itemname . '%']]);
}
if (!empty($search_wip)) {
$query->where(['d.wip_entity_name' => ['like', '%' . $search_wip . '%']]);
}
if (!empty($search_opnum)) {
$query->where(['d.operation_seq_num' => ['like', '%' . $search_opnum . '%']]);
}
$query
->limit($start, $pageSize)
->order('a.wip_entity_name asc, d.operation_seq_num asc');
$data['info'] = $query->select();
二、直接在数据库查询中写入三目运算
$data['total'] = Db::table('wip_operation_plan')
->alias('d')
->join(['wip_jobs_all' => 'a'], 'a.wip_entity_name = d.wip_entity_name')
->join(['sf_item_no' => 'c'], 'a.primary_item=c.item_no')
->where([
'd.wip_entity_name' => ['like', '%' . $like_info . '%']
])
->where('d.begin_quantity > d.output_quantity+d.bad_quantity')
->where(!empty($search_itemno) ? ['c.item_no' => ['like', '%' . $search_itemno . '%']] : [])
->where(!empty($search_itemname) ? ['b.item_name' => ['like', '%' . $search_itemname . '%']] : [])
->where(!empty($search_wip) ? ['d.wip_entity_name' => ['like', '%' . $search_wip . '%']] : [])
->where(!empty($search_opnum) ? ['d.operation_seq_num' => ['like', '%' . $search_opnum . '%']] : [])
->count();