$query
Updated on Aug 29, 2025 9 minutes to readThe Query Plugin provides methods to create query objects, raw SQL commands, and expressions. It also provides constants for sorting directions.
Properties
| Property | Description |
|---|---|
| SORT_ASC | Sort direction: ascending. |
| SORT_DESC | Sort direction: descending. |
Methods
| Method | Description |
|---|---|
| create | Creates a new Query object. |
| expr | Creates a new Expression object. |
| raw | Creates a new Command object representing raw SQL. |
Methods Details
create()
• Type
() => Query• Details
Creates a new Query object.
• Example
// Select a single record of the current user by ID
const user = E8App.$query.create()
.select('*')
.from('stbUsers')
.where({ 'stbUsers.id': E8App.$currentUser.getId() })
.one();
// user → single user object
// Select username and email of all users except the current one (alias mapping)
const users = E8App.$query.create()
.select([
{ username: 'stbUsers.username' }, // Alias: return as "username"
{ email: 'stbUsers.email' }, // Alias: return as "email"
])
.from('stbUsers')
.where(['not', { 'stbUsers.id': E8App.$currentUser.getId() }])
.all();
// users → array of user objects
// Select username and email without aliases (keys match column names)
const usersSimple = E8App.$query.create()
.select(['username', 'email'])
.from('stbUsers')
.where(['not', { 'stbUsers.id': E8App.$currentUser.getId() }])
.all();
// usersSimple → array of user objects with username and email
// Select email status and count, only show statuses with more than 5 emails
const emailStats = E8App.$query.create()
.select([
{ status: 'stbEmails.status' },
// Use COUNT(id) to count the number of rows for each status
{ totalEmails: E8App.$query.expr('COUNT(id)') }
])
.from('stbEmails')
// Group results by email status
.groupBy('status')
// Filter groups: only keep those with more than 5 emails
.having('totalEmails > 5')
.all();
// emailStats → array of objects { status, totalEmails }
// Select distinct email addresses from "to" field in emails
const distinctEmailsAdresses = E8App.$query.create()
.select({ 'to': 'stbEmails.to' })
.from('stbEmails')
.distinct() // ensures unique values
.all();
// distinctEmailsAdresses → array of unique { to }
// Select username of creator and count how many audit logs they created
const userLogs = E8App.$query.create()
.select([
// Get username from related stbUsers table via createdBy relation
{ username: 'stbAuditLog.createdBy.username' },
// Count number of logs per user
{ totalLogs: E8App.$query.expr('COUNT(stbAuditLog.id)') }
])
.from('stbAuditLog')
// Group results by username
.groupBy('username')
.all();
// userLogs → array of { username, totalLogs }
// Base query to select ownerId and ownerScriptAlias from stbAuditLog
const queryAuditLog = E8App.$query.create()
.select([{
ownerId: 'stbAuditLog.ownerId',
ownerScriptAlias: 'stbAuditLog.ownerScriptAlias',
}])
.from('stbAuditLog');
// Base query to select ownerId and ownerScriptAlias from stbEmails
const queryEmails = E8App.$query.create()
.select([{
ownerId: 'stbEmails.ownerId',
ownerScriptAlias: 'stbEmails.ownerScriptAlias',
}])
.from('stbEmails');
// Combine audit log and emails queries using UNION ALL, then apply DISTINCT
const records = queryAuditLog
.unionAll(queryEmails) // merge two queries (keeps duplicates)
.distinct() // remove duplicates after union
.all();
// records → array of unique { ownerId, ownerScriptAlias }expr()
• Type
(expression: string, params?: object) => Expression• Details
Expects a string representing an expression and optional parameters object.
Returns a new Expression object.
• Example
// Select username, email, and a computed field "isCurrent" (boolean)
const users = E8App.$query.create()
.select([
{ username: 'stbUsers.username' },
{ email: 'stbUsers.email' },
{ isCurrent: E8App.$query.expr('IF(stbUsers.id = {{ id }}, TRUE, FALSE)', { id: E8App.$currentUser.getId() }) }
])
.from('stbUsers')
.all()
// users → array of user objects with computed "isCurrent"raw()
• Type
(sql: string, params?: object) => Command• Details
Expects a raw SQL string and optional parameters object.
Returns a Command object for executing raw SQL.
• Example
// Run a raw SQL query with parameter substitution
const user = E8App.$query.raw(
`
SELECT *
FROM stbUsers
WHERE stbUsers.id = {{ id }}
`,
{ id: E8App.$currentUser.getId() }
)
.one();
// user → single user object from raw SQL
// Select email status and count, only show statuses with more than 5 emails
const emailStats = E8App.$query.raw(
`
SELECT stbEmails.status, COUNT(id) as totalEmails
FROM stbEmails
GROUP BY stbEmails.status
HAVING totalEmails > 5
`,
{}
).all();
// emailStats → array of { status, totalEmails }
// Select distinct "to" addresses from stbEmails
const distinctEmailsAdresses = E8App.$query.raw(
`
SELECT DISTINCT stbEmails.to
FROM stbEmails
`,
{}
).all();
// distinctEmailsAdresses → array of unique { to }
// Select username and number of audit logs created by that user
const userLogs = E8App.$query.raw(
`
SELECT u.username, COUNT(a.id) as totalLogs
FROM stbAuditLog AS a
JOIN stbUsers AS u ON a.createdBy = u.id
GROUP BY u.username
`,
{}
).all();
// userLogs → array of { username, totalLogs }
// Select all unique ownerId + ownerScriptAlias from audit logs and emails
const records = E8App.$query.raw(
`
SELECT ownerId, ownerScriptAlias
FROM stbAuditLog
UNION
SELECT ownerId, ownerScriptAlias
FROM stbEmails
`,
{}
).all();
// records → array of unique { ownerId, ownerScriptAlias }