Yes, the best way to add this in would be to add a section to the WHERE statement in the query, filtering by topic title.
For example:
WHERE archetype = 'private_message'
AND subtype = 'system_message'
AND created_at BETWEEN :start_date AND :end_date
AND title = 'Greetings!'
Would find all of the Greetings! messages.
Note that some of the system messages do not include a real user, so removing the line AND user_id > 0 may be necessary with this type of query.
You may also want to remove AND deleted_at IS NULL to still count welcome messages that users might delete.
You could use regex to match topics with a similar title.
To match a topic by title using a regular expression (regex) in PostgreSQL, you can use the ~ operator, which matches a regular expression against a string. The query structure would look like this:
SELECT *
FROM topics
WHERE title ~ 'YourRegexPatternHere'
Replace 'YourRegexPatternHere' with the actual regex pattern you want to match against the title field.
For example, if you’re looking for topics with titles that contain the word “Welcome” (case-insensitive), you could use:
SELECT *
FROM topics
WHERE title ~* 'Welcome'
The ~* operator is used for case-insensitive matching.