Custom Search Problem - Unknown column contact_a.id in field list when tagging returned contacts

Question

I'm running into almost the same problem that was detailed a couple years ago on the forums at https://forum.civicrm.org/index.php?topic=28829.0. When I run a custom search using the built in Contributions made in Year X and not Year Y search, and then pick Select All Results and select Tag Contacts from the drop down, I get a DB Error: no such field. The full logged error is:

Jan 06 16:27:59  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => 
SELECT     contact_a.id as contact_id
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 >= 20150101000000 AND contrib_1.receive_date <= 20151231000000
GROUP BY   contact.id

ORDER BY   donation_amount desc
 [nativecode=1054 ** Unknown column 'contact_a.id' in 'field list']
    [type] => DB_Error
    [user_info] => 
SELECT     contact_a.id as contact_id
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 >= 20150101000000 AND contrib_1.receive_date <= 20151231000000
GROUP BY   contact.id

ORDER BY   donation_amount desc
 [nativecode=1054 ** Unknown column 'contact_a.id' in 'field list']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="
SELECT     contact_a.id as contact_id
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 >= 20150101000000 AND contrib_1.receive_date <= 20151231000000
GROUP BY   contact.id

ORDER BY   donation_amount desc
 [nativecode=1054 ** Unknown column 'contact_a.id' in 'field list']"]
)

This appears to occur no matter what size the result set is (I had one test set of 1 result, and 1 with 1767), on current versions (I noticed it first on 4.4.19 but I retested on 4.6.10 with the same result), and does occur not just for adding a tag but also for putting them in a group like the original poster describes.

Has anyone found a solution for this or at least a workaround?

Answer 1

I just tried to reproduce the issue in 4.6.11 and wasn't able to; though, it's possible I didn't pick the "correct" criteria. I know there has been some work done on the dynamically created sql, which this is, in more recent releases that might have fixed the issue. I suggest you try to reproduce it in one of the demo databases.

When I've run into things like this before, sometimes, though not always, I can get around the issue by running the query, creating a smart group out of it and then doing an advanced search with the only criteria being the contacts are in that smart group. Then doing the tags or whatever with that resultant set.