CiviCRM Community Forums (archive)

I have a module that needs to build a list of all emails and names for contacts, including the group(s) the contact is in, if it is a mailing list group. We send this list to the API of a third party service who handles the mailers.

The basic code I have is:

1. Find all smart groups which are mailing list type
2. Find all contacts on each group (one by one)
3. Determine if each contact did not check off 'do_not_email' or 'is_opt_out'
4. If so, add that contact to the list to send via API

Step 5 is that I run one SQL to extract emails/names/groups for all non-smart groups.

This code was working fine, but now it is crashing the dedicated server--my guess is that we now have too many contacts for this brute-force approach. Here is my code for steps 1-4:

// This is a custom file which is attached to this forum post
require_once 'CRM/Utils/SmartGroup.php';
$_smartgroup = new CRM_Utils_SmartGroup( $params );
// group_type 2 means mailing list type
$sql = 'select  id, title from civicrm_group  where saved_search_id is not null and group_type like "%2%";';
$dao =& CRM_Core_DAO::executeQuery( $sql,$empty_array);
while ($dao->fetch()) {
  $contact_list = $_smartgroup->SmartGroupContacts($dao->id);
  foreach($contact_list as $id) {
    $sub_sql = "select civicrm_contact.id,civicrm_email.email, civicrm_contact.first_name, civicrm_contact.last_name from civicrm_contact ".
      "left join civicrm_email on civicrm_contact.id = civicrm_email.contact_id where civicrm_contact.id = {$id['contact_id']} ".
      "and civicrm_email.is_primary=1 ";
      "and civicrm_contact.do_not_email=0 and civicrm_contact.is_opt_out=0 ";
    $sub_dao =& CRM_Core_DAO::executeQuery( $sub_sql,$empty_array);
    while ($sub_dao->fetch()) {
      $output .= '"'.$sub_dao->first_name.'","'.$sub_dao->last_name.
        '","'.$sub_dao->email.'","'.$dao->title." (Smart Group)\"\n";
    }
  }
}

The largest smart group has 33774 members today. An alternative approach to this which occurred to me would be like this:

1. Find all smart groups which are mailing list type
2. Find all contacts on each group (one by one)
3. Insert all of those contacts into a spare DB table
4. Run one large SQL to extract the list of emails/names using that spare table as the initial table in the WHERE clause
5. Add those contacts to the list to send via API

This would accomplish the search via one large SQL statement with 33774 result rows instead of running a smaller statement 33774 times. That should be far more efficient.

Are there any other ideas, however?

Thanks