0

I am trying to optimise the code in a Drupal 7 website by replacing node_load functions to retrieve only the title with custom SQL queries.

So far, I have been successful in retrieving the same values. However I just cant seem to get the gist of getting the title from the latest revision of a node or retrieve any other value from the associated fields.

Can anyone point me out regarding the concept of revisions from a table point of view?

2

I believe this is what you're looking for:

function MYMODULE_get_node_title($nid) {
  // Using Drupal's DB API.
  $query = db_select('node_revision', 'r')
    ->fields('r', array('title'))
    ->addTag('node_access');
  $query->leftJoin('node', 'n', 'n.vid = r.vid');
  $query->condition('n.nid', $nid);
  $title = $query
    ->execute()
    ->fetchField();

  // Using a custom query. This might help you understand better what's the
  // exact relationship. This approach is a bit faster as well.
  $title = db_query("SELECT r.title
                     FROM
                      {node} n,
                      {node_revision} r
                     WHERE
                      n.nid = :nid AND
                      n.vid = r.vid", array(':nid' => $nid))->fetchField();

  return $title;
}

To understand the relationship look at the second approach, using a custom raw query.
vid stands for version ID and if you look at the node table you will see that there is a column named vid.
This is the active version of that node and is the ID you should use to get all field values and other attributes of that piece of content.

Hope this helps.

  • You almost always tag {node} queries with node_access. – mpdonadio Dec 29 '13 at 20:23
  • @MPD added to make it a full answer, even though I think in this particular case it might not be necessary. – Aram Boyajyan Dec 29 '13 at 20:58
  • That was very descriptive but may I ask why we need the tag for node-access? – Binny Dec 30 '13 at 3:37
  • node_access allows other modules to perform access checks for current user, alter the query and limit the nodes to only those ones to which current user has access to. Note that it won't work with raw queries executed with db_query. Read more here – Aram Boyajyan Dec 30 '13 at 9:23

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.