在codeigniter样式中写联合查询 [英] write union query in codeigniter style

问题描述

如何在codeigniter风格中写下列查询。

How can i write 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

我使用以下方式在codeignitre风格中执行Query。
是正确的方法还是有任何建议纠正方式

i have used the following way to execute Query in codeignitre style. Is it correct way or have any suggestion to correct way???

 $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屋！