By kevinnivek on

Hello,

I'm attempting to select columns across two tables in Drupal. My code is currently the following :

$query = db_select('field_data_field_table1', 'field_data_field_table2');
$query 
->fields('field_data_table1', array('entity_id'))
->fields('field_data_table2', array('entity_id'));
$query->execute();

The above code produces the following SQL :

SELECT field_data_field_table1.entity_id AS entity_id, field_data_field_table2.entity_id AS field_data_field_table2_entity_id FROM {field_data_field_table1} field_data_field_table2

Which produces the following SQL error :

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_data_field_table1.entity_id' in 'field list'

Am I missing something here? I can do this query directly in MySQL but I feel like this should be easy in Drupal to select two columns across two tables.

Thanks!

⋅ Categories: Drupal 7.x

Comments

Jaypan’s picture

Jaypan commented

You need to join your tables. The Drupal DB doesn't allow for selecting from two tables that way.

kevinnivek’s picture

kevinnivek commented

I had a feeling that that may be the reason!

Thanks for this. I'll work towards that. I was thrown off because in raw mysql you dont need to join tables. Is there any reason this is implemented this way?

kevinnivek’s picture

kevinnivek commented

Strange.. still giving me the column errors :

$query = db_select('field_data_field_table1', 'field_data_field_table2');
$query_2->join('field_data_field_table1', 't1', 't1.entity_id = field_data_field_table2.entity_id');
$query 
->fields('field_data_table1', array('entity_id'))
->fields('field_data_table2', array('entity_id'));
$query->execute();

Produces this error :

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_data_field_table1.entity_id' in 'field list'

Any idea why that might happen?

Jaypan’s picture

Jaypan commented

This:

->fields('field_data_table1', array('entity_id'))

Needs to be this:

->fields('t1', array('entity_id'))