thinkphp:数据库多条件查询

一、使用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();