Build SELECT query containing UNION and a subquery using Codeigniter's ActiveRecord syntax

Question

I have query that counts ids in joined table. I need to do an union of returned table with another table in database.

The table returned from first query has 4 columns:

• group_id
• count
• name
• description

The table I want to unite with the first one has 3 columns:

• group_id
• name
• description

I run query through phpmyadmin and it worked perfectly. Query:

SELECT group_id, size, name, description
FROM(SELECT *, count(group_id) as size
     FROM table1 
     GROUP BY group_id) as tmp
JOIN table2
ON tmp.group_id = table2.group_id
UNION
SELECT id, 0 as size, name, description
FROM table2

But when I try to make query with codeigniter, it won't work.

Error: Unknown column '0' in 'field list'

SELECT id, 0 as size, name, description 
FROM table2

Here is the Codeigniter code from module:

$this->db->select('group_id, size, name, description');
$this->db->from('(select *, count(group_id) as size from table1 group by group_id) as tmp');
$this->db->join('table2', 'tmp.group_id=table2.id');

$this->db->get();

$query1 = $this->db->last_query(); 

$this->db->select('id, 0 as size, name, description');
$this->db->from('table2');

$this->db->get();
$query2 = $this->db->last_query(); 

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

To make the story short I need to know how to make placeholder with Codeigniter or is there any other, better way to get the result I need?

Answer 1

You have to escape your select query by adding FALSE value as the second parameter of codeigniter's active record select() function.
So, it should be written like:

$this->db->select('id, 0 as size, name, description',FALSE);

When you add FALSE value to the second parameter of it, codeigniter won't keep the first parameter to be initialized by backtick character to the query.

Answer 2

get_compiled_select() does not actually execute the SQL and is suitable for creating subqueries and union queries. Just work from the deepest to shallowest query.

All of the SELECT queries will enjoy the protection and formatting that Codeigniter's ActiveRecord query building methods afford.

public function modelMethodWithSubqueryAndUnion(): array
{
    $subquery = $this->db
        ->select("group_id, COUNT(1) AS size")
        ->from("table1")
        ->groupBy("group_id")
        ->get_compiled_select();

    $query1 = $this->db
        ->select("group_id, size, name, description")
        ->from("($subquery) AS tmp")
        ->join("table2", "tmp.group_id = table2.group_id")
        ->get_compiled_select();

    $query2 = $this->db
        ->select("id, 0 AS size, name, description")
        ->from("table2")
        ->get_compiled_select();

    return $this->db->query("$query1 UNION $query2")->result_array();
}

_{*I have not tested the above code nor did I consider whether the asker's raw SQL is optimized/sensible. This answer should be merely considered as a demonstration of how to use get_compiled_select() to build prepared query segments. To check the rendered query that was last called, use $this->db->last_query().}