pull Drupal field values with db_query() or db_select()

I've created a content type in Drupal 7 with 5 or 6 fields. Now I want to use a function to query them in a hook_view call back. I thought I would query the node table but all I get back are the nid and title. How do I get back the values for my created fields using the database abstraction API?

Answers


Drupal stores the fields in other tables and can automatically join them in. The storage varies depending on how the field is configured so the easiest way to access them is by using an EntityFieldQuery. It'll handle the complexity of joining all your fields in. There's some good examples of how to use it here: http://drupal.org/node/1343708

But if you're working in hook_view, you should already be able access the values, they're loaded into the $node object that's passed in as a parameter. Try running:

debug($node);

In your hook and you should see all the properties.


If you already known the ID of the nodes (nid) you want to load, you should use the node_load_multiple() to load them. This will load the complete need with all fields value. To search the node id, EntityFieldQuery is the recommended way but it has some limitations. You can also use the database API to query the node table for the nid (and revision ID, vid) of your nodes, then load them using node_load_multiple().

Loading a complete load can have performance impacts since it will load way more data than what you need. If this prove to be an issue, you can either try do directly access to field storage tables (if your fields values are stored in your SQL database). The schema of these tables is buld dynamicaly depedning on the fields types, cardinality and other settings. You will have to dig into your database schema to figure it out. And it will probably change as soon as you change something on your fields.

Another solution, is to build stub node entities and to use field_attach_load() with a $options['field_id'] value to only load the value of a specific field. But this require a good knowledge and understanding of the Field API.


See How to use EntityFieldQuery article in Drupal Community Documentation.

Creating A Query

Here is a basic query looking for all articles with a photo that are tagged as a particular faculty member and published this year. In the last 5 lines of the code below, the $result variable is populated with an associative array with the first key being the entity type and the second key being the entity id (e.g., $result['node'][12322] = partial node data). Note the $result won't have the 'node' key when it's empty, thus the check using isset, this is explained here.

Example:

<?php
$query = new EntityFieldQuery();

$query->entityCondition('entity_type', 'node')
  ->entityCondition('bundle', 'article')
  ->propertyCondition('status', 1)
  ->fieldCondition('field_news_types', 'value', 'spotlight', '=')
  ->fieldCondition('field_photo', 'fid', 'NULL', '!=')
  ->fieldCondition('field_faculty_tag', 'tid', $value)
  ->fieldCondition('field_news_publishdate', 'value', $year. '%', 'like')
  ->fieldOrderBy('field_photo', 'fid', 'DESC')
  ->range(0, 10)
  ->addMetaData('account', user_load(1)); // Run the query as user 1.

$result = $query->execute();

if (isset($result['node'])) {
  $news_items_nids = array_keys($result['node']);
  $news_items = entity_load('node', $news_items_nids);
}
?>

Other resources


Need Your Help

Node.js Set socket ID

javascript node.js socket.io real-time

Starting from the official chat example of Node.js.

How can i change the height of UIPickerView so that i shows more rows (not scale)

ios uipickerview

One of my view controllers has 3 elements, a textfield, a custom picker and a button. I have a lot of screen space and I want to change the height of the picker so that it shows more rows.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.