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?