table.select()

Programmatically perform an SELECT query.

Syntax

table.select(
    fields?: Field[],
    modifiers?: QueryOptions | Limit | Callback,
): Promise<QueryResult>;

table.select(
    arg?: {
        fields?: Field[];
    } & QueryOptions | Limit | Callback
): Promise<QueryResult>;

type Field = 
    | string 
    | { expr: any, as?: string } 
    | Function;

interface QueryOptions {
    where?: any;
    limit?: Limit;
}

type Limit = number;
type Callback = function;

type QueryResult = Array<object> | object;

Param	Description
fields?	Fields list.
modifiers?	Query modifiers.

└ QueryOptions

Param	Description
where	The where clause.
limit	The limit clause.

Return Value

• A Savepoint instance (See ➞ Savepoint) or the boolean true when savepoint creation has been disabled via options.noCreateSavepoint; (Compare ➞ Query Return Value)

Usage

Call patterns

Two-parameter call pattern:

/**
 * SELECT first_name, last_name, email
 * LIMIT 4
 */
const result = await table.select(['first_name', 'last_name', 'email'], 4);

Single-parameter call pattern:

/**
 * SELECT first_name, last_name, email
 */
const result = await table.select(['first_name', 'last_name', 'email']);

/**
 * SELECT *
 * LIMIT 4
 */const result = await table.select(4);

/**
 * SELECT first_name, last_name, email
 * LIMIT 4
 */
const result = await table.select({
    fields: ['first_name', 'last_name', 'email'],
    limit: 4
});

Zero-parameter call pattern:

/**
 * SELECT *
 */
const result = await table.select();

The where clause

Find by primary key (of an arbitrary name):

/**
 * SELECT *
 * WHERE automatically_figured_primary_key_name = 4
 */
const result = await table.select({ where: 1 });
console.log(result); // single result object

Find by expression:

/**
 * SELECT *
 * WHERE first_name = 'John' AND last_name = 'Doe'
 */
const result = await table.select({
    where: [
        { eq: ['first_name', { value: 'John' }] },
        { eq: ['last_name', { value: 'Doe' }] }
    ]
});

/**
 * SELECT name, email
 * WHERE (role = $1 OR role = $2) AND (
 *   email IS NOT NULL OR (
 *     phone IS NOT NULL AND country_code IS NOT NULL
 *   )
 * )
 */
const result = await table.select(
    [ 'name', 'email' ],
    { where: [
        { some: [
            { eq: ['role', { binding: 'admin' }] },
            { eq: ['role', { binding: 'contributor' }] }
        ] },
        { some: [
            { isNotNull: 'email' },
            { every: [
                { isNotNull: 'phone' },
                { isNotNull: 'country_code' }
            ] }
        ] }
    ] }
);

/**
 * SELECT name, email
 * WHERE role IS NOT NULL AND COALESCE(email, phone) IS NOT NULL)
 */
const result = await table.select(
    [ 'name', 'email' ],
    { where: [
        { isNotNull: 'role' },
        { isNotNull: { fn: ['COALESCE', 'email', 'phone'] } }
    ] }
);

The orderBy clause

/**
 * SELECT name, email
 * ORDER BY
 *   CASE role WHEN 'admin' THEN 1 WHEN 'contributor' THEN 2 ELSE 3 END ASC,
 *   CASE WHEN phone IS NULL THEN 0 ELSE 1 END DESC,
 *   name ASC
 */
const result = await table.select(
    [ 'name', 'email' ],
    { orderBy: [
        { expr: {
            switch: 'role',
            cases: [
                { when: { value: 'admin' }, then: 1 },
                { when: { value: 'contributor' }, then: 2 }
            ],
            default: 0
        }, asc: true },
        { expr: {
            cases: [ { when: { isNull: 'phone' }, then: 0 } ],
            default: 1
        }, desc: true },
        { expr: 'name', asc: true }
    ] }
);

Using paths

/**
 * SELECT title, content, author ~> name AS author_name
 * WHERE author ~> role = $1
 */
const result = await table.select(
    { fields: [
        { expr: 'title' },
        { expr: 'content' },
        { expr: {
            path: ['author', '~>', 'name']
        }, as: 'author_name' }
    ], where: [
        { eq: [
            { path: ['author', '~>', 'role'] },
            { binding: ['admin'] }
        ] }
    ] }
);

/**
 * SELECT title, content, author: { name, email } AS author
 * WHERE author ~> role = $1
 */
const result = await table.select(
    { fields: [
        { expr: 'title' },
        { expr: 'content' },
        { expr: {
            path: ['author', '~>', { jsonObject: ['name', 'email'] }]
        }, as: 'author' }
    ], where: [
        { eq: [
            { path: ['author', '~>', 'role'] },
            { binding: ['admin'] }
        ] }
    ] }
);