How to return a missing field as null with db_select union?

Question

I have a db_select union of two tables where some of the columns are different. I would like to select a column that is only in one of the tables. The standard SQL way of doing this seems to set the column as null for the table where the field missing. Is there a way of doing this with db_select? db_query?

Standard SQL way:

select id, name, date, null as userid, 'A' as recordtype from table1
union all
select id, name, null /*as date*/, userid, 'B' as recordtype from table2

What I'm trying to do:

$table_one = db_select('t_one', 'to')->fields('to', array('ID', 'NAME'));
$table_two = db_select('t_two', 'tw')->fields('tw', array('ID', 'NAME', 'new_field_not_in_first_table'));
$s_query = Database::getConnection()
        ->select($table_one->union($table_two), 't')
        ->fields('t', array('ID', 'NAME', 'new_field_not_in_first_table'));

I tried adding 'NULL as new_field_not_in_first_table' in the first table but no luck. Thanks for your time!

Answer 1

I used the addField function to explicitely declare the NULL aliased columns for the UNION. It worked! Check the following snippet:

$table_one = db_select('users', 't_o');
$table_one->addField('t_o', 'uid', 'id');
$table_one->addField('t_o', 'name');
$table_one->addField(NULL, 'null', 'weight');
$table_one->addField('t_o','mail');

$table_two = db_select('taxonomy_term_data"', 'tw');
$table_two->addfield('tw', 'tid', 'id');
$table_two->addfield('tw', 'name');
$table_two->addfield('tw', 'weight');
$table_two->addField(NULL, 'null', 'mail');

$s_query = db_select($table_one->union($table_two), 't')
    ->fields('t', array('id', 'name', 'weight', 'mail'));

And this generates this kind of SQL:

SELECT t.id AS id, t.name AS name, t.weight AS weight, t.mail AS mail
FROM
  (SELECT t_o.uid AS id, t_o.name AS name, null AS weight, t_o.mail AS mail
   FROM {users} t_o 
   UNION 
   SELECT tw.tid AS id, tw.name AS name, tw.weight AS weight, null AS mail
   FROM {taxonomy_term_data} tw)

Hope this solves your problem!