Skip to content
This repository was archived by the owner on Mar 2, 2025. It is now read-only.

this.db.query

Jump to bottom
do- edited this page Nov 24, 2022 · 76 revisions

this.db.query — это функция, генерирующая SQL-запрос и соответствующий набор параметров из js-структуры данных, напоминающей запросы NoSQL-БД вроде MongoDB, CouchDB и им подобных.

Table of Contents

Использование

this.db.query предназначена для использования в коде приложения не напрямую, а для промежуточной подготовки параметров в высокоуровневых функциях:

Хотя изредка имеет смысл вызвать её непосредственно и воспользоваться результатом вида: 
 {sql: 'SELECT ...', params: [...]}

Общий вид запроса

Запрос представляет собой непустой список частей, то есть объектов, каждый из которых содержит требования к одной из таблиц в запросе:

  • что это за таблица;
  • какие нужны поля;
  • как её связать с остальными частями;
  • какие наложить фильтры:
 [
  part1, // например: {"t1 (f11 AS label, f12) AS a1" : 
         //   {'ff11 BETWEEN ? AND ?': [v11, v12], ff12: v22}}
  part2, // например: {"t2 AS a2 ON a1.id=a2.id": 
         //   {ff21: null}}
  ...
  partN  // например, просто: 'tN'
 ],

Приведённому эскизу запроса соответствует следующий набросок SQL: 

 SELECT
  a1.f11 AS label, a1.f12,
  a2.f21 AS "a2.f21", ... 
  ...
  tN.fN1 AS "tN.fN1", ... 
 FROM
  t_part1 AS a1
  [LEFT|INNER] JOIN t2 AS a2 ON a1.id=a2.id 
  ...
  [LEFT|INNER] JOIN tN ON ... 
 WHERE 1=1
  AND a1.ff11 BETWEEN ? AND ?
  AND ...
 [ORDER BY ...]

В следующих разделах синтаксис детализирован, но пока уточним область применимости this.db.query.

Ограничения

Как видно из представленной схемы, в результате в принципе не предусмотрены:

  • вычисляемые выражения и функции;
    • следовательно, агрегаты и GROUP BY;
      • тем более аналитика вроде ROLLUP
  • UNION
    • и прочие множественные операции
  • вложенные SELECT до FROM
  • табличные выражения во FROM
  • WITH и пр.
Кому не хватает чего-либо из перечисленного (или забытого), но интересен минимализм или остро требуется гибкая генерация запросов на лету — тот волен воспользоваться всей мощью полноценного синтаксиса SQL посредством описания необходимых VIEWs и, возможно, триггеров в модели.

Возможности

В сущности, всё, что умеет этот генератор SQL-кода — это

  • распределять каждый объект, связанный с определённой таблицей — часть запроса (part_i) — между разделами SELECT / FROM / WHERE;
  • автоматически вычислять JOIN'ы по модели там, где это возможно;
  • форматировать выражение WHERE, параллельно составляя список параметров;
    • игнорируя фильтры со значениями undefined, что нужно для форм расширенного поиска в UI.

Анатомия части

Итак, любой запрос для this.db.query — это список частей минимум из одного элемента. Вначале мы покажем, что представляет из себя часть на примерах запросов, где такой элемент всего один (то есть без JOIN).

Часть как объект

В общем виде часть представляет собой объект с ровно одним ключом, где:

  • ключ — имя таблицы, возможно, со списком полей в скобках;
  • значение — объект, представляющий собой набор фильтров.
Пример: 
 [{'vw_ssh_command_items(host, src AS label)': {
  id_command: id, 
  'status <>': 'ok'
 }}]

порождает 

 SELECT host, src AS label
 FROM vw_ssh_command_items
 WHERE id_command = ? AND status <> 'ok'

Список полей можно не указывать — тогда выбираются все столбцы: 

 SELECT * FROM equipment_cfgs WHERE — то, что в filter

Часть одной строкой

Если с данной таблицей не должно быть связано ни одно условие в WHERE, набор фильтров пуст. Тогда вместо объекта можно указать строку, равную значению его единственного ключа: 

 this.db.add ({}, 'roles')
 
 SELECT * FROM roles

Запросы с такой единственной частью на практике не имеют смысла, однако их применение в JOIN'ах вполне может быть обоснованным.

Псевдоним таблицы

Как и в SQL, для таблиц запроса можно указать псевдонимы, воспользовавшись ключевым словом AS (в отличие от SQL, здесь AS для псевдонимов обязательно): 

 this.db.add_all_cnt ({}, [{users: filter}, 'roles AS role']) 
    // ... FROM users LEFT JOIN roles AS role ON ...

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

Разумеется, псевдонимы всех частей должны различаться, то есть при использовании одной таблицы более одного раза явное указание псевдонима необходимо.

Подробнее о списке полей

Поля, которые необходимо извлечь в данной части, перечисляются в скобках после имени таблицы. Для каждого из них можно указать псевдоним, по аналогии с таблицей: 

 {'vw_ssh_command_items(host, src AS label)': ...} 
    // SELECT host, src AS label ...

Если скобок нет, автоматически подставляется (*) и выбираются все поля, правда в сгенерированном SQL каждое из них будет упомянуто явно — список берётся из модели. 

 'roles' // SELECT id, label FROM roles

Чтобы полностью подавить показ полей из данной части (что имеет смысл в основном для связных таблиц типа "многие-ко-многим"), необходимо явно указать пустой список: 

    user.opt = await this.db.fold ([
        {'user_options()': {
            is_on: 1,
            id_user: user.uuid
        }},
        'voc_user_options(name)'
    ], (i, d) => {d [i ['voc_user_options.name']] = 1}, {})

Имена полей в выборке

Поля первой по порядку (корневой) части фигурируют в результате со своими собственными именами (псевдонимами).

К именам всех остальных полей спереди через точку приписываются имена (псевдонимы) соответствующих частей. 

        return this.db.add_all_cnt ({}, [
            {vw_tasks : filter}, 
            'task_notes ON id_last_task_note',
        ])

        SELECT 
          vw_tasks.uuid AS "uuid", 
          ...
          task_notes.uuid AS "task_notes.uuid",
          ...

Фильтры

Объект с набором фильтров, (единственное значение в объекте каждой части) имеет вид: 

 {
  expr_1: values_1,
  expr_2: values_2,
  ...
 }

Выражения (expr_i) уточняются, дооформляются и копируются в SQL, значения — собираются в список параметров.

Общий вид

В полной форме каждый ключ объекта фильтра представляет собой SQL-выражение, начинающееся с имени поля и содержащее несколько знаков ?, а значение — список параметров соответствующей длины: 

 {
   'dt BETWEEN ? AND ?': [dt_from, dt_to],
   ...
 }

Если выражение содержит имя поля, не описанного в модели, то фильтр игнорируется.

Сравнения с константами

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

 {
   'salary >=': threshold,
   ...
 }

А для выражений, оканчивающихся на IN, константы следует задавать (непустым) списком -- тогда будет сгенерировано необходимое выражение с переменными: 

 {
    'id_status IN': [10, 20],          
      // {sql: '...id_status IN (?,?)', params: [10, 20]}
 }
 {
    'id_type NOT IN': ['A', 'B', 'C'], 
      // {sql: '...id_type NOT IN (?,?,?)', params: ['A', 'B', 'C']} 
 }

Самый популярный оператор сравнения — равенство (=). Его также можно не указывать, что сводит фильтр по фиксированному значению к паре "имя поля-константа": 

 {
   id_status: 10,
   ...
 }

Взятие по ключу

И, в свою очередь, примерно половина всех фильтров на равенство приходится на первичные ключи соответствующих таблиц. Поскольку ключевое поле известно из модели, здесь можно сэкономить, указав в качестве фильтра не объект, а скалярное значение: 

 let data = await this.db.get ([{users: this.rq.id}, 'roles AS role'])

Значения null и undefined

this.db.query — редкая функция, где нашлось применение такой экзотической особенности javaScript, как наличие двух разных пустых значений. А именно:

  • null порождает предикат IS [NOT] NULL;
  • undefined отключает фильтр:
 {
   'dt_from <>' : null,      // dt_from IS NOT NULL
    dt_to       : null,      // dt_to IS NULL
    id_status   : undefined  // фильтра по id_status не будет
    ...
 }

undefined можно использовать при реализации расширенного поиска в Web-интерфейсах, когда отсутствие значения в поле является требованием игнорировать фильтр по нему. Однако следует соблюдать осторожность: в JSON есть null, но нет undefined, поэтому нельзя передавать напрямую в фильтр фрагмент тела POST.

Это обстоятельство не создаёт большой проблемы: так или иначе, для обработки параметров, формируемых на клиенте, обычно требуется какой-нибудь переходник. В частности, Dia.pm включает такой модуль для формата, используемого w2ui.

Конструкции IS NULL OR

Нередко в запросе требуется выбрать записи, у которых заданное поле:

  • либо пусто;
  • либо удовлетворяет заданному (не)равенству.
Типичный пример: поле dt_to (дата окончания действия), которая для актуальных документов может находиться в будущем, а может и оставаться пустой. Для удобства описания таких условий в this.db.query поддерживается местный синтаксический элемент: многоточие после имени поля: 
 {
   'dt_from  <=': dt,  // AND dt_from <= ?
   'dt_to... >=': dt,  // AND (dt_to IS NULL OR dt_to >= ?)
 }

Шаблоны для LIKE

При поиске по подстроке текстового поля:

  • с клиента приходит сама искомая подстрока,
  • а в SQL её необходимо обрамить символами % с одной или двух сторон,
что неудобно.

На этот случай в this.db.query предусмотрен "синтаксический подсластитель": возможность приписать % не к значению, а к переменной, то есть в текстовый шаблон фрагмента запроса. Эти символы будут вычищены из SQL, но добавлены к значениям параметров: 

 {
    'inn   LIKE ?%' :  77,      
      // {sql: '...inn   LIKE ?', params: ['77%']}
 }
 {
    'label LIKE %?%': 'dia',    
      // {sql: '...label LIKE ?', params: ['%dia%']}
 }

В принципе данный механизм не привязан к ключевому слову LIKE и будет работать в любых ситуациях, когда со знаком вопроса соседствуют проценты. Это может иметь смысл для расширений различных диалектов SQL: например, ILIKE в PostgreSQL.

Сравнение DATETIME с DATE

Допустим, в таблице есть поле, имеющее значение точки во времени: типа DATETIME или TIMESTAMP, скажем, "момент запуска", а сравнить его предлагается с календарной датой в целом: скажем, 

  ts_launch: '1961-04-12'

Если такой запрос сформирован по результатам ввода на поисковой web-форме, наверняка имеется в виду не точный момент начала суток, а день в целом.

Поэтому функция query вместо 

  ts_launch = '1961-04-12'

сгенерирует 

  (ts_launch >= '1961-04-12 00:00:00' AND ts_launch < '1961-04-13 00:00:00')

Для тех же случаев, когда действительно требуется точное сравнение с началом суток, необходимо указать время ( 00:00:00) в передаваемом параметре.

Описанная подстановка выполняется при следующих условиях:

  • фильтр - строгое равенство;
  • поле описано в модели с типом date или timestamp;
  • значение параметра является строкой ровно из 10 символов (YYYY-MM-DD).

Подзапросы

this.db.query позволяет использовать оператор IN не только со списками констант, но и с вложенными SQL-запросами. Они должны быть представлены объектами вида {sql:..., params:...}. При таком типе параметра оператор IN подставится автоматически 

 {
   id_type: {sql: 'SELECT id FROM types WHERE kind = ?', params: [1]}
   // ...WHERE id_type IN (SELECT id FROM types WHERE kind = ?)
 }

Поскольку результат самой функции this.db.query имеет вид {sql:..., params:...}, её можно использовать для формирования вложенных подзапросов: 

 {
   id_type: this.db.query ({'types(id)': {kind: 1}})
 }

Псевдофильтры

Две компоненты реестра фильтров с фиксированными именами имеют смысл, отличный от ограничения значений полей. Обе они имеют смысл только в одной -- первой -- части запроса

ORDER

Значение, соответствующее ключу ORDER, копируется в раздел ORDER BY результирующего SQL: 

 this.db.list ([{task_notes: {
   id_task: this.rq.id, 
   ORDER: 'ts'
 }}]), 
 // SELECT * FROM task_notes WHERE id_task = ? ORDER BY ts

LIMIT

По этому ключу может быть записан либо массив из одного или двух натуральных чисел: 

 {
  LIMIT: [50, 0],
  //...
 }

либо отдельное числовое значение: 

 {
  LIMIT: 1,
  //...
 }

Если LIMIT задан, а ORDER — нет, то в качестве ORDER принудительно устанавливается первичный ключ (pk) соответствующей таблицы. Это техника безопасности: в PostgreSQL запрос с LIMIT без ORDER (к VIEW с многокаскадными JOIN) может занять изрядное время даже для пустой выборки, а требование извлечь именно последовательность с неопределённой сортировкой — смысла не имеет. Некоторые СУБД запросы с LIMIT без ORDER не принимают в принципе, считая логически некорректными (что резонно). В db.query можно было бы поступить так же, но для совместимости с некоторой частью имеющегося кода принято такое решение: доопределять ORDER, не портя запрос.

Стоит отметить, что LIMIT не влияет непосредственно на значение sql в результате db.query. Вместо этого рядом добавляются компоненты limit и offset, так что при непосредственном использовании db.query для ограничении выборки необходимо обращаться к this.db.to_limited_sql_params: 

  const {sql, params, limit, offset} = this.db.query ([{my_table: filter}])
  const [limited_sql, limited_params] = this.db.to_limited_sql_params (sql, params, limit, offset)
  const records = await this.db.select_all (limited_sql, limited_params)

Следующие методы делают это автоматически:

Соединение частей

В этом разделе показано, как в аргументе this.db.query оформляются условия для выражений JOIN раздела FROM.

LEFT или INNER

По умолчанию для всех присоединяемых таблиц this.db.query генерирует конструкцию LEFT JOIN.

Если необходимо использовать INNER JOIN, перед названием соответствующей таблицы следует поставить знак $: 

 return this.db.add_all_cnt ({}, [
  {task_notes: filter}, 
  {'$tasks(uuid, label) ON task_notes.id_task': task_filter}
 ])

 SELECT ... FROM task_notes
  INNER JOIN tasks
   ON (task_notes.id_task = tasks.uuid AND /* см. task_filter */)

Явное указание условия связи

При необходимости (что случается довольно редко) условие, которое должно фигурировать после JOIN ... ON, можно привести дословно: 

return this.db.add ({}, [
    {'users(uuid, label, uuid AS id)': {
        'login <>' : null,
        'uuid  <>' : this.user.uuid,
        is_deleted : 0,
        ORDER      : 'label',
    }},
    {'user_users AS user_user ON user_user.id_user_ref = users.uuid': {
        is_on: 1,
        id_user: this.user.uuid,
    }}
])

SELECT ... FROM users 
  LEFT JOIN user_users AS user_user 
    ON user_user.id_user_ref = users.uuid

Признак того, что условие приведено полностью — наличие знака = в ключе объекта, представляющего вторую часть. В этом случае this.db.query не пытается вычислить условие, а копирует его как есть.

Указание единственного поля-ссылки

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

 return this.db.add_all_cnt ({}, [
   {vw_tasks : filter}, 
   'task_notes ON id_last_task_note',
 ])
 
 SELECT ... FROM vw_tasks
   LEFT JOIN task_notes 
     ON vw_tasks.id_last_task_note = task_notes.id

Автоматическое вычисление связи

Нередки случаи, когда для заданного множества таблиц JOIN вообще однозначно вычисляется без явного указания чего-либо: просто на основе анализа набора полей-ссылок. 

 this.db.add_all_cnt ({}, [{users: filter}, 'roles AS role'])

 SELECT * FROM users
  LEFT JOIN roles 
   ON users.id_role = roles.id 
     // потому что других ссылок на roles нет

Стыковка с библиотеками клиентской части

Как можно видеть из вышеописанного, this.db.query во многом оптимизирована под сценарий расширенного поиска в web-интерфейсах: извлечение листаемой выборки по фиксированному JOIN с большим числом необязательных фильтров, каждый из которых активируется при задании целевого значения для соответствующего поля.

Ряд клиентских js-библиотек, реализующих таблицы (data grids) с привязкой к серверным источникам данных, как правило, позволяют легко описывать формы для такого рода поиска и далее оформляют наборы заданных пользователем фильтров в виде JSON-объектов, передаваемых в теле AJAX-запросов.

Несмотря на то, что все такие программные средства делают практически одно и то же, какого-либо стандарта для оформления наборов параметров поиска в JSON не просматривается. Поэтому в Dia.js предусмотрено несколько объектов-переходников, принимающих на вход объекты запросов разных клиентских библиотек и преобразующих их к формату аргумента this.db.query.

Соответствующие исходные тексты размещены в директории Content/Handler/HTTP/Ext.

w2ui

Исторически первой сторонней ui-библиотекой, с которой стыковалась Dia.js, стала w2ui, соответственно, для её формата прежде всего и был разработан адаптер. Впоследствии этот формат был унаследован уже на клиенте при подключении другой библиотеки: SlickGrid, не имевшей собственной AJAX-привязки.

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

Прежде всего, в местном обработчике запросов от web ui импортируется нужный класс: 

 const DiaW2uiFilter = require ('../../Ext/Dia/Content/Handler/HTTP/Ext/w2ui/Filter.js')

и определяется метод, создающий его экземпляр из текущего запроса: 

 w2ui_filter (db) {return new DiaW2uiFilter (this.rq, db)}

и далее стереотипная процедура выборки данных для показа реестра имеет общий вид: 

select_some_objects:     
    async function () {   
        this.rq.sort = this.rq.sort || [{field: "label", direction: "asc"}]    
        let filter = this.w2ui_filter (this.db)
        return await this.db.add_all_cnt ({}, [{some_objects: filter}])
    },

Параметр db передаётся только для определения продукта СУБД, что учитывается при генерации SQL и коррекции параметров. В настоящий момент это используется только для ClickHouse и в этом случае:

  • для contains вместо оператора ILIKE подставляется positionCaseInsensitiveUTF8;
  • для between при типе date верхняя граница дополняется 23:59:59 вместо 23:59:59.999.

dx

DevExtreme DevExpress (что тут фирма, а что продукт -- самый сложный вопрос при разборе их великолепной документации), в общем, dx, имеет собственный формат параметров поиска, и для него в Dia.js имеется отдельный переходник, который используется совершенно аналогично тому, что описан выше: 

//... импорт в обработчике:
const DxFilter = require ('../../Ext/Dia/Content/Handler/HTTP/Ext/dx/Filter.js')

//... метод обработчика:
dx_filter () {return new DxFilter (this.rq.loadOptions)}

// ... и дальше в select_процедурах модулей
let filter = this.dx_filter ()

DHTMLx

Для полноты картины отметим, что некоторое время назад велись эксперименты по подключению библиотеки DHTMLx, от которых в коде Dia.js остался небольшой рудимент.

В силу подозрительно напряжённого отношения авторов этой библиотеки к такой, в общем-то рутинной вещи, как динамическая подгрузка данных в таблицу (спустя почти 2 года по этой ссылке всё ещё написано: "The stated functionality requires PRO version of the dhtmlxGrid (or DHTMLX Suite) package and doesn't work with the Smart Rendering mode enabled"), дальнейшая интеграция в данном направлении крайне маловероятна.

Clone this wiki locally