7

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
Improve this question

4 Answers 4

Reset to default
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.

Improve this answer
5
  • many thanks. the link is very good. i have read it.but i still don't know what is a dynamic query?
    – enjoylife
    Commented May 29, 2011 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
    Commented May 29, 2011 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
    Commented May 29, 2011 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
    Commented May 29, 2011 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
    Commented May 29, 2011 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.
}
Improve this answer
4
  • how to retrieve the result? thank you
    – enjoylife
    Commented May 29, 2011 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
    Commented Sep 20, 2012 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
    Commented Mar 18, 2013 at 16:33
  • 1
    @Neograph734 I suggested an edit based on your comment.
    – FLY
    Commented May 21, 2013 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();
Improve this answer
-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++;
}**
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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