以 codeigniter 风格编写联合查询 [英] Write union query in codeigniter style

问题描述

如何以 Codeigniter 样式编写以下查询.

How can I write the following query in Codeigniter style.

SELECT COUNT(`id`) AS reccount
  FROM 
    (SELECT `id` FROM table1 
     WHERE tid= '101' AND `status` =  1
     UNION ALL
    SELECT `id` FROM table2 
     WHERE tid= '101' AND `status` =  1
     UNION ALL
    SELECT `id` FROM table3
     WHERE tid= '101' AND `status` =  1) t

我用下面的方式执行过.

I have used the following way to execute it.

这是唯一正确的方法还是您有什么改进的建议?

Is it the only correct way or do you have any suggestion to improve it?

 $q = $this->db->query(SELECT COUNT(`id`) AS reccount
                        FROM 
                        (SELECT `id` FROM table1 
                         WHERE tid= '101' AND `status` =  1
                         UNION ALL
                         SELECT `id` FROM table2 
                         WHERE tid= '101' AND `status` =  1
                         UNION ALL
                         SELECT `id` FROM table3
                         WHERE tid= '101' AND `status` =  1) t ");

推荐答案

自 CodeIgniter 3 起，Active Record 中引入了函数 get_compiled_select()，该函数无需实际执行查询即可给出查询字符串.

Since CodeIgniter 3 it's been introduced in Active Record the function get_compiled_select() that gives the query string without actually executing the query.

这允许@MDeSilva 方法使用更少的资源，调整如下:

This allows @MDeSilva method to use less resources, being adapted as follows:

function get_merged_result($ids){                   
    $this->db->select("column");
    $this->db->distinct();
    $this->db->from("table_name");
    $this->db->where_in("id",$model_ids);
    $query1 = $this->db->get_compiled_select(); // It resets the query just like a get()

    $this->db->select("column2 as column");
    $this->db->distinct();
    $this->db->from("table_name");
    $this->db->where_in("id",$model_ids);
    $query2 = $this->db->get_compiled_select(); 

    $query = $this->db->query($query1." UNION ".$query2);

    return $query->result();
}

这篇关于以 codeigniter 风格编写联合查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！