Database API join fields

Question

i've got a database query with a join like this:

$query = db_select('node', 'n');
$query->condition('n.nid', 0, '<>')
  ->fields('n')
  ->range(0, 50);

$query->leftJoin('field_data_field_prospekt_owner','prospekt','prospekt.revision_id = n.nid');

Let's say I want to get the fields from my 'field_data_field_prospekt_owner' table in the result. How do I go about that?

i tried extending the fields function like query->fields('n,prospekt') but this throws an error.

query->fields('n')->fields('prospekt') isn't working, unfortunately. I'm getting a error:

PDOException: SQLSTATE[42S02]: Base table or view not found: 1051 Unknown table 'prospekt': SELECT n., prospekt. FROM {node} n {} LEFT OUTER JOIN {field_data_field_prospekt_owner} prospekt_2 ON prospekt.revision_id = n.nid WHERE (n.nid <> :db_condition_placeholder_0) LIMIT 50 OFFSET 0; Array ( [:db_condition_placeholder_0] => 0 ) in main() (Zeile 42 von /var/www/asvg/prospekt_iframe.php)

Answer 1

You should do

$query->fields('n')->fields('prospekt');

See the documentation for more info.

Note that querying field tables this way is not recommended as the tables are created dynamically. You should only fetch field data via APIs.

Answer 2

If i provide the fields to select for the joined table, it works... But it doesn't if i want to select all the fields by omitting the second parameter to SelectQuery::fields(). The work around is to call the latter after the join so that the whole query would look like:

$query = db_select('node', 'n')
  ->condition('n.nid', 0, '<>')
  ->fields('n')
  ->range(0, 50);
$query->leftJoin('field_data_field_prospekt_owner','prospekt','prospekt.revision_id = n.nid');
$query->fields('prospekt');

If i provide the fields for prospekt in the query builder, it works also:

$query = db_select('node', 'n')
  ->condition('n.nid', 0, '<>')
  ->fields('n')
  ->fields('prospekt', array('col1', 'col2'))
  ->range(0, 50);
$query->leftJoin('field_data_field_prospekt_owner','prospekt','prospekt.revision_id = n.nid');

Answer 3

I was looking for another problem when I dropped on your question.

You can add your field using addField such as:

$query->addField('prospekt','prospekt_owner_value','owner');

You can use the field alias 'owner" to access the returned value.