Author Topic: Create a smart group from a custom search (Read 1507 times)

xavier · January 15, 2009, 06:30:48 am

Hi,

I developed a custom search to get all the related individuals of a household.

When I save the group, it crashes.
1) I had to remove the order by from the query
2) Now I have:

Code: [Select]

SELECT 
            civicrm_contact.id as contact_id,
            sort_name,first_name,last_name,
            organization_name as employer,
            email,phone, civicrm_country.name as country,
             
            name_a_b as relation
            
            FROM   civicrm_contact LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_a=civicrm_contact.id and (end_date >= now() or end_date is null) and civicrm_relationship.is_active=1 ) LEFT JOIN civicrm_relationship_type ON (relationship_type_id=civicrm_relationship_type.id ) LEFT JOIN civicrm_email email ON (email.contact_id = civicrm_contact.id AND email.is_primary= 1 ) LEFT JOIN civicrm_phone phone ON (phone.contact_id = civicrm_contact.id AND phone.is_primary= 1 ) LEFT JOIN civicrm_address address ON (address.contact_id = civicrm_contact.id AND address.is_primary= 1) LEFT JOIN civicrm_country ON (civicrm_country.id = address.country_id)
            WHERE  civicrm_contact.contact_type = 'Individual' AND civicrm_relationship.contact_id_b=3688 
            
            
             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 = 31 )  UNION 
SELECT contact_id as contact_id
FROM   civicrm_group_contact
WHERE  civicrm_group_contact.status = 'Added'
  AND  civicrm_group_contact.group_id = 31

With the error message that

Quote

Unknown column 'contact_a.id' in 'IN/ALL/ANY subquery

Are they specific names convention on the tables/fields that we must select AS to make the smart group works with the custom searches ?

Tia

X+

xavier · January 15, 2009, 10:21:51 am

Hi,

As per Lobo suggestion, I did rename the civicrm_contact as contact_a. Went a bit further but now get an "he used SELECT statements have a different number of columns" :

Code: [Select]

SELECT 
            contact_a.id as contact_id,
            sort_name,first_name,last_name,
            organization_name as employer,
            email,phone, civicrm_country.name as country,
             
            name_a_b as relation
            
            FROM   civicrm_contact AS contact_a LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_a=contact_a.id and (end_date >= now() or end_date is null) and civicrm_relationship.is_active=1 ) LEFT JOIN civicrm_relationship_type ON (relationship_type_id=civicrm_relationship_type.id ) LEFT JOIN civicrm_email email ON (email.contact_id = contact_a.id AND email.is_primary= 1 ) LEFT JOIN civicrm_phone phone ON (phone.contact_id = contact_a.id AND phone.is_primary= 1 ) LEFT JOIN civicrm_address address ON (address.contact_id = contact_a.id AND address.is_primary= 1) LEFT JOIN civicrm_country ON (civicrm_country.id = address.country_id)
            WHERE  contact_a.contact_type = 'Individual' AND civicrm_relationship.contact_id_b=3688 
            
            
             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 = 11 ) 

[b]UNION [/b]

SELECT contact_id as contact_id
FROM   civicrm_group_contact
WHERE  civicrm_group_contact.status = 'Added'
  AND  civicrm_group_contact.group_id = 11

Does it mean that the select should only fetch the contact id so the union works ? In that case, it can't be the same custom search for the smart group and the ones used to display the results.

Any suggestion ?

X+

xavier · January 15, 2009, 11:12:41 am

Solved by changing the contactIDs method that way:

Code: [Select]

    function contactIDs( $offset = 0, $rowcount = 0, $sort = null) {
        $select  = "
            contact_a.id as contact_id";
        $from  = $this->from( );
        $where = $this->where(  );
        $having = $this->having( );
        if ( $having ) {
            $having = " HAVING $having ";
        }

        $sql = "
            SELECT $select
            FROM   $from
            WHERE  $where
            $grouping
            ";
        return $sql;
    }

Kiran Jagtap · January 15, 2009, 09:22:14 pm

hi

There was an issue with Smart group creation using Custom Search, which was fixed.
http://issues.civicrm.org/jira/browse/CRM-3865

thanks
kiran

CiviCRM Community Forums (archive)

News:

Author Topic: Create a smart group from a custom search (Read 1507 times)

xavier

Create a smart group from a custom search

xavier

Re: Create a smart group from a custom search

xavier

Re: Create a smart group from a custom search

Kiran Jagtap

Re: Create a smart group from a custom search