Is there a way to do a subquery in a View that filters entities based on a filter value in a field collection, but also by another value attached to that field collection item?

For example, people are searching properties. Property rates are stored in a field collection that references dates, the price, and if it is available.

When a user uses the view and searches by a particular date, I only want to return the entities that have both that date AND an available = 1 on that field collection item. Just adding the 'available' filter on the View is not enough, because it needs to search the value attached to the field collection item id that the date is attached to.

My first thought was to use hook_views_query_alter:

$lookup = new EntityFieldQuery;
    $lookup->entityCondition('entity_type', 'field_collection_item')
        ->entityCondition('bundle', 'field_property_booking');
    $lookup->fieldCondition('field_property_booking_basis', 'value', 'Weekly', '=');
    $lookup->fieldCondition('field_property_booking_period', 'value', 1348891200, '=');
    $lookup->fieldCondition('field_property_booking_available', 'value', 1, '=');
    $rates = $lookup->execute();

Okay, so, based on the timestamp from the form submission, I get 5,172 results. I am not sure where to go at this point. I want to say something like the main entity has field collection item IDs IN () with the item id's above, but I am not sure how to do that.

Can I plug in somehow with $query->where conditions? Or is there a better way to do this?

1 Answer 1


After some direction from merlinofchaos, here is what I did:

  • Add the field as an exposed filter
  • Use form_alter to change it to hidden
  • Use javascript to set the value to 1, bound to a change event on the date select

Otherwise its like asking for a conditional filter (if filter X has value, then set these filters to Y) which is not so easy in UI.

You could also inspect $_GET and use hook_views_query_alter to add a $query->where, but I had better luck with Javascript.

Hope this helps others.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.