Skip to content

JSQL select

Jump to bottom Edit New page
Brian edited this page May 23, 2016 · 1 revision 
 select([options])

JSQL query used to select records from the database.

Table of Contents

Parameters

options (optional)

See options for additional information.

Options for modifying the behavior of the update query.

Property Default Explanation
selectAsObjects true If true, the return value of the execute function will be an array of objects, with object keys as field names. Otherwise, the return value will be an array of arrays, with elements being in order based on the table's fields (when selecting all fields) or the specified fields.

Returns

The select function returns a Select object, which is a subclass of cls.QueryBuilder. This object has the following functions you can use to build your query:

field(name[, alias])

Specifies a field to select.

Parameter Type Optional Remarks
name String No You may qualify the field name with a table name, or a table alias if one will be provided in the query. You may also qualify the table & field with a schema name, and provide a field alias. If you are selecting from multiple tables, you must qualify the field with a table name or alias in the case of two fields with the same name. If selectAsObjects is true, you should supply an alias for fields with the same name.
alias String Yes You may give an alias to the field as a separate parameter. If you supply an alias parameter and specify an alias in the field name, the alias parameter will not be used.

fields(fields)

Specify multiple fields to select.

Parameter Type Optional Remarks
fields String No A comma-separated list of fields to select. Each field is in the same format as name in field(name[, alias]).

fields(...fields)

Specify multiple fields to select.

Parameter Type Optional Remarks
fields Array of String No A list of fields to select. Each field is in the same format as name in field(name[, alias]).

from(table[, alias])

Set the table(s) to select from. If from is called multiple times for a single select query, subsequent calls will create implicit inner joins.

Parameter Type Optional Remarks
table String No You may qualify the table name with a schema name. You may also provide a table alias.
alias String Yes You may give an alias to the table as a separate parameter. If you supply an alias parameter and specify an alias in the table name, the alias parameter will not be used.

sub(qb)

Creates a subquery to select from, as opposed to selecting directly from other tables in the database. Each subquery call is equivalent to calling from, specifying a table matching the contents of the subquery return result.

Parameter Type Optional Remarks
qb cls.Select No Do not call execute on the select being passed to this function. For example, good: 
 jsql.select('my_table')
     .sub(jsql.select('your_table')
              .where(ex))
     .execute()
Bad: 
 jsql.select('my_table')
     .sub(jsql.select('your_table')
              .where(ex)
              .execute())
     .execute()

join(table, joinType)

Joins another table to the one being selected from.

Parameter Type Optional Remarks
table String No The table to join. May be qualified with a schema, and may specify an alias. It is a very good idea to specify an alias when joining tables.
joinType String or cls.JoinBlock.Type No Specify the type of join to use. The join types and their string equivalents are:
  • cls.JoinBlock.Type.Inner/"inner": Limit the result to the rows where there is a match in both tables
  • cls.JoinBlock.Type.Left/"left": Limit the result to the rows where there is a match in the left (first from or sub) table. Will often produce null values for fields from the joined table.
  • cls.JoinBlock.Type.Right/"right": Limit the result to the rows where there is a match in the right (joined) table. Will often produce null values for fields from the left table.
  • cls.JoinBlock.Type.Full/"full": Includes all rows from both tables. Will often produce null values for rows which don't match (those which would not be in an Inner join).
  • cls.JoinBlock.Type.Cross/"cross": Pairs each row in the left table with each row in the right table, potentially producing an extremely large number of rows.

leftJoin(table)

Equivalent to join(table, 'left')

rightJoin(table)

Equivalent to join(table, 'right')

innerJoin(table)

Equivalent to join(table, 'inner')

fullJoin(table)

Equivalent to join(table, 'full')

crossJoin(table)

Equivalent to join(table, 'cross')

on(expr)

Provides constraints to the last call to join. Some join variant must be called before on, and each call to on will apply to the last call to a join variant.

Parameter Type Optional Remarks
expr varies No If expr is an Expression, it will be added as a constraint to the join. If expr is a String, it will be parsed as an Expression. Otherwise, an Identity Expression will be created around the parameter.

where(...expr)

Creates a condition to limit what records are selected. See expr for more information.

Parameter Type Optional Remarks
expr varies args No At least one argument must be supplied. For each argument:
  • If it is an instance of cls.Expression, that expression will be used.
  • If it is a String, it will be parsed into an Expression
  • Otherwise, it will be the operand to an Identity Expression

groupBy(field[, desc])

Used in conjunction with aggregate functions to group the result set when performing aggregate operations. You can group a query by multiple fields.

Parameter Type Optional Remarks
field String No The field to group by.
desc Boolean Yes true if you want to sort the keys being grouped descending, instead of the default ascending order (false)

orderBy(field[, descending])

Add a sort to the result set.

Parameter Type Optional Remarks
field String No Must be non-empty
descending Boolean Yes true if the result set should be sorted in descending order (4, 3, 2, 1) instead of the default ascending order (1, 2, 3, 4)

limit(limit)

Limit the number of results in the result set. A result set of [1, 2, 3, 4, 5] with a limit of 2 will select [1, 2].

Parameter Type Optional Remarks
limit Number or cls.Expression No If this function is called multiple times, that last call will overwrite the previous ones

offset(offset)

Skip some of the first results in the result set. A result set of [1, 2, 3, 4, 5] with offset 2 will select [3, 4, 5].

Parameter Type Optional Remarks
offset Number or cls.Expression No If this function is called multiple times, that last call will overwrite the previous ones

execute([options])

Performs the select operation.

options.selectAsObjects can override the same option specified in select and cls.DefaultOptions.

This function will return an array of records. Each record will either be an object (if selectAsObjects is true) with field names (or aliases) as keys, or else an array.

Add a custom footer

JSQL

Clone this wiki locally