How to select a node reference field when querying the database

Question

I am using db_select to query the database to get some fields, one of the fields is a node reference and I don't know how to select it or join its table.

My content type contains a node reference, and this node reference contains another node reference (nested relationship), how can I from the original content type get the first node reference fields and the fields of its node reference ?

My current query :

$query = db_select("node", "n");
$query->fields("n", array("nid", "created", "type"));
$query->fields("f1", array('field_field1_value'));
$query->fields("f2", array('field_field2_value'));
$query->leftJoin('field_data_field_field1', 'f1', 'f1.entity_id = n.nid');
$query->leftJoin('field_data_field_field2', 'f2', 'f2.entity_id = n.nid');
$query->condition('n.type', 'my_content_type', '=');

$query = $query->orderBy('n.created', 'DESC');
$query = $query->extend('TableSort')->extend('PagerDefault')->limit(20);
$result = $query->execute();

Answer 1

I was able to join my node references using db_select, I just needed to add two relationships for each node reference (I have two node references (nested)), a first relationship to the specific table (which follows the pattern data_field_THE_NAME_OF_THE_NODE_REFERENCE_FIELD_IN_YOUR_CONTENT_TYPE) and a second relationship to the node itself with another alias (I mean by relationships JOINs).

Here's my code now :

$query = db_select("node", "n");
// joins
// setting up the first relationship
$query->leftJoin('field_data_field_X_ref', 'r', 'r.entity_id = n.nid');
$query->join('node', 'x', 'r.field_X_ref_nid = x.nid');
// setting up the second relationship
$query->leftJoin('field_data_field_Y_ref', 'r2', 'r2.entity_id = x.nid');
$query->join('node', 'y', 'r2.field_Y_ref_nid = y.nid');
// joining other fields
$query->leftJoin('field_data_field_field1', 'f1', 'f1.entity_id = n.nid');
$query->leftJoin('field_data_field_field2', 'f2', 'f2.entity_id = n.nid');
// ...
// fields
$query->fields("n", array("nid", "created", "type"));
$query->fields("f1", array('field_field1_value'));
$query->fields("f2", array('field_field2_value'));
// ...
$query->fields("x", array('title')); // title for the first node
$query->fields("y", array('title')); // title for the second node

// adding conditions
$query->condition('n.type', 'voiture_d_occasion', '=');

$result = $query->execute();

note that the two nodes references have a title, and in the query->fields() we mentioned that we want the title of both, drupal will return title for the first field, while the name of the field will be y_title for the second, you can use dsm on the query result to see the name of your fields.