CiviRules

SQL Tasks can be used as actions in CiviRules. This allows administrators to implement custom business logic without having to develop custom actions inside an extension.

To enable the CiviRules integration in SQL Tasks, visit /civicrm/civirule/form/settings to add the "Run SQL Task" action.

Example

First, create a new CiviRule using any trigger and/or condition. We will use a CiviRule with the "Contact is tagged (tag is added to contact)" trigger as our example. You could add a condition in CiviRules if you'd like to only trigger the rule for certain tags.

Next, create a new SQL Task. To make the task accept inputs from CiviRules, we need to enable the "Require user input" flag for our task. We also need to set "Allow parallel execution" to "Always (multiple instances)" since it's possible for the same CiviRule to be triggered at the same time (e.g. if multiple users are doing the same thing).

Finally, add a "Run SQL Task" action to the CiviRule and select the new task.

The CiviRule will now execute the task whenever a tag is added to a contact. In the SQL Task, we want to export the contact to a CSV file whenever this happens. To do that, we need to figure out which contact has been tagged. CiviRules provides this value in a JSON object. The JSON value is provided as the @input variable in SQL. The exact format of this JSON depends on the trigger used in CiviRules. For the trigger used in our example, the value of @input may look as follows (JSON has been beautified for better readability):

{
  "trigger": "CRM_CivirulesPostTrigger_EntityTag",
  "contact_id": 123,
  "entity_custom_data": [],
  "entity": "entitytag",
  "entitytag_data": {
    "id": 345,
    "tag_id": "1",
    "entity_id": 123,
    "entity_table": "civicrm_contact",
    "contact_id": 123
  }
}

Tip

The easiest way to inspect the payload generated by a CiviRule is to set up the SQL Task and CiviRule as described above, and then write the payload to a table, e.g. using this query:

DROP TABLE IF EXISTS temp_inspect_payload;
CREATE TABLE temp_inspect_payload AS SELECT @input AS input;

You can then trigger the rule and inspect the payload in the temp_inspect_payload table.

We can use MySQL's JSON functions to parse and extract data from this string using JSONPath, which is an XPath-like syntax for JSON. The following expression will return the contact's ID:

JSON_UNQUOTE(JSON_EXTRACT(@input, '$.contact_id'))

JSON_EXTRACT() accepts two parameters: the JSON string, and a JSONPath expression. It returns the result within quotes (i.e. "123", so we need to use JSON_UNQUOTE() to remove quotes.

Note

These functions are supported by MySQL >= 5.7.8 and MariaDB >= 10.2.3.

A full example which saves the contact's ID and name to a table may look as follows:

DROP TABLE IF EXISTS `temp_export_tagged_contact_{context.random}`;
CREATE TABLE `temp_export_tagged_contact_{context.random}` AS
SELECT
    c.id AS contact_id,
    first_name,
    last_name
FROM
    civicrm_contact c
WHERE
    c.id = JSON_UNQUOTE(JSON_EXTRACT(@input, '$.contact_id'))
;

Note that we added a {context.random} token to the table name to avoid issues when tasks are executed in parallel. {context.random} is a random string that is generated whenever a task is executed, meaning we can use it as part of our table name to ensure uniqueness.

To use this table as part of a "CSV Export" action, we need to include the token as well. The value for "Export Table" would be: temp_export_tagged_contact_{context.random}

It may be a good idea to include the random value in the file name of the exported CSV as well, i.e. export-{context.random}.csv.