6

I want to get the value of "vid" knowing the node ID, and get the value of "name" for those rows where "vid" is equal to 2.

If it were a plain query, I would not know how to write it; I don't know how to write a left join query.

The "plain" SQL query in Drupal is the following:

$result = db_query('SELECT tid FROM {taxonomy_index} WHERE nid = :nid', array(':nid'=>$nid));

The equivalent raw SQL query is the following:

SELECT did, name FROM taxonomy_index as ti LEFT JOIN taxonomy_term_data AS ttd ON ti.tid = ttd.tid WHERE vid = 2 and nid = $nid
11

You do not need to use db_select() for a static query like this. You shouldn't, actually, see Given that db_select is much slower than db_query, why would I want to use it?.

You just need to pass that to db_query(), the only thing that changed is how you add placeholders.

$result = db_query('select tid,name from {taxonomy_index} as ti left join {taxonomy_term_data} as ttd on ti.tid=ttd.tid where vid=2 and nid=:nid', array(':nid' => $nid));
foreach ($result as $term) {
  // $term contains the object for the taxonomy term.
}

See http://drupal.org/developing/api/database for more information and my blog post for generic Drupal 6 to 7 database syntax conversion information.

  • many thanks. the link is very good. i have read it.but i still don't know what is a dynamic query? – enjoylife May 29 '11 at 13:46
  • 1
    A dynamic query is what Sivaji wrote: using db_select() to build a query with PHP code. useful for example when you have a query with different conditions based on something (configuration, permission, different pages.. ) – Berdir May 29 '11 at 13:54
  • on the above code,could i change vid=2 to vid=:vid then array(':vid' => 2)).should i necessary do this? thank you – enjoylife May 29 '11 at 14:07
  • 1
    It is not strictly necessary, but yes, it might make sense. Especially if you don't want to hardcode that vid but for example use variable_get() to make it overridable. so you would do array(':vid' => variable_get('yourmodule_vocabulary', 2)). Then it would default to 2, but you could override it in settings.php or build a admin settings form for it. – Berdir May 29 '11 at 14:16
  • Yes. You need to do, vid=2 to vid=:vid then array(':vid' => 2) to keep your code compatible across the database servers. Drupal core follows the same pattern. – Sivaji May 29 '11 at 19:47
25
$terms = db_select('taxonomy_index', 'ti')
  ->fields('ti', array('tid', 'name'));
$terms->leftJoin('taxonomy_term_data', 'ttd', 'ti.tid = ttd.tid');
$terms->condition('vid', 2)
  ->condition('nid', $nid)
  ->execute();

while ($term = $terms->fetch()) {
  // $term contains the object for the taxonomy term.
}
  • how to retrieve the result? thank you – enjoylife May 29 '11 at 7:17
  • Does this work? When I try to do something identical to this I get an error that condition() is not a member function. – Johnathan Elmore Sep 20 '12 at 16:03
  • 4
    This won't work. Drupal joins can't be chained. So you'd go: $terms = db_select(...)->fields(...); $terms->leftJoin(...); $terms->condition(...)->... – Neograph734 Mar 18 '13 at 16:33
  • 1
    @Neograph734 I suggested an edit based on your comment. – FLY May 21 '13 at 8:18
0
        $catalog_pagination = db_select('taxonomy_index', 'ti')
          ->fields('pimg',array('uc_product_image_target_id','entity_id'))
          ->fields('fm',array('fid','filename'))    
          ->fields('ti', array('nid', 'tid'));
        $catalog_pagination->leftJoin('node__uc_product_image', 'pimg', 'pimg.entity_id = ti.nid'); 
        $catalog_pagination->leftJoin('file_managed', 'fm', 'fm.fid = pimg.uc_product_image_target_id');  
        $catalog_pagination->condition('ti.tid', $term_id);
        $catalog_pagination->orderBy('ti.nid', 'DESC');
        $catalog_pagination = $catalog_pagination->execute();
        $total_pages = $catalog_pagination->fetchAll();
-1

//Don't forget "fetchAll()" function:

$terms = db_select('taxonomy_index', 'ti')
  ->fields('ti', array('tid', 'name'));
$terms->leftJoin('taxonomy_term_data', 'ttd', 'ti.tid = ttd.tid');
$terms->condition('vid', 2)
  ->condition('nid', $nid)
  ->execute()
  **->fetchAll();** // an array of objects

**$i = 0;
while ($i < count($terms)) {
  $terms[i]->...
  // $terms[i] contains the object for the taxonomy term.
  $i++;
}**

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.