Join table query in hook_views_query_alter not selecting field from joined table

Question

I have the following query

SELECT
    node.nid AS nid,
    node.created AS node_created,
    'node' AS field_data_field_gm_tanknummer_node_entity_type,
    'node' AS field_data_field_gm_naam_node_entity_type,
    'node' AS field_data_field_gm_adres_node_entity_type,
    'node' AS field_data_field_gm_postcode_node_entity_type,
    'node' AS field_data_field_gm_jaar_node_entity_type,
    'node' AS field_data_field_gm_bedrijf_levensduur_node_entity_type,
    'node' AS field_data_field_gm_bedrijf_antibiotica_node_entity_type,
    'gemiddelde_waarden:page' AS view_name
FROM
    {node} node
left JOIN
    {field_data_field_gm_tanknummer} field_data_field_gm_tanknummer
ON
    node.nid = field_data_field_gm_tanknummer.entity_id
WHERE
    (( (node.status = :db_condition_placeholder_0)
AND
    (node.type IN (:db_condition_placeholder_1))
AND
    (field_data_field_gm_tanknummer.field_data_field_gm_tanknummer_value IN (:db_condition_placeholder_2)) ))
ORDER BY
    node_created DESC LIMIT 10 OFFSET 0

where I join the table field_data_field_gm_tanknummer.

I want to select the field field_data_field_gm_tanknummer_value from that table but the query cannot find this field.

This is my function

function misc_views_query_alter(&$view, &$query) {
  if ($view->name == 'gemiddelde_waarden') {

    //create the join
    $join = new views_join();
    $join->table = 'field_data_field_gm_tanknummer';
    $join->field = 'entity_id';
    $join->left_table = 'node';
    $join->left_field = 'nid';
    $join->type = 'left';
    //add the join the the view query
    $view->query->add_relationship('field_data_field_gm_tanknummer',$join,'node');

    $query->where[1]['conditions'][2]['field'] = 'field_data_field_gm_tanknummer.field_data_field_gm_tanknummer_value';
    $query->where[1]['conditions'][2]['value'] = array(
      0 => _misc_tanknummers()
    );
    $query->where[1]['conditions'][2]['operator'] = 'IN';
  }
}

Is there something wrong with the join?

Answer 1

There a typo in your SQL query: instead of

field_data_field_gm_tanknummer.field_data_field_gm_tanknummer_value

you need to specfiy:

field_data_field_gm_tanknummer.field_gm_tanknummer_value