How to use UNION with inconsistent columns via CodeIgniter's query builder

Question

I'm doing a UNION in CodeIgniter and I want the data show like this:

[array] => Array
        (
            [0] => stdClass Object
                (
                //Table 1
                    [id_tecnico] => 1698
                    [date] => 2018-02-12
                    [nro] => M49320
                    [start] => 15:15
                    [end] => 17:45
                    [comment] => ok.
                    [total] => 2.5
                )

            [1] => stdClass Object
                (
                 //Table 2
                [id_tecnico] => 1698
                [nro] => M49317
                [date] => 2018-02-12
                [activity] => meeting
                [comment] => meeeting about nothing 
                [TotalHrs] => 0.67
                )
          )

I have the following query in my model, where I make the query to the table Maintenance, but I also do a query to the table Activities with the same WHERE.

$this->db->select('p.name,s.date,s.nro,d.start,d.end,d.comment,d.total,m.machine');
$this->db->from('Tecnico_Seguimiento as t');
$this->db->join('personal as p','p.Codigo = t.id_tecnico');
$this->db->join('MAN_SeguimientoDetalle as d','d.id_detalle = t.id_detalle');
$this->db->join('MAN_Seguimiento as s','s.idMan_Tecnico = d.id_man_tecnico');
$this->db->join('MAN_Solicitud as m','m.NroSolicitud = s.NroSolicitud');
$this->db->where('t.id_tecnico',$id);
$this->db->where('s.date >=',$minvalue);
$this->db->where('s.date <=',$maxvalue);
$query1 = $this->db->get_compiled_select();

$this->db->select('act.nro as nroAct, act.date as fechaAct,act.activity,act.comment as commnetAct, act.TotalHrs, act.orden, act.date as dateAct, act.id_tecnico ');
$this->db->from('MAN_Actividades as act');
$this->db->where('act.date >=',$minvalue);
$this->db->where('act.date <=',$maxvalue);
$this->db->where('act.id_tecnico <=',$id);
$query2 = $this->db->get_compiled_select();
return $this->db->query($query1." UNION ".$query2)->result();

With this query I get these values:

[array] => Array
    (
        [0] => stdClass Object
            (
                [name] =>  HUGO
                [date] => 2018-02-20
                [nro] => M49301
                [start] => 17:45
                [end] => 19:30
                [commet] => ok.
                [total] => 1.75
                [machine] => Torno
            )

        [1] => stdClass Object
            (
            // Here should be the fields of the table activities
                [name] => M49321
                [date] => 2018-02-12
                [nro] => meeting
                [start] => meeting about nothing
                [end] => 1
                [comment] => 49321
                [total] => 17:43:00
                [machine] => 1698
            )
 )

Instead of [nro] => meeting it should be [activity] => meeting I dunno why just some the first select or I should edit the select and do just one.

just merge two query results into one array

Dhanu Kanakala
– Dhanu Kanakala

2018-07-17 20:15:58 +00:00
Commented Jul 17, 2018 at 20:15 — Dhanu Kanakala
– Dhanu Kanakala, Commented Jul 17, 2018 at 20:15

Mayke da Rosa Herbst · Accepted Answer · 2018-07-17 19:10:12Z

2

You can't do UNION with different fields in each query, the fields in the SELECT clause must be the same on both queries.

answered Jul 17, 2018 at 19:10

Mayke da Rosa Herbst

1312 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mickmackusa · Accepted Answer · 2025-09-23 03:31:59Z

Your desired output is not possible. You will not be able to generate rows of variable size (inconsistent columns).
It is misleading to say that "the fields in the SELECT clause must the same on both queries."

When UNION is used, regardless of the number of UNIONs used, the first query's SELECT clause will dictate the number of columns required of all subsequent SELECT clauses and the first query's column names will be used in the same order for al subsequent query results.

As a simple example, this is an invalid query because the two SELECT clauses do not return the same number of columns:

SELECT one, two
FROM table1
UNION
SELECT three
FROM table 2

Error: The used SELECT statements have a different number of columns

If you have consistent row sizes, the first SELECT rules the column naming based on position in the SELECT clause. In other words, any column names or aliases in SELECT clauses after UNION will be completely ignored. SQLize Demo

SELECT '1' AS one, '2' as two
UNION
SELECT '3' AS two, '4' as one

Result:

|-----|-----|
| one | two |
|-----|-----|
| 1   | 2   |
| 3   | 4   |

This means that there is never any benefit in trying to assign column aliases to UNIONed table data. Just make their values line up positionally with the first query.

For your scenario, edit your code to handwrite NULL column values to ensure that all query results will have the same number of columns.

|------------|------------|--------|-------|-------|----------|------------------------|--------|
| id_tecnico |    date    |  nro   | start |  end  | activity |         comment        |  total |
|------------|------------|--------|-------|-------|----------|------------------------|--------|
|       1698 | 2018-02-12 | M49320 | 15:15 | 17:45 |   NULL   | ok.                    |  2.5   |
|       1698 | 2018-02-12 | M49317 |  NULL |  NULL | meeting  | meeeting about nothing |  0.67  |

Your first query's select() call can look like this:

$this->db->select('p.name id_tecnico,s.date,s.nro,d.start,d.end,NULL,d.comment,d.total', false) // 8 total columns

After the UNION, the second query's select() call can look like this:

$this->db->select('act.id_tecnico,act.date,act.nro,NULL,NULL,act.activity,act.comment,act.TotalHrs', false) // 8 total columns

See this related answer that uses NULL and a hardcoded string value in a UNION query: Select all rows in a table and append a row of AVG() values using CodeIgniter's query builder

Collectives™ on Stack Overflow

How to use UNION with inconsistent columns via CodeIgniter's query builder

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related