0

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();
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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.