Как проверять TV-поля одновр. и на '', и на NULL

Для определённости рассмотрим функционал сниппета pdoResources.
Проблема заключается в том, что классическим форматом JSONxPDO в параметре &where в общем случае невозможно указать проверку значения некоторого TV-поля одновременно и на пустое значение, и на NULL. В каких случаях может понадобиться такая проверка — напишу ниже.

Для примера возьмём параметр introtext (в БД modX это поле может принимать значения NULL)
Казалось бы, чего проще:
&where=`{"introtext":"", "OR:introtext:IS":null}`
Но проблема здесь в том, что в конечном запросе перед нашим условием в преобладающем большинстве случаев будут стоять ещё и другие условия, которые де факто будут объединены с нашим условием логикой «AND», причём наше условие в скобки взято не будет. В итоге в конечном запросе мы получим следующее условие:
WHERE (<i>прочие условия</i> AND `modResource`.`introtext` = '' OR `modResource`.`introtext` IS NULL)
Как видим, логика нарушена.

Но даже если мы в коде сниппета pdoResources будем заключать пользовательские условия в скобки, то и здесь остаются проблемы в тех случаях, когда наше условие будет включать и другие параметры. Например, нам нужно отобрать ресурсы, у которых аннотация не указана и которые имеют шаблон «frog.tpl». Как задать это условие?
Если записать так:
&where=`{"introtext":"", "OR:introtext:IS":null, "template":"frog.tpl"}`
то мы получим неверную логику:
`modResource`.`introtext` = '' OR `modResource`.`introtext` IS NULL AND `modResource`.`template` = 'frog.tpl')
Нужны скобки. А скобки в JSONxPDO-формате не предусмотрены.

========================================================================================
По большому счёту, префикс «OR» в составе JSON-ключа вообще не слудует использовать, т.к. в общем случае мы получим неверную логику. Вообще, я не понимаю, как можно было добавлять возможность указания префиксов «OR» и «AND» без возможности указания логических скобок (речь о разработчиках modX).
========================================================================================

Теперь ситуации, в которых необходима одновременная проверка значения поля и на пустую строку, и на NULL.

1. Стандартные TV-параметры
===============================
В таблице modx_site_tmplvar_contentvalues имеется 4 поля, для которых допустимы значения NULL:
introtext
content
uri
properties

Что мы имеем:
а) при создании нового ресурса modX этим полям присваивает не NULL, а пустые значения (''). При очищении этих полей значение поля в БД также принимает пустое значение (''). Т.е. если работать с чистым modX, то можно обойтись и одной проверкой — на пустую строку ('').
б) при импорте ресурсов с помощью компонента importX указанные поля принимают значения NULL (это значение по умолчанию).
в) не исключено, и скорее всего, многие другие компоненты/плагины/сниппеты, которые создают новые ресурсы, тоже инициализируют значения этих полей NULL'ами (вернее, не трогают их, и как следствие, они принимают значение по умолчанию — NULL).

Поскольку:
а) в общем случае нам неизвестно, импортировались ли ресурсы, создавались ли ресурсы сторонними компонентами и какое значение имеют поля, для которых значения явно не указывались: пустая строка или NULL;
б) в общем случае нам неизвестно, есть ли в таблице site_content поля, для которых допустимы значения NULL и что это за поля;
в) наличие и состав полей, которые могут иметь значение NULL, от версии к версии modX могут меняться.

то при отборе ресурсов, для которых не указано значение некоторого поля, необходимо предусматривать одновременную проверку и на пустую строку, и на NULL. Причём для всех стандартных TV-параметров.

2. Пользовательские TV-параметры
=====================================
Что мы имеем:
а) в таблице site_tmplvar_contentvalues поле value НЕ может иметь значения NULL, и присутствуют в этой таблице только непустые значения. Тем не менее, при формировании результирующего запроса в pdoResources таблица site_tmplvar_contentvalues присоединяется к основной таблице через LEFT JOIN. В итоге для тех пользовательских TV-параметров, которые не имеют значения, в результирующем запросе мы получим NULL. Т.е. в случае с TV-параметрами вместо сравнения с пустой строкой необходимо выполнять проверку на NULL.
б) опять-таки некоторые сторонние компоненты/плагины/сниппеты могут заносить в таблицу site_tmplvar_contentvalues пустые строки (на уровне БД пустые строки разрешены).

Таким образом, и в случае с пользовательскими TV-параметрами нам необходимо выполнять одновременную проверку и на пустую строку, и на NULL.
Cyrax_02
09 августа 2013, 16:59
modx.pro
6
5 010
0

Комментарии: 6

Василий Наумкин
14 сентября 2013, 11:27
0
Насколько я понимаю, начиная с версии 1.5.0-pl всегда будет возвращаться "", вместо NULL. То есть, проверять нужно только на "".

Если не трудно, посмотришь?
    Cyrax_02
    25 октября 2013, 22:27
    0
    Ну вот, только руки дошли.
    В общем, получается так. В предложение SELECT Вы добавили проверку IFNULL для всех дополнительных TV-полей:
    IFNULL(`TVtv1`.`value`, '')
    В итоге при заполнении чанка-шаблона значениями TV-полей туда будут «вставляться» не null, а пустые строки.

    Но осталось ещё добавить IFNULL:
    1) в предложение SELECT для стандартных полей `properties`, `uri`, `introtext`, `alias` — эти поля также могут принимать значения null
    2) в предложение WHERE для всех дополнительных TV-полей.
    Например, при указании в параметре &where JSON-условия {..., «tv1»:"", ...} в предложении WHERE должно формироваться условие:
    ... AND IFNULL(`TVtv1`.`value`, '') = '' AND ...
    3) в предложении WHERE для стандартных полей `properties`, `uri`, `introtext`, `alias`

    Необходимости добавления IFNULL в предложении WHERE (последние два пункта) можно избежать, если все проверки вытащить во внешний запрос: во внутреннем запросе выполняются JOIN'ы необходимых таблиц и отбираются все необходимые поля с использованием IFNULL, а во внешнем запросе выполняются все необходимые проверки. Но в этом случае памяти может требоваться больше. Если же без использования внешнего запроса — будет дольше выполняться. Впрочем, нужно тестировать.
      Cyrax_02
      26 октября 2013, 11:42
      0
      Да, и касательно IFNULL. В целях сохранения переносимости я бы сделал так:
      switch($modx->getOption('dbtype')) {
          case 'mysql': $dbObjectNameBorder = '`'; $nullCheckExpr = 'IFNULL'; break;
          case 'sqlsrv': $dbObjectNameBorder = ''; $nullCheckExpr = 'ISNULL'; break;
      }
      и далее использовал $dbObjectNameBorder и $nullCheckExpr. Такой вариант будет работать и с mySQL, и с MS SQL.
        Cyrax_02
        26 октября 2013, 16:11
        0
        И ещё одна оптимизация. В предложении WHERE (как для стандартных полей `properties`, `uri`, `introtext`, `alias`, так и для пользовательских TV-полей) добавлять проверку на NULL (IFNULL/ISNULL) достаточно только в 2 случаях:
        1. Если поле сравнивается с некоторым значением операцией «не равно»:
        ..., "tv1:!=":"5", ...
        Сейчас такому условию НЕ будут удовлетворять ресурсы, для которых значение поля tv1 не указано. А должны удовлетворять.
        2. Если поле сравнивается с пустым значением:
        ..., "tv1:=":"", ...
        ..., "tv1":"", ...
        Сейчас такому условию НЕ будет удовлетворять ни один ресурс. А должны удовлетворять все ресурсы, для которых не задано значение поля tv1.

        Во всех остальных случаях подстановка IFNULL/ISNULL не нужна. Все прочие условия будут работать корректно и без IFNULL/ISNULL.

        — Почему в предложении WHERE важно не добавлять IFNULL/ISNULL в безусловном порядке для всех полей, а делать это только для двух вышеуказанных случаев:
        а) ускорение выполнения запроса (т.к. каждый IFNULL/ISNULL увеличивает время выполнения запроса)
        б) если IFNULL/ISNULL ставить всегда, то пользователь не будет иметь возможности корректно использовать оператор is null:
        ..., "tv1:IS":"null", ...
        Этот оператор ничего не будет возвращать, т.к. в итоговом запросе вместо (tv1 IS NULL) получим (tv1 IS '').
          Cyrax_02
          28 октября 2013, 16:01
          0
          Самую последнюю строчку читать так:
          Этот оператор ничего не будет возвращать для tv, не содержащих значений, т.к. в итоговом запросе вместо (tv IS NULL) получим ('' IS NULL)
        Cyrax_02
        25 октября 2013, 22:24
        0
        trash
          Авторизуйтесь или зарегистрируйтесь, чтобы оставлять комментарии.
          6