Вступление

Привет всем хабровчанам, любителям Yii и Node.js. Почему объединены любители PHP-фреймворка и серверного JavaScript?
Потому что Yii теперь доступен и на JavaScript (как для Node.js, так и для браузера)!

В этой статье мы рассмотрим Query Builder, полностью сохранивший API от Yii2 и работающий на Node.js.
Конструктор запросов — это лишь одна из реализованных частей Jii (не путать с Yii), в данной статье я специально не буду рассматривать фреймворк в целом, потому что его вполне можно использовать и частями.

Что такое Jii?

Jii — это компонентный JavaScript MVC фреймворк, который повторяет архитектурные решения легендарного PHP фреймворка Yii 2, в большинстве случаев сохраняя его API. Отсюда происхождение названия Jii — JavaScript Yii.

Установка

Jii и его части распространяются как пакеты менеджера npm. Установить его можно одной командой:

npm install jii jii-model jii-ar-sql

После установки вам доступен пространство имен и класс Jii, это единственная входная точка доступа ко всем классам Jii.
Пакет jii — это базовый пакет jii, который как раз объявляет пространство имен и класс Jii и возвращает его. Все остальные части Jii (в том числе jii-ar-sql) ставятся тоже как пакеты, но они лишь наполняют базовый неймспейс.


Все примеры, которые будут описаны ниже предполагают наличие установленного MySQL, Node.js и примерно такого кода:

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

        });

});

Объекты доступа к базе данных

Эти объекты реализуют интерфейс, через который можно отправлять запросы к базе данных и получать ответы в определенном формате. Они используются конструкторами запросов и Active Record.

Каждый из объектов доступа к данным обращается к СУБД через драйвера, которые для каждой базы свои. Все они реализуют единый API, позволяющий сменить СУБД.

На данный момент реализован объект доступа к MySQL, который использует пакет-драйвер из
npm mysql. Поддержка других СУБД предусмотрена и планируется в будущем.

Создание соединения с БД

Для доступа к базе данных необходимо создать экземпляр соединения Jii.sql.Connection. Затем необходимо открыть соединение для загрузки схемы БД и установки постоянного соединения.

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

Если вы создаете приложение Jii, то удобнее это соединение прописать в конфигурации приложения как компонент приложения доступен через `Jii.app.db`.

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

Выполнение SQL запросов

Когда у вас есть экземпляр подключения к базе данных, вы можете выполнять SQL запрос, выполнив следующие действия:

1. Создать экземпляр Jii.sql.Command с обычным SQL;
2. Добавить параметры в запрос, если необходимо;
3. Вызвать один из методов Jii.sql.Command.

Рассмотрим несколько примеров выборки данных из БД:

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

});

Добавление параметров

При создании команды с параметрами, вы должны всегда добавлять параметры через вызовы методов `bindValue` или `bindValues` для предотвращения атак SQL-инъекции. Например:

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

Выполнение запросов не на выборку данных

Запросы на изменение данных необходимо выполнять с помощью метода `execute()`:

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

Метод Jii.sql.Command.execute() возвращает объект, в котором находится информация с результатом запросов. Каждый из объектов доступа может добавлять в него свои специфичные параметры, но минимальный набор параметров в нем таков:

• `affectedRows` — Количество затронутых (измененных) строк
• `insertId` — уникальный сгенерированный идентификатор. Возвращается для запросов INSERT при наличии в колонке PK с AUTO_INCREMENT.

Для INSERT, UPDATE и DELETE запросов, вместо того, чтобы писать обычные SQL запросы, вы можете вызывать
Jii.sql.Command.insert(), Jii.sql.Command.update(), Jii.sql.Command.delete() методы для создания
соответствующих SQL. Эти методы будут правильно экранировать имена таблиц, столбцов и значения параметров.

// 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();

Вы можете так же вызывать Jii.sql.Command.batchInsert() для вставки нескольких строк в один запрос, это будет более
эффективно с точки зрения производительности:

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

Изменение схемы базы данных

Jii DAO предоставляет набор методов для изменения схемы базы данных:

• Jii.sql.Command.createTable(): создание таблицы
• Jii.sql.Command.renameTable(): переименование таблицы
• Jii.sql.Command.dropTable(): удаление таблицы
• Jii.sql.Command.truncateTable(): удаление всех строк из табилцы
• Jii.sql.Command.addColumn(): добавление колонки
• Jii.sql.Command.renameColumn(): переименование колонки
• Jii.sql.Command.dropColumn(): удаление колонки
• Jii.sql.Command.alterColumn(): изменение колонки
• Jii.sql.Command.addPrimaryKey(): добавление первичного ключа
• Jii.sql.Command.dropPrimaryKey(): удаление первичного ключа
• Jii.sql.Command.addForeignKey(): добавление внешнего ключе
• Jii.sql.Command.dropForeignKey(): удаление внешнего ключа
• Jii.sql.Command.createIndex(): создание индекса
• Jii.sql.Command.dropIndex(): удаление индекса

Пример использования этих методов:

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

Вы также можете получить информацию о таблице через метод Jii.sql.Connection.getTableSchema()

table = db.getTableSchema('post');

Метод возвращает объект Jii.sql.TableSchema, который содержит информацию о столбцах таблицы, первичные ключи, внешние ключи, и т.д. Все эти данные используются главным образом в конструкторе запросов и Active Record для упрощения работы с БД.

Конструктор запросов

Конструктор запросов использует объекты доступа к базе данных (Database Access Objects), что позволяет строить SQL запросы на языке JavaScript. Конструктор запросов повышает читаемость SQL-кода и позволяет генерировать более безопасные запросы в БД.

Использование конструктора запросов делится на 2 этапа:

1. Создание экземпляра класса Jii.sql.Query для представления различных частей SQL выражения (например, `SELECT`, `FROM`).
2. Вызов методов (например, `all()`) у эклемпляра Jii.sql.Query для выполнения запроса к базе данных и асинхронного получения данных.

Следующий код показывает простейший способ использования конструктора запросов:

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

Приведенный выше код сгенерирует и выполнит следующий SQL код, в котором параметр `:last_name` связан со значением `'Smith'`.

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

Создание запросов

Для построения запроса необходимо вызывать различные методы объекта Jii.sql.Query, наполняя тем самым различные части SQL команды. Имена методов схожи с названиями SQL операторов. Например, чтобы указать `FROM`, необходимо вызвать метод `from()`. Все методы возвращают сам объект запроса, что позволяет объединять несколько вызовов вместе.

Далее мы опишем использование каждого метода конструктора запросов.

Jii.sql.Query.select()

Метод Jii.sql.Query.select() метод определяет часть `SELECT` SQL запроса. Вы можете указать столбцы, которые
будут выбраны.

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

// эквивалентно:

query.select('id, email');

Имена столбцов могут включать в себя названия таблиц и/или псевдонимы столбцов.
Например,

query.select(['user.id AS user_id', 'email']);

// эквивалентно:

query.select('user.id AS user_id, email');

Вы можете передаеть объект, где ключами будут псевдонимы столбцов.
Например, приведенный выше код можно переписать следующим образом:

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

По-умолчанию (даже если не вызывать метод Jii.sql.Query.select()), в запросе будет сгенерирована звездочка `*`
для выбора всех столбцов.

Кроме имен столбцов, вы можете также указывать SQL выражения. Например:

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

Так же поддерживаются под-запросы, для этого необходимо передать объект Jii.sql.Query как один из элементов для выборки.

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

Для добавления слова `'DISTINCT'` в SQL запрос, необходимо вызвать метод Jii.sql.Query.distinct():

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

Вы так же можете вызывать метод Jii.sql.Query.addSelect() для добавления дополнительных колонок.

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

Jii.sql.Query.from()

Метод Jii.sql.Query.from() наполняет фрагмент `FROM` из SQL запроса. Например:

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

Имена таблиц могут содержать префиксы и/или псевдонимы таблиц. Например:

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

// эквивалентно:

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

При передаче объекта, ключи объекта будут являться псевдонимами таблиц.

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

Кроме того, имена таблиц могут содержать подзапросы — объекты Jii.sql.Query.

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()

Метод Jii.sql.Query.where() наполняет секцию `WHERE` в SQL выражении. Вы можете использовать несколько форматов указания условий SQL выражения:

• строка, `'status=1'`
• объект, `{status: 1, type: 2}`
• с указанием оператора, `['like', 'name', 'test']`

Строковый формат

Строковый формат очень хорошо подходит для указания простых условий. Указанная строка напрямую записывается в SQL выражение.

query.where('status=1');

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

Вы можете добавлять параметры к запросу через методы Jii.sql.Query.params() или Jii.sql.Query.addParams().

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

Условие как объект (хеш)

Объект лучше всего использовать, чтобы указать несколько объединенных (`AND`) подусловий, каждый из которых имеет
простое равенство. Ключами объекта являются столбцы, а значения — соответствующие значения, передаваемые в условие.
Например:

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

Конструктор запросов достаточно умен, чтобы правильно обрабатывать в качестве значений `NULL` и массивы.

Вы также можете использовать подзапросы с хэш-формате:

var userQuery = (new Jii.sql.Query()).select('id').from('user');

// ...WHERE `id` IN (SELECT `id` FROM `user`)
query.where({id: userQuery});

Формат с указанием оператора

Данный формат позволяет задавать произвольные условия в программном виде. Общий формат таков:

[operator, operand1, operand2, ...]

где операнды могут быть друг указан в формате строки, объекта или с указанием оператора. Оператор может быть одним
из следующих:

• `and`: операнды должны быть объединены вместе, используя `AND`. Например, `['and', 'ID = 1', 'ID = 2']` будет генерировать `ID = 1 AND ID = 2`. Если операнд массива, он будет преобразован в строку, используя правила, описанные здесь. Например, `['and', 'type=1', ['or', 'id=1', 'id=2']]` сгенерирует `type=1 AND (id=1 OR id=2)`.
    Метод не выполняет экранирование.
• `or`: похож на `AND` оператора, заисключением того, что операнды соединяются при помощи `OR`.
• `between`: операнд 1 — имя столбца, а операнды 2 и 3 — начальные и конечные значения диапазона, в которых находятся значения колонки.
     Например, `['between', 'ID', 1, 10]` сгенерирует выражение `id BETWEEN 1 AND 10`.
• `not between`: подобно `between`, но `BETWEEN` заменяется на `NOT BETWEEN` в сгенерированном выражении.
• `IN`: операнд 1 должен быть столбцом или SQL выражение (expression). Второй операнд может быть либо массивом или объектом `Jii.sql.Query`. Например, `['in', 'id', [1, 2, 3]]` сгенерирует `id IN (1, 2, 3)`.
    Метод экранирует имя столбца и обрабатывает значения в диапазоне.
    Оператор `IN` также поддерживает составные столбцы. В этом случае операнд 1 должен быть массивом столбцов, в то время как операнд 2 должен быть массивом массивов или `Jii.sql.Query` объектом, представляющий диапазон столбцов.
• `NOT IN`: похож на `IN` оператора, за исключением того, что `IN` заменяется на `NOT IN` в созданном выражении.
• `Like`: Первый операнд должен быть столбцом или SQL выражением, а операнд 2 — строка или массив, представляющий собой значения для поиска. Например, `['like', 'name', 'tester']` сгенерирует `name LIKE '%tester%'`.
    В случае задания массива будет сгенерировано несколько `LIKE`, объединенные оператором `AND`. Например, `['like', 'name', ['test', 'sample']]` сгенерирует `name LIKE '%test%' AND name LIKE '%sample%'`.
• `or like`: похож на `like`, но для объединения используется оператор `OR`, когда вторым операндом передан массив.
• `not like`: похож на оператор `like`, за исключением того, что `LIKE` заменяется на `NOT LIKE` в сгенерированном выражении.
• `or not like`: похож на `not like`, но для объединения используется оператор `OR`, когда вторым операндом передан массив.
• `exists`: требуется один операнд, который должен быть экземпляром Jii.sql.Query. Сгенерирует выражение
  `EXISTS (sub-query)`.
• `not exists`: похож на оператор `exists`, генерирует выражение `NOT EXISTS (sub-query)`.
• `>`, `<=`, или любой другой оператор БД. Первый операнд должен быть именем столбца, а второй — значением. Например, `['>', 'age', 10]` сгенерирует `age>10`.

Добавление условий

Вы можете использовать методы Jii.sql.Query.andWhere() или Jii.sql.Query.orWhere() для добавления условий в
существующий запрос. Вы можете вызывать эти методы несколько раз, например:

var status = 10;
var search = 'jii';

query.where({status: status});

if (search) {
    query.andWhere(['like', 'title', search]);
}

Если `search` не пуст, то приведенный выше код сгенерирет следующий SQL запрос:

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

Фильтрация условий

При строительстве `WHERE` условия, основанного на данных пользователя, как правило, необходимо игнорировать пустые
значения. Например, в поисковой форме, которая позволяет осуществлять поиск по имени и по электронной почте, нужно
игнорировать поле, если в него пользователь ничего не ввел. Это можно сделать с помощью метода
Jii.sql.Query.filterWhere():

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

Различия между Jii.sql.Query.filterWhere() и Jii.sql.Query.where() состоит в том, первый будет игнорировать
пустые значения.

Значение считается пустым, если это `null`, `false`, пустой массив, пустая строка или строка, состоящая только из пробелов.

Подобно методам Jii.sql.Query.andWhere() и Jii.sql.Query.orWhere(), вы можете использовать
Jii.sql.Query.andFilterWhere() и Jii.sql.Query.orFilterWhere() для добавленя дополнительных условий.

Jii.sql.Query.orderBy()

Метод Jii.sql.Query.orderBy() добавляет часть `ORDER BY` к SQL запросу. Например:

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

В приведенном выше коде, ключами объекта являются имена столбцов, а значения — соответствующее направления сортировки.

Для добавления условий сортировки используйте метод Jii.sql.Query.addOrderBy().
Например:

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

Jii.sql.Query.groupBy()

Метод Jii.sql.Query.orderBy() добавляет часть `GROUP BY` к SQL запросу. Например,

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

Если `GROUP BY` включает только простые имена столбцов, вы можете указать его, используя строку, как при написании обычного SQL. Например:

query.groupBy('id, status');

Вы можете использовать метод Jii.sql.Query.addGroupBy() для добавления дополнительных колонок к части `GROUP BY`.
Например:

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

Jii.sql.Query.having()

Метод Jii.sql.Query.having() определяет часть `HAVING` SQL выражения. Этот метод работает так же, как метод Jii.sql.Query.where(). Например,

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

Добавляйте дополнительные условия с помощью методов Jii.sql.Query.andHaving() или Jii.sql.Query.orHaving().
Например:

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

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

Методы Jii.sql.Query.limit() и Jii.sql.Query.offset() наполняют части `LIMIT` и `OFFSET` SQL выражения. Например:

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

Если вы передадите неправильные значения `limit` и `offset`, то они будут проигнорированы.

Jii.sql.Query.join()

Метод Jii.sql.Query.join() наполняет часть `JOIN` SQL выражения. Например:

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

Метод имеет 4 параметра:

• `type`: тип, например, `'INNER JOIN'`, `'LEFT JOIN'`.
• `table`: имя присоединяемой таблицы.
• `on`: (необязательный) условие, часть `ON` SQL выражения. Синтаксис аналогичен методу Jii.sql.Query.where().
• `params`: (необязательный), параметры условия (`ON` части).

Вы можете использовать следующие методы, для указания `INNER JOIN`, `LEFT JOIN` и `RIGHT JOIN` соответственно.

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

Например,

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

Чтобы присоединить несколько столбцов, необходимо вызвать `join` методы несколько раз.

Кроме того, вы можете присоединять под-запросы. В этом случае вам необходимо передать объект, где ключ будет псевдонимом присоединяемого запроса. Например:

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

Jii.sql.Query.union()

Метод Jii.sql.Query.union() наполняет часть `UNION` SQL запроса. Например,

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

Вы можете вызывать данный метод несколько раз для добавления нескольких `UNION` фрагментов.

Методы запроса

Класс Jii.sql.Query предоставляет целый набор методов для различных резельтатов запроса:

• Jii.sql.Query.all(): возвращает массив объектов, где ключами являются названия столбцов.
• Jii.sql.Query.one(): возвращает первый результат запроса — объект, соответствующий найденной строке.
• Jii.sql.Query.column(): возвращает массив, соответствующий значениями первого столбца результата запроса.
• Jii.sql.Query.scalar(): возвращает скалярное значение, расположенное в первой ячейке результата.
• Jii.sql.Query.exists(): возвращает булевое значение, указывающее содержит ли запрос какой-либо результат.
• Jii.sql.Query.count(): возвращает количество найденных строк.
• Другие методы агрегации запрос, в том числе Jii.sql.Query.sum(q), Jii.sql.Query.average(q),
    Jii.sql.Query.max(q), Jii.sql.Query.min(q). Параметр `q` является обязательным для этих методов
    и может быть либо именем столбца, либо SQL выражением.

Все эти методы возвращают экземпляр `Promise` для обработки асинхронного ответа

Например:

// 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) {
        // ...
    });

Все эти методы принимают необязательный параметр `db`, представляющий Jii.sql.Connection. Если этот параметр не
задан, будет использоваться компонент приложения `db` для подключения к БД. Ниже еще один пример использования `count()` метода:

// 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) {
        // ...
    })

Индексы в результатах запроса

Когда вы вызываете Jii.sql.Query.all(), то он вернет массив строк, которые индексируются последовательными целыми числами. Но вы можете индексировать их по-разному, например, конкретным значениям столбца или выражения с помощью метода Jii.sql.Query.indexBy(), вызванного перед методом Jii.sql.Query.all(). В этом случае будет возвращен объект.
Например:

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

Для указания сложных индексов, вы можете передать в метод Jii.sql.Query.indexBy() анонимную функцию:

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

Анонимная функция принимает параметр `row` которая содержит данные текущей строки и должна вернуть строку или число, которое будет использоваться в качестве значения индекса (ключа объекта) для текущей строки.

В завершении

Jii — опенсорсный проект, поэтому я буду очень рад, если кто-то присоединится к разработке Jii. Пишите на affka@affka.ru.
В Jii уже много чего реализовано и следующей статьей я планирую описать Active Record.