Select - Documentation

Constructor

new Select(dbopt)

Select constructor

Source:

Select/Select.js, line 13

Parameters:

Name	Type	Attributes	Default	Description
`db`	`Db`	<optional>	null	The Db instance to use

Members

(private) _bound

Values to bind by name to the query before executing

Properties:

Type	Description
`Object`

Source:

Select/Select.js, line 239

(private) _children

The list of child relationship definitions

Properties:

Type	Description
`Array.<Object>`

Source:

Select/Select.js, line 166

(private) _columns

The list of column names to select

Properties:

Type	Description
`Array.<String>`

Source:

Select/Select.js, line 179

(private) _groupBys

The list of GROUP BY clauses

Properties:

Type	Description
`Array.<String>`

Source:

Select/Select.js, line 209

(private) _havings

The list of HAVING clauses

Properties:

Type	Description
`Array.<String>`

Source:

Select/Select.js, line 203

(private) _joins

The list of JOIN strings to add

Properties:

Type	Description
`Array.<String>`

Source:

Select/Select.js, line 191

(private) _limit

The LIMIT to use

Properties:

Type	Description
`Number`

Source:

Select/Select.js, line 221

(private) _offset

The OFFSET to use

Properties:

Type	Description
`Number`

Source:

Select/Select.js, line 227

(private) _options

The list of strings to come immediately after "SELECT" and before column names

Properties:

Type	Description
`Array.<String>`

Source:

Select/Select.js, line 173

(private) _orderBys

The list of ORDER BY clauses

Properties:

Type	Description
`Array.<String>`

Source:

Select/Select.js, line 215

(private) _page

The page used to construct an OFFSET based on the LIMIT

Properties:

Type	Description
`Number`

Source:

Select/Select.js, line 233

(private) _siblings

The list of sibling relationship definitions

Properties:

Type	Description
`Array.<Object>`

Source:

Select/Select.js, line 160

(private) _tables

The list of tables in the FROM clause

Properties:

Type	Description
`Array.<String>`

Source:

Select/Select.js, line 185

(private) _wheres

The list of WHERE clauses

Properties:

Type	Description
`Array.<String>`

Source:

Select/Select.js, line 197

Methods

(private, static) _extractBindingName(sql) → {*|string}

Extract the name of the first bound variable E.g. given "SELECT * FROM users WHERE id IN(:id)" it would return "id"

Source:

Select/Select.js, line 480

Parameters:

Name	Type	Description
`sql`	`String`

Returns:

Type:: * | string

(static) init(dbopt) → {Select}

Shortcut to initialize without the new keyword

Source:

Select/Select.js, line 50

Parameters:

Name	Type	Attributes	Default	Description
`db`	`Db`	<optional>	null	The Db instance to use

Returns:

Type:: Select

(static) parse(sql, dbopt) → {Select}

Return a new Select object that matches the given SQL

Source:

Select/Select.js, line 32

Parameters:

Name	Type	Attributes	Default	Description
`sql`	`String`			The SQL to parse
`db`	`Db`	<optional>	null	The Db instance to use for queries

Returns:

Type:: Select

_conditions(collection, criteria) → {Select}

Utility function to add conditions for a clause (WHERE, HAVING)

Properties:

Name	Type	Attributes	Description
`criteria[0`	`*`		The expression or name of the column on which to match
`criteria[1]`	`*`	<optional>	The comparison operator; defaults to "="
`criteria[2]`	`*`	<optional>	The value to test against

Source:

Select/Select.js, line 756

Parameters:

Name	Type	Description
`collection`	`Array`	The collection to add the clauses to (e.g. this._wheres or this._havings)
`criteria`	`Array`	A list of expressions to stringify

Returns:

Type:: Select

Examples

The following are equivalent
    this._conditions(this._wheres, ['deleted_at IS NULL']);
    this._conditions(this._wheres, ['deleted_at', null]);
    this._conditions(this._wheres, ['deleted_at', '=', null]);

More examples
    this._conditions(this._wheres, ['fname', 'LIKE', 'joe']); // `fname` LIKE 'joe'
    this._conditions(this._wheres, ['fname', 'LIKE ?', 'joe']); // `fname` LIKE 'joe'
    this._conditions(this._wheres, ['fname LIKE %?%', 'joe']); // `fname` LIKE '%joe%'
    this._conditions(this._wheres, ['fname LIKE ?%', 'joe']); // `fname` LIKE 'joe%'
    this._conditions(this._wheres, ['fname', 'LIKE ?%', 'joe']); // `fname` LIKE 'joe%'
    this._conditions(this._wheres, ['price >', 10]); // `price` > 10
    this._conditions(this._wheres, ['price', '>', 10]); // `price` > 10
    this._conditions(this._wheres, ['price =', 10]); // `price` = 10
    this._conditions(this._wheres, ['price !=', 10]); // `price` != 10
    this._conditions(this._wheres, ['price', 10]); // `price` = 10
    this._conditions(this._wheres, ['price', '=', 10]); // `price` = 10
    this._conditions(this._wheres, ['price', '!=', 10]); // `price` != 10
    this._conditions(this._wheres, ['price', 'BETWEEN', [10,20]]); // `price` BETWEEN 10 AND 20
    this._conditions(this._wheres, ['price', 'NOT BETWEEN', [10,20]]); // `price` NOT BETWEEN 10 AND 20
    this._conditions(this._wheres, ['price', [10,20]]); // `price` IN(10,20)
    this._conditions(this._wheres, ['price', '=', [10,20]]); // `price` IN(10,20)
    this._conditions(this._wheres, ['price', 'IN', [10,20]]); // `price` IN(10,20)
    this._conditions(this._wheres, ['price', 'NOT IN', [10,20]]); // `price` NOT IN(10,20)

(private) _isEntirelyDigits(string) → {Boolean}

Check to see if the given string is 0 or all digits not starting with 0

Source:

Select/Select.js, line 995

Parameters:

Name	Type	Description
`string`	`String`	The input string

Returns:

Type:: Boolean

(private) _isEntirelyDigitsNoZeros(string) → {Boolean}

Check to see if the given string is all digits

Source:

Select/Select.js, line 1005

Parameters:

Name	Type	Description
`string`	`String`	The input string

Returns:

Type:: Boolean

(private) _isPlaceholder(string) → {Boolean}

Check to see if the given string is ? or a bound variable like :var

Source:

Select/Select.js, line 985

Parameters:

Name	Type	Description
`string`	`String`	The input string

Returns:

Type:: Boolean

(async) _spliceChildData(queries)

Fetch child data and splice it into the given result set

Source:

Select/Select.js, line 520

Parameters:

Name	Type	Description
`queries`	`Array`	The final SQL statements that were executed

(async) _spliceSiblingData(queries)

Fetch sibling data and splice it into the given result set

Source:

Select/Select.js, line 492

Parameters:

Name	Type	Description
`queries`	`Array`	The final SQL statements that were executed

bind(placeholder, valueopt) → {Select}

Bind values by name to the query

Source:

Select/Select.js, line 278

Parameters:

Name	Type	Attributes	Default	Description
`placeholder`	`Object` \| `String` \| `Array`			The name of the placeholder or an object with placeholder: value pairs
`value`	`*`	<optional>	null	The value to bind when placeholder is a string

Returns:

Type:: Select

Example

query.bind('postId', 123); // replace :postId with 123
    query.bind({ postId: 123 }); // replace :postId with 123

column(columnName) → {Select}

Add a column name to fetch

Source:

Select/Select.js, line 562

Parameters:

Name	Type	Description
`columnName`	`String`	The name of the column

Returns:

Type:: Select

columns(columnNames) → {Select}

Add an array of column names to fetch

Source:

Select/Select.js, line 552

Parameters:

Name	Type	Description
`columnNames`	`Array.<String>`	The names of columns

Returns:

Type:: Select

crossJoin(expression) → {Select}

Add a CROSS JOIN expression

Source:

Select/Select.js, line 657

Parameters:

Name	Type	Description
`expression`	`String`	The expression following the CROSS JOIN keyword

Returns:

Type:: Select

Example

query.join('posts p ON p.id = c.post_id');

escape(value) → {string}

Manually escape a value

Source:

Select/Select.js, line 1082

Parameters:

Name	Type	Description
`value`	`*`	The value to escape

Returns:

Type:: string

escapeLike(infix, value) → {String}

Get the proper escaping for a LIKE or NOT LIKE clause

Source:

Select/Select.js, line 1101

Parameters:

Name	Type	Description
`infix`	`String`	One of ?% or %?% or %? or ?
`value`	`String`	The value to search for

Returns:

Type:: String

escapeQuoteless(value) → {string}

Manually escape a value without quotes

Source:

Select/Select.js, line 1091

Parameters:

Name	Type	Description
`value`	`*`	The value to escape without quotes

Returns:

Type:: string

(async) fetch(optionsopt) → {Promise.<Object>}

Fetch records and splice in related data

Source:

Select/Select.js, line 308

Parameters:

Name	Type	Attributes	Description
`options`		<optional>	Query options

Returns:

Type:: Promise.<Object>

(async) fetchFirst() → {Object|null}

Fetch the first matched record

Source:

Select/Select.js, line 325

Returns:

Type:: Object | null

(async) fetchGrouped(byField) → {Array}

Fetch values grouped by the given field name

Source:

Select/Select.js, line 383

Parameters:

Name	Type	Description
`byField`	`String`	The field by which to group

Returns:

Type:: Array

Example

const query = Select.parse('SELECT * FROM comments');
     const byUser = query.fetchGrouped('user_id')
     // a key for each user id with an array of comments for each key

fetchHash() → {Promise.<Object>}

Fetch each record as an object with key-value pairs

Source:

Select/Select.js, line 338

Returns:

Type:: Promise.<Object>

(async) fetchIndexed(byField) → {Promise.<Object>}

Fetch values and index by the given field name

Source:

Select/Select.js, line 366

Parameters:

Name	Type	Description
`byField`	`String`	The field by which to index (e.g. id)

Returns:

Type:: Promise.<Object>

fetchList() → {Promise.<Object>}

Fetch each record as an array of values

Source:

Select/Select.js, line 347

Returns:

Type:: Promise.<Object>

fetchValue() → {Promise}

Fetch the value of first column of the first record

Source:

Select/Select.js, line 356

Returns:

Type:: Promise

foundRows(countExpropt) → {Promise.<Number>}

Run a version of this query that simply returns COUNT(*)

Source:

Select/Select.js, line 468

Parameters:

Name	Type	Attributes	Default	Description
`countExpr`	`String`	<optional>	"*"	Use to specify `DISTINCT colname` if needed

Returns:

Type:: Promise.<Number>

The number of rows or false on error

from(tableName) → {Select}

Add a table to the "FROM" clause (same as .table())

Source:

Select/Select.js, line 602

Parameters:

Name	Type	Description
`tableName`	`String`	The name of the table to query

Returns:

Type:: Select

fullJoin(expression) → {Select}

Add a FULL JOIN expression

Source:

Select/Select.js, line 635

Parameters:

Name	Type	Description
`expression`	`String`	The expression following the FULL JOIN keyword

Returns:

Type:: Select

Example

query.fullJoin('posts p ON p.id = c.post_id');

fullOuterJoin(expression) → {Select}

Add a FULL OUTER JOIN expression

Source:

Select/Select.js, line 690

Parameters:

Name	Type	Description
`expression`	`String`	The expression following the FULL OUTER JOIN keyword

Returns:

Type:: Select

Example

query.fullOuterJoin('posts p ON p.id = c.post_id');

getClone() → {Select}

Clone this object

Source:

Select/Select.js, line 400

Returns:

Type:: Select

getFoundRowsQuery(countExpropt) → {Select}

Build a version of this query that simply returns COUNT(*)

Source:

Select/Select.js, line 424

Parameters:

Name	Type	Attributes	Default	Description
`countExpr`	`String`	<optional>	"*"	Use to specify `DISTINCT colname` if needed

Returns:

Type:: Select

The SQL query

getFoundRowsSql(countExpr, normalize) → {String}

Get SQL needed to return the found rows of this query

Source:

Select/Select.js, line 450

Parameters:

Name	Type	Default	Description
`countExpr`	`String`	*	The expression to use inside the COUNT()
`normalize`	`Boolean`	false	If true, return a normalized sql

Returns:

Type:: String

groupBy(column) → {Select}

Add a group by column or expression

Source:

Select/Select.js, line 863

Parameters:

Name	Type	Description
`column`	`String`	The name of a column (or expression) to group by

Returns:

Type:: Select

having(column, operatoropt, valueopt) → {Select}

Add a HAVING condition (See _conditions for usage)

Source:

Select/Select.js, line 932

Parameters:

Name	Type	Attributes	Description
`column`	`String`		The expression or name of the column on which to match
`operator`	`*`	<optional>	The comparison operator; defaults to "="
`value`	`*`	<optional>	The value to test against

Returns:

Type:: Select

innerJoin(expression) → {Select}

Add an INNER JOIN expression (same as ->join())

Source:

Select/Select.js, line 668

Parameters:

Name	Type	Description
`expression`	`String`	The expression following the INNER JOIN keyword

Returns:

Type:: Select

Example

query.innerJoin('posts p ON p.id = c.post_id');

join(expression) → {Select}

Add an INNER JOIN expression (same as .innerJoin())

Source:

Select/Select.js, line 613

Parameters:

Name	Type	Description
`expression`	`String`	The expression following the INNER JOIN keyword

Returns:

Type:: Select

Example

query.join('posts p ON p.id = c.post_id');

leftJoin(expression) → {Select}

Add a LEFT JOIN expression

Source:

Select/Select.js, line 624

Parameters:

Name	Type	Description
`expression`	`String`	The expression following the LEFT JOIN keyword

Returns:

Type:: Select

Example

query.leftJoin('posts p ON p.id = c.post_id');

leftOuterJoin(expression) → {Select}

Add a LEFT OUTER JOIN expression

Source:

Select/Select.js, line 679

Parameters:

Name	Type	Description
`expression`	`String`	The expression following the LEFT OUTER JOIN keyword

Returns:

Type:: Select

Example

query.leftOuterJoin('posts p ON p.id = c.post_id');

limit(max) → {Select}

Limit results to the given number

Source:

Select/Select.js, line 1014

Parameters:

Name	Type	Description
`max`	`Number` \| `String`	The number to limit by (placeholder string or integer greater than 0)

Returns:

Type:: Select

normalized() → {String}

Get the SQL as a one-line string

Source:

Select/Select.js, line 90

Returns:

Type:: String

offset(number) → {Select}

Fetch results from the given offset

Source:

Select/Select.js, line 1035

Parameters:

Name	Type	Description
`number`	`Number` \| `String`	The offset (placeholder string or integer greater than or equal to 0)

Returns:

Type:: Select

option(optionExpression) → {Select}

Add an option expression such as "TOP 10" or "SQL_CALC_FOUND_ROWS"

Source:

Select/Select.js, line 572

Parameters:

Name	Type	Description
`optionExpression`	`String`	Expression to go after "SELECT" and before column list

Returns:

Type:: Select

orderBy(column) → {Select}

Add a column or expression to order by

Source:

Select/Select.js, line 957

Parameters:

Name	Type	Description
`column`	`String`	The column name or expression to sort by. Include DESC or prefix with - to sort descending

Returns:

Type:: Select

orHaving(conditions) → {Select}

Add an OR with conditions under the HAVING clause

Source:

Select/Select.js, line 942

Parameters:

Name	Type	Description
`conditions`	`Array.<Array>`

Returns:

Type:: Select

orWhere(conditions) → {Select}

Add WHERE conditions to place inside an OR block (See _conditions for usage)

Source:

Select/Select.js, line 909

Parameters:

Name	Type	Description
`conditions`	`Array`	A list where each item is an array with parameters that would be taken by where()

Returns:

Type:: Select

page(number) → {Select}

Set the offset based on the limit with the given page number

Source:

Select/Select.js, line 1060

Parameters:

Name	Type	Description
`number`	`Number` \| `String`	The page number (integer greater than 0)

Returns:

Type:: Select

parse(sql) → {Select}

Load the given SQL into this object

Source:

Select/Select.js, line 19

Parameters:

Name	Type	Description
`sql`	`String`	The SQL to parse

Returns:

Type:: Select

reset(fieldopt) → {Select}

Source:

Select/Select.js, line 134

Parameters:

Name	Type	Attributes	Default	Description
`field`	`String` \| `Array`	<optional>	null	If given, reset the given component(s), otherwise reset all query components Valid components: option, column, table, where, orWhere, having, groupBy, orderBy, limit, offset, page

Returns:

Type:: Select

rightJoin(expression) → {Select}

Add a RIGHT JOIN expression

Source:

Select/Select.js, line 646

Parameters:

Name	Type	Description
`expression`	`String`	The expression following the RIGHT JOIN keyword

Returns:

Type:: Select

Example

query.rightJoin('posts p ON p.id = c.post_id');

rightOuterJoin(expression) → {Select}

Add a RIGHT OUTER JOIN expression

Source:

Select/Select.js, line 701

Parameters:

Name	Type	Description
`expression`	`String`	The expression following the RIGHT OUTER JOIN keyword

Returns:

Type:: Select

Example

query.rightOuterJoin('posts p ON p.id = c.post_id');

sortField(column, mapNamesopt) → {Select}

Sort by the given column, with a map of columns to translate

Source:

Select/Select.js, line 971

Parameters:

Name	Type	Attributes	Default	Description
`column`	`String`			The column name such as "created_at" or "-created_at" for descending
`mapNames`	`Object`	<optional>	{}	Column names to translate from one name to another

Returns:

Type:: Select

Example

query.sortField('-modified_at'); // ORDER BY modified_at DESC
    query.sortField('created_at', ['created_at'=>'created']); // ORDER BY created

table(tableName) → {Select}

Add a table to the "FROM" clause (same as .from())

Source:

Select/Select.js, line 582

Parameters:

Name	Type	Description
`tableName`	`String`	The name of the table to query

Returns:

Type:: Select

tables(tableNames) → {Select}

Add multiple table to the "FROM" clause

Source:

Select/Select.js, line 592

Parameters:

Name	Type	Description
`tableNames`	`Array`	The names of the tables to query

Returns:

Type:: Select

toBoundSql() → {String}

Get normalized SQL with all parameters bound

Source:

Select/Select.js, line 123

Returns:

Type:: String

toString() → {String}

Get the SQL as a pretty-printed string

Source:

Select/Select.js, line 58

Returns:

Type:: String

unbind(placeholderopt) → {Select}

Unbind a previously bound property

Source:

Select/Select.js, line 294

Parameters:

Name	Type	Attributes	Default	Description
`placeholder`	`String`	<optional>	null

Returns:

Type:: Select

unjoin(table) → {Select}

Remove a join condition with the specified table

Source:

Select/Select.js, line 711

Parameters:

Name	Type	Description
`table`	`String` \| `Array.<String>`	The name of the table or tables in the first part of the join statement

Returns:

Type:: Select

where(column, operatoropt, valueopt) → {Select}

Add WHERE clauses to conditions (See _conditions for usage)

Source:

Select/Select.js, line 875

Parameters:

Name	Type	Attributes	Description
`column`	`String`		The expression or name of the column on which to match
`operator`	`*`	<optional>	The comparison operator; defaults to "="
`value`	`*`	<optional>	The value to test against

Returns:

Type:: Select

whereBetween(column, twoValueArray) → {Select}

Add a WHERE clause with a BETWEEN condition

Source:

Select/Select.js, line 886

Parameters:

Name	Type	Description
`column`	`String`	The column name
`twoValueArray`	`Array`	The two values to be between

Returns:

Type:: Select

withChildData(property, childQuery) → {Select}

Specify data from a child table to be spliced in Can be used for one-to-many or many-to-many relationships

Source:

Select/Select.js, line 264

Parameters:

Name	Type	Description
`property`	`String`	The name of the property into which to splice
`childQuery`	`Select`	The Select query to fetch the child data

Returns:

Type:: Select

withSiblingData(property, siblingQuery) → {Select}

Specify data from a sibling table be spliced in Can be used for one-to-one or many-to-one relationships

Source:

Select/Select.js, line 252

Parameters:

Name	Type	Description
`property`	`String`	The name of the property into which to splice
`siblingQuery`	`Select`	The Select query to fetch the sibling data

Returns:

Type:: Select