Greetings!

I need a push to get me going on learning db_select function.
I am trying to get the results of the node table, joined with field_mfield_1 and field_mfield_2 where uid = 1.

Problem is that I am stuck figuring out how to do joins in the new D7 structure.
I have this, modified from a block tutorial:

<?php
  $query = db_select('node', 'n')
    ->fields('n', array('nid', 'title', 'created'))
    ->execute();
?>

How do I do a join on this with field revisions set to current?

Thank you!

Comments

angelg33k’s picture

angelg33k commented

You can do it i several steps, to get the right order in the query. Here is an example that selects the title from the node table and the body text from the field_data_body table.

// start with the base table
$query = db_select('node', 'n');

// add the join statement
$query->innerJoin('field_data_body', 'fdb', 'n.nid = fdb.entity_id');

// and fetch your data
$result = $query
    ->fields('n', array('nid', 'title', 'created'))
    ->fields('fdb', array('body_value'))
    ->condition('type', 'page')
    ->condition('status', 1)
    ->orderBy('created', 'DESC')
    ->execute();

The result set can then be treated like an array of objects, for example:

foreach($result as $node){
    print $node->title;
    print $node->body_value;
}
damien tournoud’s picture

damien tournoud commented

If you want to query for entities and field, use the EntityFieldQuery. DatabaseSelect is not the proper interface for doing this type of queries.

See http://api.drupal.org/api/drupal/includes--entity.inc/class/EntityFieldQ...

farald’s picture

farald commented

Thank you for replies!

I grinded a lot on this problem, and therefore learned a lot, but ultimately I went for another solution.

There are a lot of fields so..

<?php
$node = entity_load('node',FALSE, array('uid' => 3));
?>

..will do, and then use cache to store analyzed data for a period of time.

Thanks again!

farald’s picture

farald commented
Status: Active » Closed (fixed)

Closing up.