WHERE

Where – класс определяет фрагмент WHERE SQL выражения. Данный класс позволяет получить sql любой сложности и массив биндов. Экземпляр класса получаем через фабрику (смотри раздел УСТАНОВКА).

Определимся с понятиями

Логическое выражение – набор логических операций. В нашем случае это where часть sql запроса.

Логическая операция – наименьшая автономная часть where запроса.
Пример: name like '%Вас%'

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

Логическая операция: [колонка, знакСравнения, значение]
Пример: [‘name’, ‘like’, ‘%Вас%’]

Рассмотрим как же sql выражение curse = 1 and (old < 18 or old > 50) будет представлено под капотом:

[
    ['curse', '=', 1],
    'and',
    [
        ['old', '<', 18],
        'or',
        ['old', '>', 50]
    ]
]

Данную структуру будем называть сырое выражение.

Сырое выражение можно получить с помощью метода getRaw() и задать методом setRaw($raw).

Методы linkAnd() и linkOr()

В простом случае, если в where логические операции связаны только оператором И или только ИЛИ, тогда можно использовать linkAnd() и linkOr() соответственно.

// sql: old > 18 and old < 50
$wh-> linkAnd([
    ['old', '<', 18],
    ['old', '>', 50]
]);


// sql: old < 18 or old > 50
$wh-> linkOr([
    ['old', '<', 18],
    ['old', '>', 50]
]);

Если же логическое выражение содержит всего одну логическую операцию, то можно использовать любой из двух методов и можно указать только массив логической операции, не обертывая его в еще один массив: $wh->linkOr(['old', '<', 18]).

Sql любой сложности можно описать тремя методами: linkAnd(), linkOr(), getRaw(). Рассмотрим на примере уже знакомого sql выражения:

// sql: curse = 1 and (old < 18 or old > 50)   
$wh->linkAnd([
    ['curse', '=', 1],
    $wh->linkOr([  ['old', '<', 18], ['old', '>', 50]  ])->getRaw()
]);

В классе Where есть приватная переменная, в которой хранится сырое выражение. Методы linkAnd() и linkOr() перезаписывают эту переменную, поэтому при составлении логического выражения методы вкладываются друг в друга и переменная с сырым выражением содержит данные полученные из последнего выполненного метода.

Логическая операция

В простом случае логическая операция представляет собой массив: [колонка, знакСравнения, значение].

Первый элемент массива - это просто строка, вставляющаяся в sql запрос без изменений, а следовательно в ней можно писать sql функции. Пример: ['LOWER(name)', 'like', '%вас%']

Второй элемент - это то же строка, вставляющаяся в sql без изменений между двумя операндами. Может принимать следующие значения: =, >, <, >=, <=, <>, like, not like, between, not between, in, not in.

Третий элемент массива может быть как числового, так и строкового типа. Where класс автоматически подставит в sql запрос вместо значения псевдоним. Например name like :al_where_UsK63hsfVW. Значения можно получить с помощью функции getBind().

Элемент массива с ключом sql. Бывает нужно, чтобы значение вставлялось в sql код без изменений. Например, для применения функций. Этого можно добиться, указав в качестве ключа ‘sql’. Пример: [‘LOWER(name)’, ‘like’, ‘sql’ => "LOWER('$name')"]

Элемент массива с ключом bind в логической операции используется для хранения массива биндов, где ключи – псевдонимы. Приведённый выше пример неправильный с точки зрения безопасности. Нельзя вставлять переменные в sql – черевато инъекциями. Поэтому в данном случае нужно будет самому указывать псевдонимы, например так: ['LOWER(name)', 'like', 'sql' => "LOWER(:name)", 'bind'=> ['name' => $name] ]

Функция flex($column, $value) сама по себе является логической операцией и полезна, если нужен поиск по тексту без учета регистра.
Пример:

// Sql:  curse = 1 and LOWER(name) like LOWER(':aliasName')

// без функции flex
$name = 'м%А';
$wh->linkAnd([
    ['curse', '=', 1],
    [   'LOWER(name)',
        'like',
        'sql' => "LOWER(':aliasName')",
        'bind'=> ['aliasName' => $name]
    ]
]);

// с функцией flex
$name = 'м*А';
$wh->linkAnd([
    ['curse', '=', 1],
    $wh->flex('name', $name)
]);

Обратите внимание, что в обычном случае в качестве маски используется процент (%), а в функции flex() используется звездочка (*). Есть еще fullFlex($column, $value), это то же самое, что flex($column, "*$value*")

Оператор in в логической операции можно записать так: ['curse', 'not in', [1, 3, 5]]. Класс Where преобразует такую запись в следующий sql код: curse not in (:al_where_jCgWfr95kh, :al_where_mCqefr95kh, :al_where_jCfgfr9Gkh)

Оператор between в логической операции можно записать так: ['curse', ' between', 1, 5]. Класс Where преобразует такую запись в следующий sql код: curse between :al_where_Pi4CRr4xNn and :al_where_WiPPS4NKiG

Описание функций и методов

Примеры

Пример 1

Используем максимальное количество элементов класса Where в запросе.

Выборка всех системных блоков с процессором i5 или i7, оперативкой между 4 и 32 гигами. Материнская плата должна быть одной из следующих марок: ASUS, GIGABYTE, Intel, MSI. Дата создания сборки должна быть между 01.01.2016 и 01.01.2019. Средняя оценка сборки не должна быть ниже 3, а автора сборки должны звать Вася.

(core = 'i5' or core = 'i7')
and size_ram between 4 and 32 
and mark_motherboard in ('ASUS', 'GIGABYTE', 'Intel', 'MSI')
and build_date between  TO_DATE('01.01.2016', 'dd.mm.yyyy hh24:mi::ss') and TO_DATE('01.01.2019', 'dd.mm.yyyy hh24:mi::ss')
and avg(build_rating) >= abs(3) 
and lower(author_build) like lower('%Вася%')

// Создаем экземпляры классов Where с помощью фабрики подробнее в документации в разделе УСТАНОВКА
// (здесь случай компонента для yii2)
$wh = Yii::$app->gp->where();
$sqlWhere = $wh->linkAnd([
                $wh->linkOr([ ['core', '=', 'i5'], ['core', '=', 'i7'] ])->getRaw(),
                ['size_ram', 'between', 4, 32],
                ['mark_motherboard', 'in', ['ASUS', 'GIGABYTE', 'Intel', 'MSI'] ],
                ['build_date', 'between', '01.01.2016', '01.01.2019'],
                ['avg(build_rating)', '>=', 'sql' => 'abs(:x)', 'bind' => ['x' => 3]],
                $wh->fullFlex('author_build', 'Вася')
            ])->generate()->getSql();
$bindWhere = $wh->getBind();

/* Содержимое переменной $sqlWhere:
 ( core  =  :al_where_FqzYHPa9TD  or  core  =  :al_where_wSqo2YqqS5 )  
 and  size_ram  between  :al_where_wp5urPaF7F and :al_where_9DWystdstW  
 and  mark_motherboard  in  ( :al_where_WQFd4y2lPL ,  :al_where_gb1cwjSwOQ ,  :al_where_1NjObC7fUr ,  :al_where_2H7xL2WEdB )  
 and  build_date  between   TO_DATE(:al_where_fkD7nZg5lU, 'dd.mm.yyyy hh24:mi::ss')  and  TO_DATE(:al_where_LdyVRznPF8, 'dd.mm.yyyy hh24:mi::ss')   
 and  avg(build_rating)  >=  abs(:x)  
 and  LOWER(author_build)  like  LOWER(:al_where_Ha7bUPnrFE) 

Содержимое переменной $bindWhere:
Array
(
    [al_where_FqzYHPa9TD] => i5
    [al_where_wSqo2YqqS5] => i7
    [al_where_wp5urPaF7F] => 4
    [al_where_9DWystdstW] => 32
    [al_where_WQFd4y2lPL] => ASUS
    [al_where_gb1cwjSwOQ] => GIGABYTE
    [al_where_1NjObC7fUr] => Intel
    [al_where_2H7xL2WEdB] => MSI
    [al_where_fkD7nZg5lU] => 01.01.2016
    [al_where_LdyVRznPF8] => 01.01.2019
    [x] => 3
    [al_where_Ha7bUPnrFE] => %Вася%
) */  

Пример 2

Пример со множеством вложенных скобок.

core  =  'i7'  or
( 
    core  =  'i5'  and  orm  =  64  and
    ( 
        LOWER(mark_monitor)  like  LOWER('%apple%')  or
        ( LOWER(mark_monitor)  like  LOWER('%samsung%')  and  diagonal  =  27)
    )
)

$sqlWhere = $wh->linkOr([
    ['core', '=', 'i7'],
    $wh->linkAnd([
        ['core', '=', 'i5'],
        ['orm', '=', '64'],
        $wh->linkOr([
            $wh->fullFlex('mark_monitor', 'apple'),
            $wh->linkAnd([
                $wh->fullFlex('mark_monitor', 'samsung'),
                ['diagonal', '=', 27]
            ])->getRaw()
        ])->getRaw()
    ])->getRaw()
])->generate()->getSql();
$bindWhere = $wh->getBind();

/* Содержимое переменной $sqlWhere:
core  =  :al_where_9yIsG56PAg  or  
    ( core  =  :al_where_LX8Pu4fIiQ  and  orm  =  :al_where_ydlEfqrfz2  and  
        ( LOWER(mark_monitor)  like  LOWER(:al_where_w1dH6AonLZ)  or  
            ( LOWER(mark_monitor)  like  LOWER(:al_where_4EgcfW9iLu)  and  diagonal  =  :al_where_Azr8RX4ODu ) 
        ) 
    ) 

Содержимое переменной $bindWhere:
Array
(
    [al_where_9yIsG56PAg] => i7
    [al_where_LX8Pu4fIiQ] => i5
    [al_where_ydlEfqrfz2] => 64
    [al_where_w1dH6AonLZ] => %apple%
    [al_where_4EgcfW9iLu] => %samsung%
    [al_where_Azr8RX4ODu] => 27
) */