CiviCRM Community Forums (archive)

Hi,

I have created a custom search, which seems to work and delivers the expected results.  However when I select all result records and try to create a smart group, I get a DB error referring to a field that is not part of my query.

The SQL query is:
SELECT
           DISTINCT (c.id) as contact_id,
           c.household_name as house,
           COUNT(cR.contact_id_a) as cp,
           COUNT(cDate.date_left_eco_10) as cl
           
            FROM   
        civicrm_contact c
        RIGHT JOIN civicrm_relationship cR ON cR.contact_id_b = c.id
        RIGHT JOIN civicrm_value_player_info_1 cDate ON cDate.entity_id = cR.contact_id_a
        LEFT JOIN civicrm_group_contact cGroup ON cGroup.contact_id = cDate.entity_id
The error message is

DB Error: no such field
Database Error Code: Unknown column 'contact_a.id' in 'IN/ALL/ANY subquery', 1054

Error Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

[c*de] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] =>
            SELECT
           DISTINCT (c.id) as contact_id,
           c.household_name as house,
           COUNT(cR.contact_id_a) as cp,
           COUNT(cDate.date_left_eco_10) as cl
           
            FROM   
        civicrm_contact c
        RIGHT JOIN civicrm_relationship cR ON cR.contact_id_b = c.id
        RIGHT JOIN civicrm_value_player_info_1 cDate ON cDate.entity_id = cR.contact_id_a
        LEFT JOIN civicrm_group_contact cGroup ON cGroup.contact_id = cDate.entity_id
           
            WHERE  c.contact_type = 'Household'  AND cGroup.group_id = 6 AND cGroup.status = 'Added'
            GROUP BY house
            HAVING cl < cp
             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 = 18 )  UNION
SELECT contact_id as contact_id
FROM   civicrm_group_contact
WHERE  civicrm_group_contact.status = 'Added'
  AND  civicrm_group_contact.group_id = 18  [nativecode=1054 ** Unknown column 'contact_a.id' in 'IN/ALL/ANY subquery']
    [type] => DB_Error
    [user_info] =>
            SELECT
           DISTINCT (c.id) as contact_id,
           c.household_name as house,
           COUNT(cR.contact_id_a) as cp,
           COUNT(cDate.date_left_eco_10) as cl
           
            FROM   
        civicrm_contact c
        RIGHT JOIN civicrm_relationship cR ON cR.contact_id_b = c.id
        RIGHT JOIN civicrm_value_player_info_1 cDate ON cDate.entity_id = cR.contact_id_a
        LEFT JOIN civicrm_group_contact cGroup ON cGroup.contact_id = cDate.entity_id
           
            WHERE  c.contact_type = 'Household'  AND cGroup.group_id = 6 AND cGroup.status = 'Added'
            GROUP BY house
            HAVING cl < cp
             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 = 18 )  UNION
SELECT contact_id as contact_id
FROM   civicrm_group_contact
WHERE  civicrm_group_contact.status = 'Added'
  AND  civicrm_group_contact.group_id = 18  [nativecode=1054 ** Unknown column 'contact_a.id' in 'IN/ALL/ANY subquery']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="
            SELECT
           DISTINCT (c.id) as contact_id,
           c.household_name as house,
           COUNT(cR.contact_id_a) as cp,
           COUNT(cDate.date_left_eco_10) as cl
           
            FROM   
        civicrm_contact c
        RIGHT JOIN civicrm_relationship cR ON cR.contact_id_b = c.id
        RIGHT JOIN civicrm_value_player_info_1 cDate ON cDate.entity_id = cR.contact_id_a
        LEFT JOIN civicrm_group_contact cGroup ON cGroup.contact_id = cDate.entity_id
           
            WHERE  c.contact_type = 'Household'  AND cGroup.group_id = 6 AND cGroup.status = 'Added'
            GROUP BY house
            HAVING cl < cp
             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 = 18 )  UNION
SELECT contact_id as contact_id
FROM   civicrm_group_contact
WHERE  civicrm_group_contact.status = 'Added'
  AND  civicrm_group_contact.group_id = 18  [nativecode=1054 ** Unknown column 'contact_a.id' in 'IN/ALL/ANY subquery']"]
)

In the error message, my original SQL query seems to have been extended by an additional AND, from "AND contact_a.id NOT IN ...".  I'm guessing this is something to do with the caching of searches for smart groups, but why does the error occur?

In fact the smart group does get saved but I then get the same error when I try to use the group - for instance through the "Find Contacts" form.

Thanks for any tips.

can u alias your civicrm_contact table to contact_a

i.e.

FROM civicrm_contact contact_a
...

We need to fix the documentation to reflect this (will do so later tonite)

lobo

To save custom search as a smart group, you need to implement the contactIDs( ) function

the "select" clause from that sql should be just retrieve the contactID. We'll re-read the fix the documentation for 3.0 soon

lobo