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:

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
  (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')

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?

  • can you export the View and attach? Commented Feb 2, 2016 at 4:46
  • @petednz-fuzion, thanks for having a look. Here's a pastebin: pastebin.com/yge5f8pL. Commented Feb 2, 2016 at 16:25
  • It's worth noting that there are three displays in this View. I deleted one from the export just for length. The master display works fine. The broken display (Second Page) is a clone of the first except that I've added a filter for the field "CiviCRM Custom: Agency Details: Accepts Food Donations." Commented Feb 2, 2016 at 16:28
  • well i just had to keep removing stuff till it worked for me ;-) Ie remove the Filters that didn't make sense without the fields, remove the Contextual Filter, and change it to 'table' plugin. By that time it worked but probably doesn't help. You have a single DB, or separate. if the latter have you updated your settings.php with any new table names and cleared caches. (don't feel i am being v useful here.) Commented Feb 2, 2016 at 19:27
  • Thanks for looking into it, @petednz-fuzion. I finally got to the bottom of it myself. Basically, this View was a big fricken mess with a bunch of cruft from (I assume) previous iterations of the page. Despite the display plugin being set to GMap, this page displays no map, and the table I was looking at on the page was actually an attachment, so I guess I was editing the wrong filters, etc. In the end I more or less built a new View, left the cruft behind, and that did the trick. Commented Feb 2, 2016 at 19:50

1 Answer 1


This might work:

  • Goto Query Settings under Advanced options and disable SQL rewriting.


  • Try providing the view a default value for the contextual filter under "When filter value is not available option."
  • 1
    It is good to mention that Disabling SQL rewriting on a query disables permission checks on displayed content and it is better to use it for debugging purposes, then as a workaround for underlying permission issues.
    – oksana-c
    Commented Feb 2, 2016 at 7:16

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.