1

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)

  • 1
    query->fields('n')->fields('prospekt'); is working fine, the problem is that prospect table alias does not exists. Provide full code. – googletorp Jul 19 '11 at 15:46
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.

0

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');
0

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.

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.