phpsqlcodeigniteractiverecordunion

Convert SELECT query with UNION and WHERE clauses to CodeIgniter's active record

This is the my original SQL query

SELECT 'car' AS type,id,title,status FROM car_product
UNION
SELECT 'van' AS type,id,title,status FROM van_product

Now I want to run this query using CodeIgniter's active record and include WHERE clauses, but only knew how to make two queries and merge the results like this:

$this->db->select('`car` As type,id,title,status');
$this->db->from('car_product');
$this->db->where('status =', $status);
$query_car = $this->db->get()->result();
     
$this->db->select('`van` AS type,id,title,status');
$this->db->from('van_product');
$this->db->where('status =', $status);
$query_van = $this->db->get()->result();

return array_merge($query_car, $query_van);

How can I create a UNION query with CodeIgniter's query builder methods?

Solution

  • You can use ->query() method directly without using queryBuilder for this.

    $query = $this->db->query("select 'car' as type,id,title,status from car_product union    select 'van' as type,id,title,status from van_product");

    Then you can deal it as you deal with your other queries

    e.g: 

    return $query->result();