0

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?

  • can you export the View and attach? – petednz - fuzion Feb 2 '16 at 4:46
  • @petednz-fuzion, thanks for having a look. Here's a pastebin: pastebin.com/yge5f8pL. – universalhandle Feb 2 '16 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." – universalhandle Feb 2 '16 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.) – petednz - fuzion Feb 2 '16 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. – universalhandle Feb 2 '16 at 19:50
0

This might work:

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

OR

  • 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 Feb 2 '16 at 7:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.