1

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.

  • You might want to take a look at entity field queries, rather then db_queries. Entity field queries are designed to do just this drupal.org/node/1343708 – Neograph734 Aug 7 '15 at 12:52
0

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.

  • Thanks for taking the time to reply, however, this seems to product the same result i've already got. That is a comma separated list of house IDs. I then need to query the 'proerties ids to get the houuse type id – useyourillusiontoo Aug 7 '15 at 12:47
  • I'm sorry but I'm not 100% sure what you're trying to achieve, maybe a fuller explanation would help – Colin Shipton Aug 7 '15 at 13:28

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.