15

I am using db_select() and I don't understand the syntax of the fields() method. I'm using a join() to join another table. So for tables t and n, I want to do something like

SELECT t.tid, t.field1, t.field2, n.title, n.author 
FROM table t INNER JOIN node n ON t.nid = n.nid;

I have

$results = db_select('table', 't')
  ->fields( ... )
  ->join('node', 'n', 'n.nid = t.nid')
  ->execute();

But as you can see, I'm lost at fields(). The examples I've seen only specify fields for one table:

->fields('t', array('tid', 'field1', 'field2'))

What is the syntax that I want to use?

28

Easy, just call fields() twice.

$query = db_select('table', 't');
$query->join('node', 'n', 'n.nid = t.nid');
$result = $query
  ->fields('t', array('tid', 'field1', 'field2'))
  ->fields('n', array('nid', 'field1', 'field2'))
  ->execute();

foreach ($result as $row) {
  // Do something with $row.
}

You can call most methods multiple times (multiple fields, multiple conditions, multiple sort orders, multiple joins, ...).

Note that, as shown in my example, the call to join() needs to be separate and can not be "chained" (that's the technical term for having multiple method calls in a row) because it does not return the query object but the name for the table alias.

2

An implicit join will still work with db_query(), if you're not doing anything fancy. I'm not sure if this is bad practice or not though. I asked once in IRC and didn't get a response from anyone. So if you've got any of those left over from D6, they'll still work.

  • 3
    Using db_query() is perfectly fine (It's actually recommended for performance reasons) unless there is a explicit reason for using db_select(). I've outlined those reasons here: drupal.stackexchange.com/questions/1200/… – Berdir Nov 16 '11 at 23:20
  • Great! I was too lazy to convert mine anyway. ;) – colan Nov 29 '11 at 2:34

protected by kiamlaluno Jul 3 '15 at 7:56

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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