Write union query in codeigniter style

Question

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 ");

possible duplicate of [UNION query with codeigniter's active record pattern](http://stackoverflow.com/questions/2040655/union-query-with-codeigniters-active-record-pattern) — Sean Fahey, Sep 30 '14 at 19:19

score 19 · Answer 1 · answered Mar 06 '16 at 21:38

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.

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();
}

If you intend to use this make sure that your both the table column are same sequence and name. — always-a-learner, Aug 24 '17 at 06:04
A very nice way of using get_compiled_select, which I normally would use for debugging only. Not any more. — Herbert Van-Vliet, Dec 17 '19 at 22:23

score 1 · Answer 2 · answered Mar 09 '15 at 21:47

You can use CI to generate a union query. However, latest versions made this much harder than before.

DB has a method called _compile_select, in previous versions of CI it was public, however now it is protected so you can't just call $this->db->_compile_select() from your controller. In order to do this properly one could:

Create custom loader class to be able to extend core/database classes (i.e. load MY_DB_active_record instead of CI_DB_active_record).

Create custom activerecord class, with just one method:

public function compile_select() {
    return $this->_compile_select();
}

In your controller, create all necessary queries, compile them into a string array using our public method compile_select()
Join the array into single query: '(' . implode(') UNION (', $queries) . ')'. You can also wrap this into a separate method inside your custom AR class.

score -2 · Answer 3 · answered Feb 20 '13 at 11:27

-2

function get_merged_result($ids){                   
    $this->db->select("column");
    $this->db->distinct();
    $this->db->from("table_name");
    $this->db->where_in("id",$model_ids);
    $this->db->get(); 
    $query1 = $this->db->last_query();

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

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

    return $query->result();
}

answered Feb 20 '13 at 11:27

Shaolin

2,541
4
30
41

Here you are running 2 queries just to get the SQL output and join them with a union? Might as well write the query in the first place as the OP has done. – dakdad Feb 20 '13 at 12:24
1

Nop, You can get more details by reading this article, http://codesamplez.com/database/codeigniter-activerecord – Shaolin Feb 20 '13 at 12:30
1

The problem is, `$this->db->get();` is going to execute the query. So, you are running 2 queries, just to get the SQL statement for the third! You can just write it as a string and pass to the `$this->db->query($sql);` function. – dakdad Feb 21 '13 at 00:16

Write union query in codeigniter style

3 Answers3

Linked

Related