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

TODO

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 conditions for join clause.

Executing the find statement

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