( (Where and Where) OR (Where and Where) ) CodeIgniter

Question

这是laravel的引用：( (Where and Where) OR (Where and Where) ) Laravel 5.2

这是我的代码：

$this->db->select('BaseTbl.*');
$this->db->from('tablename as BaseTbl');
// *************** WHERE THIS Based on sendMeetingSchedule = 'YES' ****************
$this->db->where('BaseTbl.companyId', 10);
$this->db->where_not_in('BaseTbl.status', array('Draft'));
$this->db->where('BaseTbl.description !=', '');
$this->db->where('BaseTbl.sendMeetingSchedule', 'Yes');
// *************** OR THIS Based on sendMeetingSchedule = 'NO' ****************
$this->db->or_where('BaseTbl.sendMeetingSchedule', 'No');
$this->db->where('BaseTbl.status', 'Pending Meeting');
$this->db->order_by('BaseTbl.id', 'desc');

这是我的查询输出：

SELECT `BaseTbl`.* FROM `tablename` as `BaseTbl` WHERE `BaseTbl`.`companyId` = '10' AND `BaseTbl`.`status` NOT IN('Draft') AND `BaseTbl`.`description` != '' AND `BaseTbl`.`sendMeetingSchedule` = 'Yes' OR `BaseTbl`.`sendMeetingSchedule` = 'No' AND `BaseTbl`.`status` = 'Pending Meeting' ORDER BY `BaseTbl`.`id` DESC

我想要基于sendMeetingSchedule列的where子句。如果它是'YES‘，那么运行上面的where子句，否则在codeigniter中运行下面的where子句。

这是我想要的查询输出：

SELECT `BaseTbl`.* FROM `tablename` as `BaseTbl` WHERE (`BaseTbl`.`companyId` = '10' AND `BaseTbl`.`status` NOT IN('Draft') AND `BaseTbl`.`description` != '' AND `BaseTbl`.`sendMeetingSchedule` = 'Yes') OR (`BaseTbl`.`sendMeetingSchedule` = 'No' AND `BaseTbl`.`status` = 'Pending Meeting') ORDER BY `BaseTbl`.`id` DESC

注意:不使用自定义where子句：

$this->db->where('MATCH (field) AGAINST ("value")', NULL, FALSE);

Answer 1

左方括号和右方括号需要使用group_start()和group_end()。

$this->db->select('BaseTbl.*');
$this->db->from('tablename as BaseTbl');

// *************** WHERE THIS Based on sendMeetingSchedule = 'YES' ****************
$this->db->group_start() // Open bracket;
$this->db->where('BaseTbl.companyId', 10);
$this->db->where_not_in('BaseTbl.status', array('Draft'));
$this->db->where('BaseTbl.description !=', '');
$this->db->where('BaseTbl.sendMeetingSchedule', 'Yes');
$this->db->group_end() // Close bracket;

// *************** OR THIS Based on sendMeetingSchedule = 'NO' ****************
$this->db->or_group_start() // Open bracket;
$this->db->where('BaseTbl.sendMeetingSchedule', 'No');
$this->db->where('BaseTbl.status', 'Pending Meeting');
$this->db->group_end() // Close bracket;
$this->db->order_by('BaseTbl.id', 'desc');

注意:适用于Codeigniter 3.0.3或更高版本