Find statement

Find statement builder in Jaguar is equivalent to SQL's SELECT statement. It can be used to fetch one or multiple records from the database.

Selecting on table

The table is selected when a new Find builder is instantiated. In the example below, find statement will be executed on "people" table.

var find = Find("people");

Table alias

The alias parameter lets us pick an alias for the main table we are selecting on. This alias can be later used in column selection and where clauses in place of the original table name.

var find = Find("people", alias: 'p');

The above statement is equivalent to:

SELECT * FROM people as p;

Selecting columns

Selecting all columns

selAll method can be used to request fetching of all columns from the database.

var find = Find("people").selAll();

The above statement is equivalent to:

SELECT * FROM people AS p;

Use table optional parameter of selAll method to specify table from which all columns shall be fetched.

var find = Find("people", alias: 'p').selAll('p');

The above statement is equivalent to:

SELECT p.* FROM people AS p;

Selecting columns by name

Individual columns can be selected using sel method.

var find = Find("people").sel('name').sel('age');

The above statement is equivalent to:

SELECT name, age FROM people;

Use alias optional parameter of sel method to select with what key the column will be returned as in the result.

var find = Find("people").sel('name', alias: 'n').sel('age', alias: 'a');

The above statement is equivalent to:

SELECT name AS n, age AS a FROM people;

The matching rows will be returned as:

{
  'n': 'Teja',
  'a': 29,
}

Use table parameter of sel method to specify which table the column belongs to:

var find = Find("people", alias: 'p').sel('name', table: 'p').sel('age', table: 'p');

The above statement is equivalent to:

SELECT p.name, p.age FROM people as p;

Selecting multiple columns with one call

Use selMany method to select multiple columns from a table with a single call.

var find = Find("people").selMany(['name', 'age']);

The above statement is equivalent to:

SELECT name, age FROM people;

Use table parameter of selMany method to specify which table the column belongs to:

var find = Find("people", alias: 'p').selMany(['name', 'age'], table: 'p');

The above statement is equivalent to:

SELECT p.name, p.age FROM people as p;

Filtering

where method is used to add conditional expression to filter which records will be fetched by the statement.

final find = Find('person').selAll().where(eq('id', 1));

The above statement is equivalent to:

SELECT * FROM person WHERE id = 1

Refer Conditional expressions article to learn how to write conditions for find statement's 'where' clause.

Find statement also provides various convenience methods to add where conditions with less code using methods eq, ne, gt, gtEq, ltEq, lt, like and between. Use and and or methods to build nested conditional expressions.

Joining

Use innerJoin, leftJoin, rightJoin, fullJoin and crossJoin methods to perform inner, left, right, full and cross joins respectively on SELECT statement.

The optional alias parameter can be used to provide an alias to the joined table.

Provide the join condition by using subsequent joinOn method.

final find = Find('people').selAll().selAll('address')
  .innerJoin('address').joinOn(Field('people.id').eqF('address.pid'));

The above statement is equivalent to:

SELECT *, address.* FROM people INNER JOIN address ON people.id = address.pid;

Refer Conditional expressions article to learn how to write conditions for join clause.

Result rows manipulation

Order / sort

orderBy method can be used to order or sort the retrieved rows in a specific order. The optional ascending parameter sets if the ordering shall be ascending or descending.

var find = Find("people").selAll().orderBy('age');

The above statement is equivalent to:

SELECT * FROM people AS p ORDER BY age ASC;

orderByMany lets you add multiple orderby clauses with one call.

Limit

limit method sets the maximum number of rows returned by the query.

orderBy method can be used to order or sort the retrieved rows in a specific order. The optional ascending parameter sets if the ordering shall be ascending or descending.

var find = Find("people").selAll().limit(10);

The above statement is equivalent to:

SELECT * FROM people AS p LIMIT 10;

limit can be combined with offset to obtain effects similar to pagination.

Offset

offset method can be used to skip initial 'n' number of rows from the returned results.

var find = Find("people").selAll().offset(20);

The above statement is equivalent to:

SELECT * FROM people AS p OFFSET 10;

offset can be combined with limit to obtain effects similar to pagination.

Executing the find statement

Person person = await find.exec(adapter).oneTo(Person.from);