CodeIgniter Union Query Example

CodeIgniter Union Query: This tutorial will show you how to write union query in codeigniter. First let's set one thing clear. CodeIgniter Active Records doesn't support UNION queries! This doesn't mean you can't fire union queries in codeigniter. Always you can go with native sql statement and the framework supports it. But as good practice don't do this unless you have to.

Here I'll show you a workaround using active records to write union query in code igniter. Still it uses query() function but much better than the sql queries.

How to Write Union Query in CodeIgniter?

As I said earlier, you can't write pure active records query for table unions - because the framework supports only those SQL features which are compatible with all of its supported SQL types. As for UNION, you have to write sql query and execute it with ActiveRecord's query() method like below.

$sql = 'SELECT Name, Email FROM Customers UNION SELECT Name, Email FROM Suppliers';
$query = $this->db->query($sql);

Alternative Method:

This method employs slightly better approach than the previous one. It produces query strings from active records without actually running the queries, then 'union' them with $this->db->query(); function. Here is how to do it.

$this->db->select('Name, Email');
$this->db->from('Customers');
$query1 = $this->db->get_compiled_select();

$this->db->select('Name, Email');
$this->db->from('Suppliers');
$query2 = $this->db->get_compiled_select();

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

// Produces SQL:
// SELECT Name, Email FROM Customers
// UNION
// SELECT Name, Email FROM Suppliers;

The method $this->db->get_compiled_select(); is introduced in codeigniter v3.0 and compiles active records query without actually executing it. But this is not a completely new method. In older versions of CI it is like $this->db->_compile_select(); but the method has been made protected in later versions making it impossible to call back.

CodeIgniter Union All Query:

To run union all query in codeignitar, just use 'UNION ALL' operator instead of 'UNION' in query function.

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

Read:

• How to Write Like Query in CodeIgniter
• How to Write Limit Query in CodeIgniter

So that explains about writing union queries in codeigniter framework. Meet you in another useful post.