Sending a daily summary of activity

We can integrate Gato GraphQL with WP-Cron, as to automate the execution GraphQL queries that perform admin tasks, with some time interval. (The Automation extension is required.)

In this tutorial lesson, we set-up WP-Cron to, every 24 hs, execute a GraphQL query that retrieves the number of new comments added to the site, and sends these stats to the desired email account.

GraphQL query with daily stats of new comments permalink

This GraphQL query sends an email indicating the number of new comments added to the site for several periods of time:

  • In the last 24 hs
  • In the last 1 year
  • Since the beginning of the month
  • Since the beginning of the year

We create a Persisted Query with slug "daily-stats-by-email-number-of-comments" and content:

query CountComments {
DATE_ISO8601: _env(name: DATE_ISO8601) @remove

timeToday: _time
dateToday: _date(format: $__DATE_ISO8601, timestamp: $__timeToday)

timeYesterday: _intSubstract(substract: 86400, from: $__timeToday)
dateYesterday: _date(format: $__DATE_ISO8601, timestamp: $__timeYesterday)

time1YearAgo: _intSubstract(substract: 31536000, from: $__timeToday)
date1YearAgo: _date(format: $__DATE_ISO8601, timestamp: $__time1YearAgo)

timeBegOfThisMonth: _makeTime(hour: 0, minute: 0, second: 0, day: 1)
dateBegOfThisMonth: _date(format: $__DATE_ISO8601, timestamp: $__timeBegOfThisMonth)

timeBegOfThisYear: _makeTime(hour: 0, minute: 0, second: 0, month: 1, day: 1)
dateBegOfThisYear: _date(format: $__DATE_ISO8601, timestamp: $__timeBegOfThisYear)

commentsAddedInLast24Hs: commentCount(filter: { dateQuery: { after: $__dateYesterday } } )
@export(as: "commentsAddedInLast24Hs")
commentsAddedInLast1Year: commentCount(filter: { dateQuery: { after: $__date1YearAgo } } )
@export(as: "commentsAddedInLast1Year")
commentsAddedSinceBegOfThisMonth: commentCount(filter: { dateQuery: { after: $__dateBegOfThisMonth } } )
@export(as: "commentsAddedSinceBegOfThisMonth")
commentsAddedSinceBegOfThisYear: commentCount(filter: { dateQuery: { after: $__dateBegOfThisYear } } )
@export(as: "commentsAddedSinceBegOfThisYear")
}

query CreateEmailMessage @depends(on: "CountComments") {
emailMessageTemplate: _strConvertMarkdownToHTML(
text: """

This is the number of comments added to the site:

| Period | # Comments added |
| --- | --- |
| **In the last 24 hs**: | {$commentsAddedInLast24Hs} |
| **In the last 365 days**: | {$commentsAddedInLast1Year} |
| **Since beggining of this month**: | {$commentsAddedSinceBegOfThisMonth} |
| **Since beggining of this year**: | {$commentsAddedSinceBegOfThisYear} |

"""

)
emailMessage: _strReplaceMultiple(
search: [
"{$commentsAddedInLast24Hs}",
"{$commentsAddedInLast1Year}",
"{$commentsAddedSinceBegOfThisMonth}",
"{$commentsAddedSinceBegOfThisYear}"
],
replaceWith: [
$commentsAddedInLast24Hs,
$commentsAddedInLast1Year,
$commentsAddedSinceBegOfThisMonth,
$commentsAddedSinceBegOfThisYear
],
in: $__emailMessageTemplate
)
@export(as: "emailMessage")
}

mutation SendDailyStatsByEmailNumberOfComments(
$to: [String!]!
)
@depends(on: "CreateEmailMessage")
{
_sendEmail(
input: {
to: $to
subject: "Daily stats: Number of new comments"
messageAs: {
html: $emailMessage
}
}
) {
status
}
}

Scheduling the execution of the GraphQL query via WP-Cron permalink

We must schedule the WP-Cron event to execute the Gato GraphQL hook gatographql__execute_persisted_query, passing along the email to send the email to as argument, and the recurrence (daily).

We do this either via PHP:

wp_schedule_event(
time(),
'daily',
'gatographql__execute_persisted_query',
[
'daily-stats-by-email-number-of-comments',
[
'to' => ['admin@mysite.com']
],
'SendDailyStatsByEmailNumberOfComments',
1 // This is the admin user's ID
]
);

Or via the WP-Crontrol plugin:

  • Hook name: gatographql__execute_persisted_query
  • Arguments: ["daily-stats-by-email-number-of-comments",{"to":["admin@mysite.com"]},"SendDailyStatsByEmailNumberOfComments",1]
  • Recurrence: Once Daily

New entry in WP-Crontrol
New entry in WP-Crontrol

🔥 Tips:

The 4th argument passed to the WP-Cron event is the ID (as an int) or username (as a string) of the user that must be logged-in when executing the GraphQL query.

(In this case, value 1 is the ID of the admin user, and could have provided username "admin" too.)

Passing this argument is typically needed when executing mutations, as most of these require a user (with the proper capabilities) to be logged-in.

Extensions referenced in this tutorial permalink

  1. Automation permalink

    Use GraphQL to automate tasks in your app: Execute queries when some event happens, chain queries, and schedule and trigger queries via WP-Cron.

  2. Email Sender permalink

    Send emails via global mutation "_sendEmail".

  3. Field Response Removal permalink

    Remove the output of a field from the response.

  4. Field To Input permalink

    Retrieve the value of a field, manipulate it, and input it into another field or directive, all within the same operation.

  5. Helper Function Collection permalink

    Collection of fields providing useful functionality.

  6. Internal GraphQL Server permalink

    Execute GraphQL queries directly within your application, using PHP code.

  7. Multiple Query Execution permalink

    Combine multiple queries into a single query, sharing state across them and executing them in the requested order.

  8. PHP Functions via Schema permalink

    Manipulate the field output using standard programming language functions available in PHP.