Integrate the Views upgrades in CRM-8241 & CRM-7766 with Views filters

Explanation: Tickets CRM-7766 & CRM-8241 introduce the idea of using conditional joins to filter what values are displayed in a field. This is different from filters (ie the one that produce SQL WHERE clauses) in that is won't restrict the entire record from being displayed but only apply restrictions to the values displayed views field (ie SQL Select statement). In other words you can display all the records you want but only show the values in a field if they meet a certain criteria.

This only works on tables brought into the view via a join, (in a CiviCRM Contact View you can only apply this principal to tables that are joined like civicrm_address, civicrm_phone, etc...). This cannot be applied to tables that can be used as a base table for a view (civicrm_contact, civicrm_membership, civicrm_contribution, civicrm_relationship, civicrm_grant, civicrm_event, civicrm_mailing, civicrm_campaigns, civicrm_pledges, civicrm_participants) as in their respective views they are not joined they are the there as the initial table liste din the SQL FROM statement (hence they are called the base table).

The Problem: This all works great as long as you aren't applying a filter once you apply a filter then views gets in the way of the conditional join by creating it's own join that that does not apply the conditional filter. This will get event nastier when you have multiple joins to the same table (say you want one field to show the phone number of type "Phone" and a 2nd field to show the numbers of the type "Mobile"), the example below ignores the multiple instances of a table problem.

By way of example
Query

SELECT civicrm_contact.id AS id,
civicrm_phone2.phone AS civicrm_phone2_phone
FROM civicrm_demo_drupal.civicrm_contact civicrm_contact
LEFT JOIN civicrm_demo_drupal.civicrm_phone civicrm_phone ON civicrm_contact.id = civicrm_phone.contact_id
LEFT JOIN civicrm_demo_drupal.civicrm_phone civicrm_phone2 ON civicrm_contact.id = civicrm_phone2.contact_id AND civicrm_phone2.phone_type_id = 1
WHERE civicrm_phone.location_type_id in ('5', '1', '3', '4', '2')

Note the where is applied to civicrm_phone which is added by a filter while a conditional join (for phone type =1) is added by choosing a phone_type when adding/editing a phone field in views. These two collide and the affect is phone numbers that don't meat the views filter (ie the WHERE clause) are allowed through by the 2nd join (ie the conditional join).

Solution: I don't really have on yet to be honest this one gets quite sticky given the way Views constructs SQL, not that I'm saying Views is wrong as I don't have a beter way to do it.