CiviCRM Community Forums (archive)

Hi,

I developed a custom search, use the result for smart groups (works fine, even for mail activity").

When I use this group as a recipient for civimail, it crashed:
[debug_info] => INSERT IGNORE INTO  I_1 (email_id, contact_id)
                    SELECT DISTINCT     civicrm_email.id as email_id,
                                        contact_a.id as contact_id
                    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)
                    LEFT JOIN           X_1
                            ON          contact_a.id = X_1.contact_id
                    WHERE           
                                        contact_a.do_not_email = 0
                        AND             contact_a.is_opt_out = 0
                        AND             contact_a.is_deceased = 0
                        AND             (civicrm_email.is_bulkmail = 1 OR civicrm_email.is_primary = 1)
                        AND             civicrm_email.on_hold = 0
                                         AND contact_a.contact_type = 'Individual' AND civicrm_relationship.contact_id_b=3694
                        AND             contact_a.id NOT IN (
                                          SELECT contact_id FROM civicrm_group_contact
                                          WHERE civicrm_group_contact.group_id = 46 AND civicrm_group_contact.status = 'Removed')
                        AND             X_1.contact_id IS null
                    ORDER BY civicrm_email.is_bulkmail [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 'civicrm_contact AS contact_a LEFT JOIN civicrm_relationship ON (civicrm_relation' at line 4]


Is there a different method to override for the mailing in the smart group for civimail than the one for the "normal" groups usage or ?

X+

hi,

Currently for some custom searches, mailing is break for custom search -> smart group.
There are mainly two cases by which query is fail
1. Missing 'From' word  from 'from clause'.
2. Missing 'civicrm_email' table from joins.

This is due to some structural issue related to custom search code.

To fix you need to hack the code for get recipients.
go to CRM/Mailing/BAO/Mailing.php line around 305
ie
list( $from, $where ) = CRM_Contact_BAO_SavedSearch::fromWhereEmail( $ss->saved_search_id );after this line you need to crrect from clause.

code might be
           
            if ( !stristr( $from, 'from' ) ) {
                $from = 'FROM '. $from;
            }
           
            if ( !stristr( $from, 'civicrm_email' ) ) {
                $from .= ' LEFT JOIN civicrm_email ON ( contact_a.id = civicrm_email.contact_id )';
            }

(IMO) for long term, solution would be fix custom search code.

kiran