db_select query advice

Question

I have two content types: development and house.

A development has many houses.

Each house has a house_type field

I'm trying to perform a db_select query that allows me to search all developments and find the house_types attached to them.

I've managed to find the developments, then found the attached houses_on_development (i've used group_concat for this), AND THEN, WHERE I'M STUCK...

I need to query each of the house_types of these concatenated house_ids individually to find out their house_type.

I've started with:

$search_type = 'development';
$query = db_select('node', 'development');
$query->condition('development.status', '1', '=');

 $query->leftjoin('field_data_belongs2dev', 'property',
        'development.nid = property.field_data_belongs2dev_target_id');
$query->addExpression('group_concat(property.entity_id)', 'houses_on_development');

And now i'm stuck. I know how to write the query to get the house_types if I was querying house_id one at a time but don't know how to fit that query into a bigger db_select. For this I would use:

SELECT field_property_type_tid
   FROM field_data_field_property_type
   WHERE entity_id
   IN  ( house_id1, house_id2, etc... )

I feel like i'm so close but missing that final bit od knowledge. Please help.

Answer 1

Think your query needs to be:

$query = db_select('node', 'development');
$query->fields('property_type', array('field_property_type_tid'))
  ->condition('development.status', 1, '=')
  ->addJoin('LEFT', 'field_data_belongs2dev', 'property', 'development.nid = property.field_data_belongs2dev_target_id');
$query->addJoin('LEFT', 'field_data_field_property_type', 'property_type', 'property.field_data_belongs2dev_target_id = property_type.entity_id');
$query->addExpression('group_concat(property.entity_id)', 'houses_on_development');
$results = $query->execute();

Hope this should point you in the right direction.