Combine several select queries into one with union

Background

I wanted to create a view which lists all nodes of different content types that link to the current node displayed on the page. The nodes link via node reference. Since node references only work in one way, and I wanted to get the node references of different content types linking to the current node, I couldn't (didn't know) how to achieve this easily with just a plain view.

The solution I came about was to embed a view to display the different nodes that link to the current one, and feed that view a list of node ids of the linked nodes.

Four different content types with four different node reference fields linked to our node. To get the nids we need to query four different tables.

To get the nids from the four tables in one trip to the database, we can use the union syntax.

Drupal DatabaseAPI union syntax

• Create the different select queries.
• Union the queries to each other like below
  

  $subquery_1->union($subquery_2)->union($subquery_3)->union($subquery_4);
  

• Execute the first query.

Code example

Below you find the actual queries to query four different node reference tables.

      /**
       * field_data_field_col_ref_place
       * field_data_field_entity_ref_place
       * field_data_field_occ_ref_place
       * field_data_field_place_ref_place
       */
      $subquery_1 = db_select('field_data_field_col_ref_place', 'cp')
        ->condition('cp.field_col_ref_place_target_id', $nid, '=')
        ->fields('cp', array('entity_id'));
       
      $subquery_2 = db_select('field_data_field_entity_ref_place', 'ep')
        ->condition('ep.field_entity_ref_place_target_id', $nid, '=')
        ->fields('ep', array('entity_id'));
       
      $subquery_3 = db_select('field_data_field_occ_ref_place', 'op')
        ->condition('op.field_occ_ref_place_target_id', $nid, '=')
        ->fields('op', array('entity_id'));
       
      $subquery_4 = db_select('field_data_field_place_ref_place', 'pp')
        ->condition('pp.entity_id', $nid, '=')
        ->fields('pp', array('field_place_ref_place_target_id'));
       
      $subquery_1->union($subquery_2)->union($subquery_3)->union($subquery_4);
     
      $nids = $subquery_1->execute()->fetchCol();

Remarks

The items you retrieve need to be of the same type (for example integers) or a union won't be possible.