In DPQL, you can use the LIKE operator in the WHERE clause to check for patterns.
You would use it alongside wildcards:
%
represents any number of characters
_
represents one character
Examples:
1. Tickets from a single email domain
An example of this you could use would be if you wanted to look at all tickets from users under a specific email domain.
The query below wouldn't work as the email address is incomplete:
SELECT Tickets.id, tickets.person.emails.email
FROM tickets
WHERE tickets.person.emails.email = 'deskpro.com'
2. However, if rather than = we use Like and the % wildcard, we can pull all emails that end in deskpro.com
SELECT Tickets.id, tickets.person.emails.email
FROM tickets
WHERE tickets.person.emails.email LIKE '%deskpro.com'
3. Tickets from similar email domains
Similarly, if we wanted to pull all tickets submitted from Deskpro.com and Deskpro.co.uk, we could use the following as the second % would bypass the characters specified after Deskpro:
SELECT Tickets.id, tickets.person.emails.email
FROM tickets
WHERE tickets.person.emails.email LIKE '%deskpro%'
Wildcard Variations
Different wildcard variations that return support@deskpro.com:
WHERE | LIKE | Description |
WHERE person.emails.email | LIKE 'Support%' | Any values that begin with support |
WHERE person.emails.email | LIKE '%Deskpro.com' | Any values that end with deskpro.com |
WHERE person.emails.email | LIKE '%Deskpro%' | Any values that contain Deskpro |
WHERE person.emails.email | LIKE 's%m' | Any value that starts with S and ends with M |
WHERE person.emails.email | LIKE '_u%' | Any value that has a U in the second position |