$query
Updated on Aug 29, 2025 10 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
🔽 Show more
• 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 }
🔽 Show more
expr()
• Type
(expression: string, params?: object) => Expression
🔽 Show more
• 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"
🔽 Show more
raw()
• Type
(sql: string, params?: object) => Command
🔽 Show more
• Details
Expects a raw SQL string and optional parameters object.
Returns a Command object for executing raw SQL.
Parameter values can be scalar values or non-empty flat arrays of scalar values. An array is expanded into a comma-separated SQL list; use it in a list context, for example WHERE stbUsers.id IN ({{ ids }}). Empty and nested arrays are not supported.
• 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 }
🔽 Show more