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 specified fields; limit to 4 records
const result = await table.select(['first_name', 'last_name', 'email'], 4);

Single-parameter call pattern:

// Select specified fields from all records
const result = await table.select(['first_name', 'last_name', 'email']);

// Select all fields; limit to 4 records
const result = await table.select(4);

// Select specified fields; limit to 4 records
const result = await table.select({
    fields: ['first_name', 'last_name', 'email'],
    limit: 4
});

Zero-parameter call pattern:

// Select all fields (*) from all records
const result = await table.select();

The where clause

Find by primary key:

// Select record with primary key of 1; returning single result object
const result = await table.select({ where: 1 });
console.log(result); // { ... }

Find by expression:

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

// Desc:
// 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' }
            ] }
        ] }
    ] }
);

// Desc:
// 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

// Desc:
// 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

// Desc:
//  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'] }
        ] }
    ] }
);

// Desc:
//  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'] }
        ] }
    ] }
);