php - 如何在Codeigniter中将相关子查询用于事件记录查询

我正在努力从不同的表格中获取客户的详细信息。我已经创建并测试了这个查询，它运行良好。问题是如何将此查询转换为活动记录查询。谢谢你。

            SELECT c.cust_id, `c`.`cust_contact_name`, `c`.`cust_active_flag`,
            (select COUNT(`pm`.`cust_id`)  from property_master as pm
            Where pm.cust_id = c.cust_id) as prop_count,
            (select a.addr_phoneno1 from address as a
            WHERE a.cust_id = c.cust_id AND a.addr_type_flag IN ('C', 'CP')) as cust_phone,
            (select count(ci.cust_id) from customer_inquiry as ci
            WHERE ci.cust_id = c.cust_id) as inquiry_count,
            (select max(inq_date) from customer_inquiry as ci
            WHERE ci.cust_id = c.cust_id) as last_inq_date
            FROM customer as c
            GROUP BY c.cust_ID

最佳答案

CodeIgniter活动记录类不直接支持子查询。您可以使用任何第三方库，如NTICompass CodeIgniter-Subqueries或$this->db->query();来执行查询。
不过，你可以用下面的方法。但我建议使用$this->db->query();

$this->db->select('c.cust_id, `c`.`cust_contact_name`, `c`.`cust_active_flag`,
            (select COUNT(`pm`.`cust_id`)  from property_master as pm ' Where pm.cust_id = c.cust_id) as prop_count, (select a.addr_phoneno1 from address as a
            WHERE a.cust_id = c.cust_id AND a.addr_type_flag IN ('C', 'CP')) as cust_phone,
            (select count(ci.cust_id) from customer_inquiry as ci
            WHERE ci.cust_id = c.cust_id) as inquiry_count,
            (select max(inq_date) from customer_inquiry as ci
            WHERE ci.cust_id = c.cust_id) as last_inq_date)
         ->from('customer c');
$this->db->group_by('c.cust_ID');
$result = $this->db->get();

关于php - 如何在Codeigniter中将相关子查询用于事件记录查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43336994/