1

I have a quite simple query : (find all nodes, fetch 3 fields from the node table and join the results with some tax.terms)

$query = db_select ( 'node', 'n' );
  $query->fields ( 'n', array( 
      'nid',
      'title',
      'type' ));
  $query->fields('b',array('body_value'));
  $query->fields('ttd',array('name'));

  $query->leftJoin ( 'field_data_body', 'b', 'b.entity_id = n.nid');
  $query->leftjoin ( 'taxonomy_index', 'ti', 'ti.nid = n.nid');
  $query->leftjoin ( 'taxonomy_term_data', 'ttd', 'ttd.tid = ti.tid AND ttd.vid = "5"');

  /* $query->condition('ttd',5); */

  $result = $query->execute ()->fetchAll ();

Now the problem is that i want a single object returned form the query for each node. Like this :

stdClass Object
(
    [nid] => 557
    [title] => Lorem ipsum
    [type] => content_type
    [body_value] => lorem ispum
    [name] => taxonomy_term_value
)

But what i get is multiple objects because of the taxonomy index table (where a node can have anywhere from 12 term id's to none at all) but i only need 1 that corresponds to the condition stated above in the query (vid = 5, from the tax.term data table) if that node has a link to the tax.term, if the node does not have link it should return nothing,null,empty string...

So my results are as follows :

stdClass Object
(
    [nid] => 155
    [title] => lorem ipsum
    [type] => content_type
    [body_value] => lorem ispum
    [name] => 
)
stdClass Object
(
    [nid] => 3411
    [title] => lorem ipsum
    [type] => content_type
    [body_value] => lorem ispum
    [name] => value1
)
stdClass Object
(
    [nid] => 161
    [title] => lorem ipsum
    [type] => content_type
    [body_value] => lorem ispum
    [name] => value4
)
stdClass Object
(
    [nid] => 561
    [title] => lorem ipsum
    [type] => content_type
    [body_value] => lorem ispum
    [name] => value16
)
0

What you get currently is what you asked for: leftJoin.

To get only the single object you're looking for, you must use join instead (which is default and equivalent to innerJoin).

BTW you may simplify your code using the short notation, like this:

$query = db_select('node', 'n')
  ->fields('n', array('nid', 'title', 'type' ))
  ->fields('b', array('body_value'))
  ->fields('ttd',array('name'))
  ->join('field_data_body', 'b', 'b.entity_id = n.nid')
  ->join('taxonomy_index', 'ti', 'ti.nid = n.nid')
  ->join('taxonomy_term_data', 'ttd', 'ttd.tid = ti.tid AND ttd.vid = "5"');
  • But won't this return only results that have a value in ttd.name ? Because each node has a taxonomy term which is not required so it can be null. – lordZ3d Jun 23 '15 at 9:18
  • Not sure to correctly understand your last comment. Anyway what is certain is: the innerJoin will not return a record where there is no link between a node and the searched ttd, but will always return it otherwise, whatever the ttd.name value is (in other words when it is present, even if empty). – cFreed Jun 23 '15 at 11:56
  • My question was exactly what you described what will happen, which is not a solution sadly to my problem, since there has to be a result even if the node does not have link to the matched ttd.name. In short, there has to be a result for every node even if a node does not have a link between it and the tax.term (vid = 5) ,it should return an empty string. [name] => – lordZ3d Jun 23 '15 at 14:38
  • Now I don't understand at all. Reading your original question I believed that, with leftJoin: 1. you got too much resulting records (i.e. including those ones without a link to the (as condition says) ttid; 2. you wanted to get only those WITH a link ("i only need 1 that corresponds to the condition stated"). Can you edit your question adding a example with some detailed real records involved? – cFreed Jun 23 '15 at 15:02
  • I'm sorry , i can have trouble sometimes explaining my thoughts correctly. I've edited the original answer. – lordZ3d Jun 24 '15 at 12:41

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.