在ci中使用where not in查询 - using where not in query in ci

using where not in query in ci

本文关键字：not in 查询 where ci | 更新日期: 2023-09-27

$result3=$this->hrdb->select(array('concat(eb.first_name," ",eb.middle_name, " ",eb.last_name,"") as emp_name','eb.id','pp.emp_id','pp.gross_monthly','pp.amount as net_amount','pp.payslip_id','eor.dep_id'))
                    ->where('pp.emp_id' not_in                    (select(array('pp.gross_monthly','pp.emp_id','pp.amount as net_amount','pp.payslip_id'))),Null,FALSE)
                    ->from('payroll_payments as pp')
                    ->join('employee_basic as eb','pp.emp_id=eb.id','left')
                    ->join('employee_org as eor','pp.emp_id=eor.emp_id','left')
                    ->order_by($_field, $params['_sort_direction'])
                    ->where(array('pp.npyear'=>$params['nep_year'],'pp.npmonth'=>$params['nep_month2']))
                    ->get()
                    ->result();

是我写的查询，但是告诉

出现问题

解析错误:语法错误，未预料的T_STRING/var/www/html/vianet_db/htdocs/addons/shared_addons/模块/人力资源管理/模型/payslip_m.php第558行

使用concat(eb.first_name." ".eb.middle_name." ".eb.last_name."") as emp_name并检查。从查询中删除'引号并简单地添加字段名。用eb.id,pp.emp_id代替'eb.id','pp.emp_id'。对select section中所有不需要的字段和array id执行

您需要在SELECT (first SELECT also)中添加第二个可选参数FALSE，以便不自动转义字符。

$subqueryResult = //your subquery result in array
$result3=$this->hrdb->select(array('concat(eb.first_name," ",eb.middle_name, " ",eb.last_name,"") as emp_name','eb.id','pp.emp_id','pp.gross_monthly','pp.amount as net_amount','pp.payslip_id','eor.dep_id'), FALSE)
                    ->from('payroll_payments as pp')
                    ->join('employee_basic as eb','pp.emp_id=eb.id','left')
                    ->join('employee_org as eor','pp.emp_id=eor.emp_id','left')
                    ->where_not_in('pp.emp_id', $subqueryResult)
                    ->where(array('pp.npyear'=>$params['nep_year'],'pp.npmonth'=>$params['nep_month2']))
                    ->order_by($_field, $params['_sort_direction'])
                    ->get()
                    ->result();