Introduction

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

In this article, we will look at the Query Builder, which fully preserves the API from Yii2 and works on Node.js.
Query Designer is only 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 also be used in parts.

What is Jii?

Jii is a component MVC JavaScript framework that repeats the architectural solutions 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, you have access to the namespace and Jii class, this is the only entry point to all Jii classes.
The jii package is the basic jii package, which just declares the namespace and the Jii class and returns it. All other parts of Jii (including jii-ar-sql ) are also put as packages, but they only fill the base namespace.


All the examples that will be described below assume that MySQL, Node.js is installed, 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 are different for each database. All of them implement a single API that allows you to change the DBMS.

At the moment, the MySQL access object is implemented, which uses the driver package from
npm mysql . Support for other DBMS is planned 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 load 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, then it is more convenient to set this connection in the application configuration as a component of the application available via `Jii.app.db`.

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

Execute SQL queries

When you have an instance of connecting to a database, you can execute a SQL query by following these steps:

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.

Consider a few examples of sample data from the 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 Parameters

When creating a command with parameters, you should always add parameters via 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) { }); 

Execution of queries not on sampling data

Requests to change data must be performed using the `execute ()` method:

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

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

• `affectedRows` - ​​Number of affected (changed) rows
• `insertId` is a unique generated identifier. Returned for INSERT requests if a PK column has AUTO_INCREMENT.

For INSERT, UPDATE and DELETE queries, instead of writing ordinary SQL queries, you can call
Jii.sql.Command.insert () , Jii.sql.Command.update () , Jii.sql.Command.delete () methods for creating
corresponding SQL. These methods will correctly escape 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 lines into one query, this will be more
effective in terms of performance:

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

Database schema change

Jii DAO provides a set of methods for modifying a database schema:

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

An example of using these methods:

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

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

 table = db.getTableSchema('post'); 

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

Query Builder

Query Designer uses Database Access Objects, which allows you to build SQL queries in JavaScript. Query Designer improves the readability of SQL code and allows you to generate more secure queries to 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 the SQL statement (for example, `SELECT`,` FROM`).
2. Calling methods (for example, `all ()`) on the Jii.sql.Query statement to perform a database query and asynchronous data retrieval.

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` `Smith'`.

 SELECT `id`, `email` FROM `user` WHERE `last_name` = :last_name LIMIT 10 

Making requests

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 SQL statement names. 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 design method.

Jii.sql.Query.select ()

The Jii.sql.Query.select () method defines the `SELECT` part of an SQL query. You can specify the columns that
will be selected.

 query.select(['id', 'email']); // : query.select('id, email'); 

Column names can 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 for selection.

 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 need to 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 aliases of tables. For example:

 query.from(['public.user u', 'public.post p']); // : query.from('public.user u, public.post p'); 

When transferring an object, the object keys will be aliased 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 fills 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 the operator, `['like', 'name', 'test']`

String format

The string format is very well suited for specifying simple conditions. The specified string is directly written 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)

The object is best used to specify several combined (`AND`) sub-conditions, each of which has
simple equality. The object keys are the 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 properly handle `NULL` and arrays as values.

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}); 

Format with operator

This format allows you to set arbitrary conditions in software. The general format is:

 [operator, operand1, operand2, ...] 

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

• `and`: operands should 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 shielding.
• `or`: is similar to the` AND` operator, except that operands are connected using `OR`.
• `between`: operand 1 is the name of the column, and operands 2 and 3 are the starting and ending values ​​of the range in which the column values ​​are located.
  For example, `['between', 'ID', 1, 10]` generates 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 a `Jii.sql.Query` object. For example, `['in', 'id', [1, 2, 3]]` will generate `id IN (1, 2, 3)`.
  The method escapes the name of the column 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 an `Jii.sql.Query` object representing a range of columns.
• `NOT IN`: is similar to the` IN` operator, except that `IN` is replaced with` NOT IN` in the generated expression.
• Like Like: The first operand must be a column or SQL expression, and operand 2 is a string or an array representing the values ​​to be searched. For example, `['like', 'name', 'tester']` will generate `name LIKE '% tester%'`.
  In the case of specifying an array, several `LIKE` will be generated, combined by the operator` AND`. 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 join, when an array is passed by the second operand.
• `not like`: similar to the` like` operator, except that `LIKE` is replaced with` NOT LIKE` in the generated expression.
• `or not like`: is similar to` not like`, but the OR operator is used to join, when the second operand is passed an array.
• `exists`: one operand is required, which must be an instance of Jii.sql.Query . Generates an expression
  `EXISTS (sub-query)`.
• `not exists`: similar to the` exists` operator, generates the expression `NOT EXISTS (sub-query)`.
• `>`, `<=`, or any other database operator. The first operand must be the name of a column, and the second must be a 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
existing request. 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%') 

Condition filtering

When building `WHERE` conditions 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 need
ignore the field if the user has not entered anything into it. This can be done using the method
Jii.sql.Query.filterWhere () :

 //   username  email    query.filterWhere({ username: username, email: email, }); 

The differences between Jii.sql.Query.filterWhere () and Jii.sql.Query.where () is the first one to ignore
null 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 () for adding 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 ​​are the corresponding 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` only includes simple column names, you can specify it using a string, just like in normal 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 expression. 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 populate the `LIMIT` and` OFFSET` SQL statements. For example:

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

If you pass incorrect values ​​of `limit` and` offset`, they will be ignored.

Jii.sql.Query.join ()

The Jii.sql.Query.join () method fills the `JOIN` part of the SQL expression. 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, for example,` ʻINNER JOIN``, `` LEFT JOIN'`.
• `table`: the name of the table being joined.
• `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` part).

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-requests. In this case, you need to pass an object, where the key will be an 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 fills in 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 several `UNION` fragments.

Query methods

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

• Jii.sql.Query.all () : returns an array of objects, where the keys are the names of the columns.
• 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 ​​in the first column of the query result.
• Jii.sql.Query.scalar () : returns a scalar value located in the first cell of the result.
• Jii.sql.Query.exists () : returns a boolean 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 of these methods return an instance of `Promise` for handling 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 `db` parameter representing Jii.sql.Connection . If this parameter is not
set, the application component `db` 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 the query results

When you call Jii.sql.Query.all () , it returns an array of strings, which are indexed by consecutive integers. But you can index them differently, for example, specific values ​​of a column or expression 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(); 

The anonymous function takes the `row` parameter which contains the data of the current row and should return a row or a number that will be used as an 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. affka@affka.ru.
Jii Active Record.