Complex Views / Field Collection question 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-&gt;entityCondition('entity_type', 'field_collection_item')
        -&gt;entityCondition('bundle', 'field_property_booking');
    $lookup-&gt;fieldCondition('field_property_booking_basis', 'value', 'Weekly', '=');
    $lookup-&gt;fieldCondition('field_property_booking_period', 'value', 1348891200, '=');
    $lookup-&gt;fieldCondition('field_property_booking_available', 'value', 1, '=');
    $rates = $lookup-&gt;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?
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.