Introduction

Hello to all the Khabrovites, fans of Yii and Node.js. Why are lovers of the PHP framework and server-side JavaScript combined?
Because Yii is now available in JavaScript (both for Node.js and the browser)!

In this article, we will look at Query Builder, which has completely preserved the API from Yii2 and runs on Node.js.
The query designer is just one of the implemented parts of Jii (not to be confused with Yii), in this article I will not specifically consider the framework as a whole, because it can be used in parts as well.

What is Jii?

Jii is a component-based JavaScript MVC framework that repeats the architecture of the legendary PHP framework Yii 2, in most cases retaining its API. Hence the origin of the name Jii - JavaScript Yii.

Installation

Jii and its parts are distributed as npm manager packages. You can install it with one command:

npm install jii jii-model jii-ar-sql

After installation, the namespace and Jii class are available to you, this is the only entry point for access to all Jii classes.
The jii package is the core jii package, which just declares the namespace and class Jii and returns it. All other parts of Jii (including jii-ar-sql ) are also delivered as packages, but they only fill the basic namespace.


All the examples that will be described below assume the presence of installed MySQL, Node.js and something like this:

var Jii = require('jii-ar-sql');
var db = new Jii.sql.mysql.Connection({
    host: '127.0.0.1',
    database: 'example',
    username: 'root',
    password: '',
    charset: 'utf8'
});
db.open().then(function() {
    (new Jii.sql.Query())
        .from('user')
        .where({last_name: 'Smith'})
        .count('*', db)
        .then(function(count) {
            console.log('Records count:', count);
            db.close();
        });
});

Database Access Objects

These objects implement an interface through which you can send queries to the database and receive responses in a specific format. They are used by query designers and Active Record.

Each of the data access objects accesses the DBMS through drivers, which have their own for each database. All of them implement a single API that allows changing the DBMS.

At the moment, an object of access to MySQL is implemented , which uses the driver package from
npm mysql . Support for other DBMSs is provided and planned in the future.

Creating a database connection

To access the database, you must create an instance of the Jii.sql.Connection connection . Then you need to open the connection to download the database schema and establish a permanent connection.

var db = new Jii.sql.mysql.Connection({
    host: '127.0.0.1',
    database: 'example',
    username: 'root',
    password: '',
    charset: 'utf8'
});
db.open().then(function() {
    // ...
});

If you are creating a Jii application, it is more convenient to register this connection in the application configuration as the application component is accessible through `Jii.app.db`.

module.exports = {
    // ...
    components: {
        // ...
        db: {
            className: 'Jii.sql.mysql.Connection',
            host: '127.0.0.1',
            database: 'example',
            username: 'root',
            password: '',
            charset: 'utf8',
        }
    },
    // ...
};

Executing SQL Queries

When you have an instance of a database connection, you can execute the SQL query by doing the following:

1. Create an instance of Jii.sql.Command with plain SQL;
2. Add parameters to the request, if necessary;
3. Call one of the Jii.sql.Command methods .

Let's consider some examples of data sampling from a database:

var db = new Jii.sql.mysql.Connection(...);
db.open().then(function() {
    // Возвращает массив объектов, каждый из объектов представляет запись в таблице,// где ключи объекта - это названия столбцов, а зачения - их соответствующие значения в// строке таблицы. При пустом ответе будет возвращен пустой массив.
    db.createCommand('SELECT * FROM post')
        .queryAll()
        .then(function(posts) {
        });
    // Возвращает объект, соответствующей строке в таблице (первой в результатах)// Вернет `null` при пустом результате
    db.createCommand('SELECT * FROM post WHERE id=1')
        .queryOne()
        .then(function(post) {
        });
    // Возвращает массив, соответствующей колонке в таблице (первой в результатах)// Вернет пустой массив при пустом результате
    db.createCommand('SELECT title FROM post')
        .queryColumn()
        .then(function(titles) {
        });
    // Возвращает скаляр. `null` при пустом результатае
    db.createCommand('SELECT COUNT(*) FROM post')
        .queryScalar()
        .then(function(count) {
        });
});

Adding Options

When creating a command with parameters, you should always add parameters through calls to the `bindValue` or` bindValues` methods to prevent SQL injection attacks. For example:

db.createCommand('SELECT * FROM post WHERE id=:id AND status=:status')
   .bindValue(':id', request.id)
   .bindValue(':status', 1)
   .queryOne()
   .then(function(post) {
   });

Executing queries not on data sampling

Data modification requests must be completed using the `execute ()` method:

db.createCommand('UPDATE post SET status=1 WHERE id=1')
   .execute();

The Jii.sql.Command.execute () method returns the object in which the information with the result of the queries is located. Each of the access objects can add its own specific parameters to it, but the minimum set of parameters in it is as follows:

• `affectedRows` - ​​The number of affected (changed) lines
• `insertId` is a unique generated identifier. Returned for INSERT queries if there is a PK column with AUTO_INCREMENT in the column.

For INSERT, UPDATE and DELETE queries, instead of writing regular SQL queries, you can call
Jii.sql.Command.insert () , Jii.sql.Command.update () , Jii.sql.Command.delete () methods to create the
appropriate SQL. These methods will correctly shield the names of tables, columns, and parameter values.

// INSERT (table name, column values)
db.createCommand().insert('user', {
    name: 'Sam',
    age: 30
}).execute().then(function(result) {
    // result.affectedRows// result.insertId
});
// UPDATE (table name, column values, condition)
db.createCommand().update('user', {status: 1}, 'age > 30').execute();
// DELETE (table name, condition)
db.createCommand().delete('user', 'status = 0').execute();

You can also call Jii.sql.Command.batchInsert () to insert multiple rows in a single query, this will be more
efficient in terms of performance:

// table name, column names, column values
db.createCommand().batchInsert('user', ['name', 'age'], {
    ['Tom', 30],
    ['Jane', 20],
    ['Linda', 25],
}).execute();

Changing the database schema

Jii DAO provides a set of methods for changing the database schema:

• Jii.sql.Command.createTable () : creating a table
• Jii.sql.Command.renameTable () : renaming a table
• Jii.sql.Command.dropTable () : deleting a table
• Jii.sql.Command.truncateTable () : removing all rows from tables
• Jii.sql.Command.addColumn () : adding a column
• Jii.sql.Command.renameColumn () : renaming a column
• Jii.sql.Command.dropColumn () : delete a column
• Jii.sql.Command.alterColumn () : column change
• Jii.sql.Command.addPrimaryKey () : adding a primary key
• Jii.sql.Command.dropPrimaryKey () : removing the primary key
• Jii.sql.Command.addForeignKey () : adding an external key
• Jii.sql.Command.dropForeignKey () : removing a foreign key
• Jii.sql.Command.createIndex () : creating an index
• Jii.sql.Command.dropIndex () : index removal

An example of using these methods:

// CREATE TABLE
db.createCommand().createTable('post', {
    id: 'pk',
    title: 'string',
    text: 'text'
});

You can also get table information through the Jii.sql.Connection.getTableSchema () method

table = db.getTableSchema('post');

The method returns a Jii.sql.TableSchema object that contains information about table columns, primary keys, foreign keys, etc. All this data is used mainly in the query designer and Active Record to simplify the work with the database.

Query constructor

The query designer uses Database Access Objects, which allows you to build SQL queries in JavaScript. The query designer improves the readability of SQL code and allows you to generate more secure queries in the database.

Using the query designer is divided into 2 stages:

1. Creating an instance of the Jii.sql.Query class to represent various parts of an SQL expression (for example, `SELECT`,` FROM`).
2. Calling methods (for example, `all ()`) on the Jii.sql.Query instance to query the database and asynchronously retrieve data.

The following code shows the simplest way to use the query designer:

(new Jii.sql.Query())
    .select(['id', 'email'])
    .from('user')
    .where({last_name: 'Smith'})
    .limit(10)
    .all()
    .then(function(rows) {
        // ...
    });

The above code will generate and execute the following SQL code in which the `: last_name` parameter is associated with the value of` `Smith ''.

SELECT`id`, `email`FROM`user`WHERE`last_name` = :last_name
LIMIT10

Create Queries

To build a query, you need to call various methods of the Jii.sql.Query object , thereby filling various parts of the SQL command. Method names are similar to the names of SQL statements. For example, to specify `FROM`, you must call the` from () `method. All methods return the request object itself, which allows you to combine several calls together.

Next, we describe the use of each query designer method.

Jii.sql.Query.select ()

The Jii.sql.Query.select () method method determines the part of the `SELECT` SQL query. You can specify the columns to
be selected.

query.select(['id', 'email']);
// эквивалентно:
query.select('id, email');

Column names may include table names and / or column aliases.
For example,

query.select(['user.id AS user_id', 'email']);
// эквивалентно:
query.select('user.id AS user_id, email');

You can pass an object where the keys are column aliases.
For example, the above code can be rewritten as follows:

query.select({user_id: 'user.id', email: 'email'});

By default (even if you do not call the Jii.sql.Query.select () method ), an asterisk `*` will be generated in the query
to select all columns.

In addition to column names, you can also specify SQL expressions. For example:

query.select(["CONCAT(first_name, ' ', last_name) AS full_name", 'email']);

Sub -queries are also supported, for this you need to pass the Jii.sql.Query object as one of the elements to select.

var subQuery = (new Jii.sql.Query()).select('COUNT(*)').from('user');
// SELECT `id`, (SELECT COUNT(*) FROM `user`) AS `count` FROM `post`var query = (new Jii.sql.Query()).select({id: 'id', count: subQuery}).from('post');

To add the word `` DISTINCT '' to the SQL query, you must call the Jii.sql.Query.distinct () method :

// SELECT DISTINCT `user_id` ...
query.select('user_id').distinct();

You can also call the Jii.sql.Query.addSelect () method to add additional columns.

query.select(['id', 'username'])
    .addSelect(['email']);

Jii.sql.Query.from ()

The Jii.sql.Query.from () method fills the `FROM` fragment from the SQL query. For example:

// SELECT * FROM `user`
query.from('user');

Table names may contain prefixes and / or table aliases. For example:

query.from(['public.user u', 'public.post p']);
// эквивалентно:
query.from('public.user u, public.post p');

When transferring an object, the keys of the object will be aliases of the tables.

query.from({u: 'public.user', p: 'public.post'});

In addition, table names can contain subqueries - Jii.sql.Query objects .

var subQuery = (new Jii.sql.Query()).select('id').from('user').where('status=1');
// SELECT * FROM (SELECT `id` FROM `user` WHERE status=1) u
query.from({u: subQuery});

Jii.sql.Query.where ()

The Jii.sql.Query.where () method populates the `WHERE` section in the SQL statement. You can use several formats for specifying SQL expression conditions:

• string, `` status = 1``
• object, `{status: 1, type: 2}`
• with statement of operator, `['like', 'name', 'test']`

String format

The string format is very well suited to indicate simple conditions. The specified string is written directly to the SQL expression.

query.where('status=1');
// или с указанием параметров
query.where('status=:status', {':status': status});

You can add parameters to the query through the Jii.sql.Query.params () or Jii.sql.Query.addParams () methods .

query.where('status=:status')
    .addParams({':status': status});

Condition as an object (hash)

An object is best used to indicate several combined (`AND`) subconditions, each of which has a
simple equality. The keys of the object are columns, and the values ​​are the corresponding values ​​passed to the condition.
For example:

// ...WHERE (`status` = 10) AND (`type` IS NULL) AND (`id` IN (4, 8, 15))
query.where({
    status: 10,
    type: null,
    id: [4, 8, 15]
});

The query designer is smart enough to handle arrays as NULL values ​​correctly.

You can also use subqueries with a hash format:

var userQuery = (new Jii.sql.Query()).select('id').from('user');
// ...WHERE `id` IN (SELECT `id` FROM `user`)
query.where({id: userQuery});

Operator format

This format allows you to set arbitrary conditions in the program form. The general format is as follows:

[operator, operand1, operand2, ...]

where the operands can be specified in the format of a string, an object, or with an operator. An operator can be one
of the following:

• `and`: operands must be combined together using` AND`. For example, `['and', 'ID = 1', 'ID = 2']` will generate `ID = 1 AND ID = 2`. If the operand is an array, it will be converted to a string using the rules described here. For example, `['and', 'type = 1', ['or', 'id = 1', 'id = 2']] 'will generate` type = 1 AND (id = 1 OR id = 2) `.
    The method does not perform escaping.
• `or`: similar to the` AND` operator, except that operands are joined using `OR`.
• `between`: operand 1 is the name of the column, and operands 2 and 3 are the start and end values ​​of the range in which the column values ​​are located.
     For example, `['between', 'ID', 1, 10]` will generate the expression `id BETWEEN 1 AND 10`.
• `not between`: like` between`, but `BETWEEN` is replaced with` NOT BETWEEN` in the generated expression.
• `IN`: operand 1 must be a column or SQL expression. The second operand can be either an array or an `Jii.sql.Query` object. For example, `['in', 'id', [1, 2, 3]]` will generate `id IN (1, 2, 3)`.
    The method escapes the column name and processes the values ​​in the range.
    The `IN` operator also supports composite columns. In this case, operand 1 must be an array of columns, while operand 2 must be an array of arrays or a `Jii.sql.Query` object representing a range of columns.
• `NOT IN`: similar to the` IN` operator, except that `IN` is replaced with` NOT IN` in the generated expression.
• `Like`: The first operand must be a column or SQL expression, and operand 2 must be a string or an array representing the values ​​to search. For example, `['like', 'name', 'tester']` will generate `name LIKE '% tester%' '.
    If the array is specified, several `LIKE` will be generated, combined by the` AND` operator. For example, `['like', 'name', ['test', 'sample']] 'will generate` name LIKE'% test% 'AND name LIKE'% sample% ''.
• `or like`: similar to` like`, but the OR operator is used to concatenate when the array is passed as the second operand.
• `not like`: similar to the` like` operator, except that `LIKE` is replaced by` NOT LIKE` in the generated expression.
• `or not like`: similar to` not like`, but the OR operator is used to concatenate when the array is passed as the second operand.
• `exists`: one operand is required, which must be an instance of Jii.sql.Query . Generates the expression
  `EXISTS (sub-query)`.
• `not exists`: similar to the operator` exists`, generates the expression `NOT EXISTS (sub-query)`.
• `>`, `<=`, or any other database operator. The first operand must be the name of the column, and the second the value. For example, `['>', 'age', 10]` will generate `age> 10`.

Adding Conditions

You can use the Jii.sql.Query.andWhere () or Jii.sql.Query.orWhere () methods to add conditions to an
existing query. You can call these methods several times, for example:

var status = 10;
var search = 'jii';
query.where({status: status});
if (search) {
    query.andWhere(['like', 'title', search]);
}

If `search` is not empty, then the above code will generate the following SQL query:

... WHERE (`status` = 10) AND (`title` LIKE '%jii%')

Filtering conditions

When constructing a `WHERE` clause based on user data, it is usually necessary to ignore empty
values. For example, in a search form that allows you to search by name and email, you must
ignore the field if the user has not entered anything in it. This can be done using the
Jii.sql.Query.filterWhere () method :

// Данные полей username и email берутся из формы
query.filterWhere({
    username: username,
    email: email,
});

The differences between Jii.sql.Query.filterWhere () and Jii.sql.Query.where () is that the first will ignore
empty values.

A value is considered empty if it is `null`,` false`, an empty array, an empty string, or a string consisting only of spaces.

Like the Jii.sql.Query.andWhere () and Jii.sql.Query.orWhere () methods , you can use
Jii.sql.Query.andFilterWhere () and Jii.sql.Query.orFilterWhere () to add additional conditions.

Jii.sql.Query.orderBy ()

The Jii.sql.Query.orderBy () method adds the `ORDER BY` part to the SQL query. For example:

// ... ORDER BY `id` ASC, `name` DESC
query.orderBy({
    id: 'asc',
    name: 'desc',
});

In the above code, the object keys are the column names, and the values ​​correspond to the sort direction.

To add sorting conditions, use the Jii.sql.Query.addOrderBy () method .
For example:

query.orderBy('id ASC')
    .addOrderBy('name DESC');

Jii.sql.Query.groupBy ()

The Jii.sql.Query.orderBy () method adds the `GROUP BY` part to the SQL query. For example,

// ... GROUP BY `id`, `status`
query.groupBy(['id', 'status']);

If `GROUP BY` includes only simple column names, you can specify it using a string, as if you were writing regular SQL. For example:

query.groupBy('id, status');

You can use the Jii.sql.Query.addGroupBy () method to add additional columns to the `GROUP BY` part.
For example:

query.groupBy(['id', 'status'])
    .addGroupBy('age');

Jii.sql.Query.having ()

The Jii.sql.Query.having () method defines the `HAVING` part of the SQL statement. This method works the same as the Jii.sql.Query.where () method . For example,

// ... HAVING `status` = 1
query.having({status: 1});

Add additional conditions using the Jii.sql.Query.andHaving () or Jii.sql.Query.orHaving () methods .
For example:

// ... HAVING (`status` = 1) AND (`age` > 30)
query.having({status: 1})
    .andHaving(['>', 'age', 30]);

Jii.sql.Query.limit () and Jii.sql.Query.offset ()

The Jii.sql.Query.limit () and Jii.sql.Query.offset () methods fill the `LIMIT` and` OFFSET` parts of the SQL statement. For example:

// ... LIMIT 10 OFFSET 20
query.limit(10).offset(20);

If you pass the wrong `limit` and` offset` values, they will be ignored.

Jii.sql.Query.join ()

The Jii.sql.Query.join () method populates the `JOIN` part of the SQL statement. For example:

// ... LEFT JOIN `post` ON `post`.`user_id` = `user`.`id`
query.join('LEFT JOIN', 'post', 'post.user_id = user.id');

The method has 4 parameters:

• `type`: type, e.g.` `INNER JOIN'`,` `LEFT JOIN'`.
• `table`: name of the joined table.
• `on`: (optional) condition, part of the` ON` SQL expression. The syntax is similar to the Jii.sql.Query.where () method .
• `params`: (optional), condition parameters (` ON` parts).

You can use the following methods to specify `INNER JOIN`,` LEFT JOIN` and `RIGHT JOIN` respectively.

• Jii.sql.Query.innerJoin ()
• Jii.sql.Query.leftJoin ()
• Jii.sql.Query.rightJoin ()

For example,

query.leftJoin('post', 'post.user_id = user.id');

To join multiple columns, you need to call the `join` methods several times.

In addition, you can attach sub-queries. In this case, you need to pass an object where the key will be the alias of the join request. For example:

var subQuery = (new Jii.sql.Query()).from('post');
query.leftJoin({u: subQuery}, 'u.id = author_id');

Jii.sql.Query.union ()

The Jii.sql.Query.union () method populates the `UNION` part of the SQL query. For example,

var query1 = (new Jii.sql.Query())
    .select('id, category_id AS type, name')
    .from('post')
    .limit(10);
var query2 = (new Jii.sql.Query())
    .select('id, type, name')
    .from('user')
    .limit(10);
query1.union(query2);

You can call this method several times to add multiple `UNION` fragments.

Request Methods

The Jii.sql.Query class provides a whole set of methods for various query results:

• Jii.sql.Query.all () : returns an array of objects where the keys are column names.
• Jii.sql.Query.one () : returns the first result of the query - the object corresponding to the found string.
• Jii.sql.Query.column () : returns an array corresponding to the values ​​of the first column of the query result.
• Jii.sql.Query.scalar () : returns the scalar value located in the first cell of the result.
• Jii.sql.Query.exists () : returns a boolean value indicating whether the query contains any result.
• Jii.sql.Query.count () : returns the number of rows found.
• Other query aggregation methods, including Jii.sql.Query.sum (q) , Jii.sql.Query.average (q) ,
    Jii.sql.Query.max (q) , Jii.sql.Query.min (q ) . The `q` parameter is required for these methods
    and can be either a column name or an SQL expression.

All these methods return an instance of `Promise` for processing an asynchronous response.

For example:

// SELECT `id`, `email` FROM `user`
(new Jii.sql.Query())
    .select(['id', 'email'])
    .from('user')
    .all().then(function(rows) {
        // ...
    });
// SELECT * FROM `user` WHERE `username` LIKE `%test%`
(new Jii.sql.Query())
    .from('user')
    .where(['like', 'username', 'test'])
    .one().then(function(row) {
        // ...
    });

All of these methods accept the optional parameter `db` representing Jii.sql.Connection . If this parameter is not
specified, the `db` application component will be used to connect to the database. Below is another example of using the `count ()` method:

// executes SQL: SELECT COUNT(*) FROM `user` WHERE `last_name`=:last_name
(new Jii.sql.Query())
    .from('user')
    .where({last_name: 'Smith'})
    .count()
    .then(function(count) {
        // ...
    })

Indexes in query results

When you call Jii.sql.Query.all () , it will return an array of strings that are indexed by consecutive integers. But you can index them in different ways, for example, specific column or expression values ​​using the Jii.sql.Query.indexBy () method called before the Jii.sql.Query.all () method . In this case, the object will be returned.
For example:

// returns {100: {id: 100, username: '...', ...}, 101: {...}, 103: {...}, ...}var query = (new Jii.sql.Query())
    .from('user')
    .limit(10)
    .indexBy('id')
    .all();

To specify complex indexes, you can pass an anonymous function to the Jii.sql.Query.indexBy () method :

var query = (new Jii.sql.Query())
    .from('user')
    .indexBy(function (row) {
        return row.id + row.username;
    }).all();

An anonymous function accepts the `row` parameter which contains the data of the current row and should return a string or a number that will be used as the index value (object key) for the current row.

At the end

Jii is an open source project, so I will be very happy if someone joins the development of Jii. Write to affka@affka.ru.
Jii has already implemented a lot of things, and I plan to describe Active Record in the next article.