Query returns no result

Question

I want to select all the nodes from the database where a specific taxonomy term is defined (in Drupal 7).

I already know the taxonomy term (IDs). So I want to use a join to get the latest node thats created for that taxonomy.

For the moment I have this:

$query = db_select("node","n");
$query->join('taxonomy_index', 'i', 'n.nid = i.nid');
$query->fields("n",array('nid','title','created'))
        ->orderby("created","desc")
        ->range(0,1);

drupal_set_message($id);

$query->where('(i.tid = :type)', array(':type' => $id));

drupal_set_message($query);

$query->execute();


$array = array();
foreach ($query as $record) {
    $array[] = $record;
}
return $array;

This is (some of ) the output of the messages:

Id: 1

SELECT n.nid AS nid, n.title AS title, n.created AS created FROM {node} n INNER JOIN {taxonomy_index} i ON n.nid = i.nid WHERE ((i.tid = :type)) ORDER BY created DESC LIMIT 1 OFFSET 0

If I use the following string directly in PHPMYADMIN, I have results:

SELECT n.nid AS nid, n.title AS title, n.created AS created FROM drpl_node n INNER JOIN drpl_taxonomy_index i ON n.nid = i.nid WHERE ((i.tid = 1)) ORDER BY created DESC LIMIT 1 OFFSET 0

The result directly in Drupal returns an empty array.. What am I doing wrong?

Answer 1

Usually it's better if you use EntityFieldQuery instead of db_select. See EntityFieldQuery vs Db_select()

From EntityFieldQuery use page:

The EntityFieldQuery API lets you fetch information about entities (nodes, users, taxonomy terms et cetera) from Drupal without actually building SQL queries. EntityFieldQuery is a class that allows retrieval of a set of entities based on specified conditions. It allows finding of entities based on entity properties, field values, and other generic entity metadata. The syntax is really compact and easy to follow, as well. And, best of all, it's core Drupal; no additional modules are necessary to use it.

In your case:

$nodeQuery = new EntityFieldQuery();
$query = $nodeQuery->entityCondition('entity_type', 'node')
  // get only nodes that are 'published'
  ->propertyCondition('status', 1) 
  // replace field_food_menu with field_TAXONOMY_NAME 
  // replace 2 with the taxonomy ID (tid) you're wanting
  ->fieldCondition('YOUR_TAXONOMY_FIELD_NAME', 'tid', $SEARCHED_TID)
  // SOrt by creation date.
  ->propertyOrderBy('changed', 'DESC')
  // Retrieve only last item.
  ->range(0,1)
  // USe user 1 to query all nodes, not only those that current use can access.
  ->addMetaData('account', user_load(1));


// Get a list of nids that match given criteria (because if the use of range() a list of 1 elem, at most.
$result = $query->execute();

(Code it's not tested, probably you have to tweak it a little).

I should add that this method also checks if user is allowd to access to the nodes that returns. That's the reason for the last line, it makes all nodes available, not just those the current user can access. If you want to see only nodes for that user just remove that line.