UNION query with codeigniter's active record pattern

codeigniter datatable union
codeigniter query builder
mysql union
join query in codeigniter
select query in codeigniter
codeigniter insert query
update query in codeigniter
delete query in codeigniter

How to do UNION query with PHP CodeIgniter framework's active record query format?

CodeIgniter's ActiveRecord doesn't support UNION, so you would just write your query and use the ActiveRecord's query method.

$this->db->query('SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2');

UNION query with codeigniter's active record pattern, With a properly designed database, there's almost never a good reason to use UNION. There are no performance benefits to using it vs separate  I am using codeigniter 2.1 version.In this i have try to write data tables query with union all.I need both query values.But it not working,my query is given below,

This is a quick and dirty method I once used

// Query #1

$this->db->select('title, content, date');
$this->db->from('mytable1');
$query1 = $this->db->get()->result();

// Query #2

$this->db->select('title, content, date');
$this->db->from('mytable2');
$query2 = $this->db->get()->result();

// Merge both query results

$query = array_merge($query1, $query2);

Not my finest work, but it solved my problem.

note: I didn't need to order the result.

Add union() to DB Query Builder, table, thats why I need a union. my query is like below---. Code: SELECT '​Income' as source, fld_type, fld_amount, ta.fld_account as account,  Using UNION is a Dark Path and should be avoided as much as possible. Codeigniter's strength is being lean. The introduction of db->union() would bloat CI and then promote bad practice. BUT - I know the world is a messy place and occasionally we need to engineer nasty solutions, so perhaps: Do what skunkbad has said and run 2 queries or

By doing union using last_query(), it may hamper performance of application. Because for single union it would require to execute 3 queries. i.e for "n" union "n+1" queries. It won't much affect for 1-2 query union. But it will give problem if union of many queries or tables having large data.

This link will help you a lot: active record subqueries

We can combine active record with manual queries. Example:

// #1 SubQueries no.1 -------------------------------------------

$this->db->select('title, content, date');
$this->db->from('mytable');
$query = $this->db->get();
$subQuery1 = $this->db->_compile_select();

$this->db->_reset_select();

// #2 SubQueries no.2 -------------------------------------------

$this->db->select('title, content, date');
$this->db->from('mytable2');
$query = $this->db->get();
$subQuery2 = $this->db->_compile_select();

$this->db->_reset_select();

// #3 Union with Simple Manual Queries --------------------------

$this->db->query("select * from ($subQuery1 UNION $subQuery2) as unionTable");

// #3 (alternative) Union with another Active Record ------------

$this->db->from("($subQuery1 UNION $subQuery2)");
$this->db->get();

Is there any way to write union with query builder?, I used union in my one of project. Union requied columun must be same in both queries.hope it helps you. function get_invoice_data($start  Preparing the Query ¶. This can be easily done with the prepare() method. This takes a single parameter, which is a Closure that returns a query object. Query objects are automatically generated by any of the “final” type queries, including insert, update, delete, replace, and get.

You may use the following method to get the SQL statement in the model:

$this->db->select('DISTINCT(user_id)');
$this->db->from('users_master');
$this->db->where('role_id', '1');

$subquery = $this->db->_compile_select();
$this->db->_reset_select();

This way the SQL statement will be in the $subquery variable, without actually executing it.

You have asked this question a long time ago, so maybe you have already got the answer. if not, this process may do the trick.

php, The UNION command is used to combine more than one SELECT query results into a single query contain rows from all the select queries. The  The query() function returns a database result object when “read” type queries are run, which you can use to show your results. When “write” type queries are run it simply returns TRUE or FALSE depending on success or failure. When retrieving data you will typically assign the query to your own variable, like this:

by modifying somnath huluks answer, i add these following variable and functions to DB_Active_rec class as follows:

class DB_Active_records extends CI_DB_Driver
{

   ....

   var $unions;

   ....

    public function union_push($table = '')
    {
        if ($table != '')
        {
            $this->_track_aliases($table);
            $this->from($table);
        }

        $sql = $this->_compile_select();

        array_push($this->unions, $sql);
        $this->_reset_select();
    }

    public function union_flush()
    {
        $this->unions = array();
    }

    public function union()
    {
        $sql = '('.implode(') union (', $this->unions).')';
        $result = $this->query($sql);
        $this->union_flush();
        return $result;
    }

    public function union_all()
    {
        $sql = '('.implode(') union all (', $this->unions).')';
        $result = $this->query($sql);
        $this->union_flush();
        return $result;
    }
}

therefore you can virtually use unions without dependencies to db_driver.

to use union with this method, you simply make regular active record queries, but calling union_push instead of get.

note: you have to ensure your queries have matching columns like regular unions

example:

    $this->db->select('l.tpid, l.lesson, l.lesson_type, l.content, l.file');
    $this->db->where(array('l.requirement' => 0));
    $this->db->union_push('lessons l');
    $this->db->select('l.tpid, l.lesson, l.lesson_type, l.content, l.file');
    $this->db->from('lessons l');
    $this->db->join('scores s', 'l.requirement = s.lid');
    $this->db->union_push();
    $query = $this->db->union_all();
    return $query->result_array();

would produce:

(SELECT `l`.`tpid`, `l`.`lesson`, `l`.`lesson_type`, `l`.`content`, `l`.`file`
FROM `lessons` l
WHERE `l`.`requirement`=0)
union all 
(SELECT `l`.`tpid`, `l`.`lesson`, `l`.`lesson_type`, `l`.`content`, `l`.`file`
FROM `lessons` l
JOIN `scores` s ON `l`.`requirement`=`s`.`lid`)

MySQL UNION - Complete Tutorial, How to do UNION query with PHP CodeIgniter framework's active record query format? Answers: CodeIgniter's ActiveRecord doesn't support  Query Builder Class¶ CodeIgniter gives you access to a Query Builder class. This pattern allows information to be retrieved, inserted, and updated in your database with minimal scripting. In some cases only one or two lines of code are necessary to perform a database action. CodeIgniter does not require that each database table be its own class file.

UNION query with codeigniter's active record pattern, So, if you are going to need executing a query that requires 'UNION' , you may be thinking what to do, avoid using active record class? The UNION ALL command combines the result set of two or more SELECT statements (allows duplicate values). The following SQL statement returns the cities (duplicate values also) from both the "Customers" and the "Suppliers" table: SELECT City FROM Customers. SELECT City FROM Suppliers. Try it Yourself » SQL Keywords Reference.

CodeIgniter Active Record Class Tutorial With PHP Code Examples, We can combine more than one SELECT query by using UNION command to get single result set. All SELECT queries ( return ) number of column and data type  Create a union query by creating and combining select queries. Even though you can create a union query by directly writing the SQL syntax in the SQL view, you might find it easier to build it in parts with select queries. You can then copy and paste the SQL parts into a combined union query.

SQL Union query of tables to join two results with ALL options, CodeIgniter Union Query Example. PHP Codeigniter active records doesn't support union query but still there are better workarounds to write union query, union  The UNION command is used to combine more than one SELECT query results into a single query contain rows from all the select queries. The number of columns and data types in the SELECT statements must be the same in order for the UNION command to work. The DISTINCT clause is used to eliminate duplicate values from the UNION query result set.

Comments
  • To explain, CodeIgniter's ActiveRecord only supports SQL features that are compatible with all its supported SQL types (or implements them in its own way). The idea of ActiveRecord is to abstract the database type to be database independant and let people move from MySQL to MSSQL or whatever else without major issue. If they tried to add unison it would screw with other database types.
  • database independant and let people move from MySQL to MSSQL or whatever else without major issue
  • Name one popular RDBMS that does not support UNION? Other ORMs (± ActiveRecord semantics), such as SQLAlchemy, offer excellent support for UNIONs and JOINs (of various kinds) across all database back-ends, including SQLite. For back-ends which do not support it directly (e.g. SQLite), the ORM makes it work by doing a little bit more behind-the-scenes while still maintaining portability. In this particular case, by performing the query manually you lose all semblance of portability in addition to the more advanced features of the ActiveRecord system itself (e.g. table filtering).
  • This is not pure active record query. I was having same requirement. I got solution & given answer.
  • how can i add order by in this query?
  • Bad idea if your data is changing via some asynchronous process, data may get added/deleted/changed in between the two queries.
  • what is the use of => $query = $this->db->get(); ? $query doesn't seems to use anywhere in the code.
  • Fantastic! Good idea. This is a real union. The other one runs 2 queries on the database. I sometimes wonder if developers care at all about database performance.
  • _compile_select() is a protected method in the latest version of CodeIgniter (the underscore indicates that it was intended as an internal method in the first place). See my answer for a workaround: stackoverflow.com/a/14270008/560114
  • I am in a dilemma here and need clarification. Does adding the get_compiled_select method in DB_active_rec.php will make the library work properly ? And do we have to use the added method for combining the results ? I see no mention about this method on the GitHub repository of the library you mentioned for performing UNION. And will it perform UNION ALL ?
  • @RocketHazmat: See the above comment and clarify my doubt. I am not able to run my UNION ALL query according to your example shown.
  • @SilentAssassin: Adding get_compiled_select will make the library work. You don't need to call it yourself, it's called internally by the library. It does do UNION ALL. To use UNIONs you do this: $sub1 = $this->subquery->start_union(); $sub1->select('a')->from('b')->where('c', 'd'); $sub2 = $this->subquery->start_union(); $sub2->select('a2')->from('b2')->where('c2', 'd2'); $this->subquery->end_union(); $query = $this->db->get();.
  • @RocketHazmat See this link. This is what I am trying to do. I know the ORDER BY is creating issues. How to write it before start_union ? The $sub1 won't be initialized before that so I've to write it using db object ?
  • @SilentAssassin: The individual SELECTs in a UNION cannot have an ORDER BY. You can only have one ORDER BY for the complete result set. What I meant by "before start_union" was to write $this->db->order_by() before sub1 = $this->subquery->start_union();, because there may be syntax errors if you write it after. I'm working on that.