CiviCRM Community Forums (archive)

I've been doing some work to speed up the import of a csv file containing changes and additions to contacts and activity information from foot canvassing. The approach is to see how much improvement could be gained by pushing all of the processing into mysql queries rather than doing it row by row in php. More on that another time.

One irritation in working with MySQL is that it doesn't support insertions into multiple tables in a single query. In some cases it seems you need to get the id of an insertion in one table in order to store that id in a record being inserted into another table before proceeding to the next insertion. For example, when creating an activity, you may need to use civicrm_activity.id when inserting related records in civicrm_activity_target, civicrm_activity_assignment, and any civicrm_value_xxx custom value tables extending the activity.  

However, stored functions provide an easy way to continue using queries to process large volumes of records with single query statements rather than reverting to sequential row-by-row processing, as the following example shows.

DELIMITER //
CREATE
Function insert_canvass
(Psource_contact_id int(10) unsigned,
 Psubject varchar(255),
 Pactivity_date_time datetime,
 Passignee_contact_id int(10) unsigned,
Ptarget_contact_id int(10) unsigned,
Pmark varchar(64),
Psign varchar(64),
Pdonor varchar(64),
Pvolunteer varchar(64)
)
RETURNS  int(10) unsigned

COMMENT 'Inserts into 4 tables'
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE Vactivity_id int(10) unsigned;
INSERT INTO civicrm_activity (source_contact_id, activity_type_id, subject, activity_date_time, status_id, priority_id)
VALUES (Psource_contact_id, 23, Psubject, Pactivity_date_time, 2, 2 );
SET Vactivity_id = LAST_INSERT_ID();
INSERT INTO civicrm_activity_assignment (activity_id, assignee_contact_id)
VALUES (Vactivity_id, Passignee_contact_id);
INSERT INTO civicrm_activity_target (activity_id, target_contact_id)
VALUES (Vactivity_id, Ptarget_contact_id);
INSERT INTO civicrm_value_canvass_results_1 (entity_id, mark_1, sign_2, donor_3, volunteer_4) VALUES (Vactivity_id, Pmark, Psign, Pdonor, Pvolunteer);
RETURN Vactivity_id;
END//
DELIMITER ;
This can be run to insert the activities from an import table using something like the following:
SELECT insert_canvass(2,i.short_comment,i.canvass_date,i.canvasser_id,i.civicrm_id,m.value,s.value, d.value, v.value)
from civicrm_import_job_ff52b66bb116f0f9f01916df9d08cc65 i
LEFT JOIN civicrm_option_value m on m.name=i.mark and m.option_group_id=80
LEFT JOIN civicrm_option_value s on s.name=i.sign and s.option_group_id=81
LEFT JOIN civicrm_option_value d on d.name=i.donor and d.option_group_id=82
LEFT JOIN civicrm_option_value v on v.name=i.volunteer and v.option_group_id=83

In the SELECT query, 2 is a hard-coded contact id for the current user who is creating the canvass activities (actually it's programmatically added to the sql at runtime but appears hard-coded here for convenience), and mark, sign, donor, and volunteer are custom fields in the civicrm_value_canvass_results_1 table.

If desired, an Update statement could be used to store the activity_id for each row, or perhaps a url that would display the activity, and that id or url returned to the user upon completion of the import.