CiviCRM Community Forums (archive)

Having same issue.  In my case it's getting malformed sql.

It's adding an and clause to make sure the contact hasn't been removed but it adds the clause after the order by.

The smart group is based upon the custom query "Contributions made in Year X and not Year "  (sites/all/modules/civicrm/CRM/Contact/Form/Search/Custom/ContribSYBNT.php).

Here is the sql it's barfing on:

CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache955
(SELECT 65 as group_id, contact_a.id as contact_id
FROM civicrm_contact AS contact LEFT JOIN
         ncivicrm_contribution contrib_1 ON contrib_1.contact_id = contact.id LEFT JOIN         
         XG_CustomSearch_SYBNT xg ON xg.contact_id = contact.id
WHERE contrib_1.contact_id = contact.id
AND contrib_1.is_test = 0
AND contrib_1.receive_date >= 20120701000000
AND contrib_1.receive_date <= 20130630000000
AND xg.contact_id IS NULL
GROUP BY contact.id
ORDER BY donation_amount desc
AND contact_a.id NOT IN ( SELECT contact_id
                                       FROM civicrm_group_contact
                                       WHERE civicrm_group_contact.status = 'Removed'
                                       AND civicrm_group_contact.group_id = 65 ) )

I tried moving the last and clause to before the group by and manually run it but then it fails on not finding the table XG_CustomSearch_SYBNT.  I'm not sure if that is a temp table or on the fly view or what.  Maybe it would exists when running this.

I guess it makes some sense to be able to manually remove someone from a group like this but alas, I tried it and that fails as well with unknown column 'contact_a.id', so at the moment the extra "and not in" won't come up with anything anyway.

To see if we had any others that don't work, I disabled that one and the display smartgroup runs on all the other.  We have 61 smart groups.