cannot create smart group based on query with ORDER BY or GROUP BY specified

When creating a smart group based on a query that includes an ORDER BY or GROUP BY clause, CiviCRM generates a fatal error.

Here are the steps on the D7 demo site that I took:

• Added a donation to an existing contact
• Went to Search -> Custom Searches -> Contributions made in Year X and not Year Y
• For Inclusion date, I Entered 1/1/2011 to 12/31/2011
• Clicked Search and found my one result
• Selected "New Smart Group" from actions menu
• Entered name and clicked save

And I got the error copied below.

The problem seems to be in CRM/Contact/BAO/GroupContactCache.php in the load function.

On line 272, the variable $searchSQL is filled with the output of $customClass->contactIDs( ). $customClass->contactIDs( ) generates a SQL statement that will include ORDER BY and GROUP BY clauses (if those are present).

Then, on line 292, and additional WHERE clause is appended to the variable. This works fine if no ORDER BY or GROUP BY clause has been added, but will fail if one has been added because it creates a SQL statement with a syntax error.

I'm not sure the proper way to fix this - perhaps all $customClass contactID functions should allow you to insert extra WHERE clauses?

jamie

Here's the error:
Database Error Code: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND contact_a.id NOT IN (
SELECT contact_id FROM ' at line 16, 1064</div>
<div class="crm-section">Additional Details: <p></p><pre>Array
(
[callback] => Array
(
[0] => CRM_Core_Error
[1] => handle
)

[code] => -2
[message] => DB Error: syntax error
[mode] => 16
[debug_info] =>

SELECT DISTINCT contact.id as contact_id,
contact.display_name as display_name,

sum(contrib_1.total_amount) AS donation_amount,
count(contrib_1.id) AS donation_count

FROM civicrm_contact AS contact
LEFT JOIN civicrm_contribution contrib_1 ON contrib_1.contact_id = contact.id

WHERE contrib_1.contact_id = contact.id
AND contrib_1.is_test = 0
AND contrib_1.receive_date >= 20110101000000 AND contrib_1.receive_date <= 20111231000000
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 = 6 ) UNION
SELECT contact_id as contact_id
FROM civicrm_group_contact
WHERE civicrm_group_contact.status = 'Added'
AND civicrm_group_contact.group_id = 6 [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND contact_a.id NOT IN (
SELECT contact_id FROM ' at line 16]