$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
                    🔽 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.
• 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