I've got a Drupal View that I expect to return some results, but when I look at it (logged in as user 1), the empty result message is displayed. I turned on the setting which tells Views to output SQL in the preview. When I run the query directly against the database I get the expected result.
The View uses CiviCRM fields. I checked watchdog and the CiviCRM log for errors and found none.
The query looks like this:
SELECT DISTINCT <long list>
FROM {civicrm_contact} civicrm_contact
LEFT JOIN {civicrm_address} civicrm_address
ON civicrm_contact.id = civicrm_address.contact_id
LEFT JOIN {civicrm_phone} civicrm_phone
ON civicrm_contact.id = civicrm_phone.contact_id
LEFT JOIN {civicrm_value_agency_details_5} civicrm_value_agency_details_5
ON civicrm_contact.id = civicrm_value_agency_details_5.entity_id
LEFT JOIN {civicrm_phone} civicrm_phone2
ON civicrm_contact.id = civicrm_phone2.contact_id
WHERE ((
(civicrm_contact.contact_sub_type IN ('Agency'))
AND (civicrm_address.city LIKE '%Brooklyn%' ESCAPE '\\')
AND (civicrm_phone.is_primary <> '0')
AND (civicrm_address.is_primary = '0')
AND (civicrm_contact.is_deleted = '0')
AND (civicrm_value_agency_details_5.accepts_food_donations_144 <> '0')
))
GROUP BY id
I am a little surprised it works at all; the id in the GROUP BY clause seems ambiguous.
Has anyone run into anything like this before? I am not too familiar with the Views codebase. Can anyone recommend where to throw in some debugging code to get a better sense of what's going on? Does Views do any post-query processing -- could it decide not to output what the query returns?
