Билдер SQL запросов для modx, minishop2 и TV

Всем привет, просили меня недавно сделать выгрузку со старого сайта на modx с более чем 200к товаров, т.к. я уже давно не работал с modx, абсолютно забыв методы API, а также помня, насколько медленно и ресурсозатратно оно работает было решено писать SQL напрямую и все бы ничего, пока я не увидел структуру tv полей. Руками писать SQL с выборкой необходимых TV было адским адом, и было решено за часик накидать мини билдер SQL запросов

Получился примерно такой в использовании билдер, по мне покрывает 100% моих задач в формировании SQL для выгрузок из modx & ms2:
$builder = new SimpleBuilder();
$builder->addResourceFields('id', 'pagetitle', 'longtitle')
    ->addMs2Fields('article', 'price')
    ->addTvFields('price_opt', 'product_time', 'valute', 'remains', 'product_tax')
    ->where('deleted', '=', false)
    ->where('published', '=', true)
    ->where('class_key', '=', 'msProduct')
    ->where('remains', '!=', 0)
    ->whereNotNull('remains')
    ->whereIn('id', [18559, 18560])
    ->limit(1)
    ->offset(1)
;
$sql = $builder->sql();

$products = $modx->query($sql)->fetchAll(PDO::FETCH_ASSOC);
Под катом код и результат SQL который формирует билдер
Если хочется сразу к коду


И так, к SQL который сформировал код выше:
SELECT resource.id,
       resource.pagetitle,
       resource.longtitle,
       ms2.article,
       ms2.price,
       price_opt_table.value    as price_opt,
       product_time_table.value as product_time,
       valute_table.value       as valute,
       remains_table.value      as remains,
       product_tax_table.value  as product_tax
FROM modx_site_content as resource
         LEFT JOIN modx_ms2_products as ms2 on resource.id = ms2.id
         LEFT JOIN modx_site_tmplvars as price_opt_tv_name on price_opt_tv_name.name = 'price_opt'
         LEFT JOIN modx_site_tmplvar_contentvalues as price_opt_table
                   on resource.id = price_opt_table.contentid and price_opt_table.tmplvarid = price_opt_tv_name.id
         LEFT JOIN modx_site_tmplvars as product_time_tv_name on product_time_tv_name.name = 'product_time'
         LEFT JOIN modx_site_tmplvar_contentvalues as product_time_table
                   on resource.id = product_time_table.contentid and
                      product_time_table.tmplvarid = product_time_tv_name.id
         LEFT JOIN modx_site_tmplvars as valute_tv_name on valute_tv_name.name = 'valute'
         LEFT JOIN modx_site_tmplvar_contentvalues as valute_table
                   on resource.id = valute_table.contentid and valute_table.tmplvarid = valute_tv_name.id
         LEFT JOIN modx_site_tmplvars as remains_tv_name on remains_tv_name.name = 'remains'
         LEFT JOIN modx_site_tmplvar_contentvalues as remains_table
                   on resource.id = remains_table.contentid and remains_table.tmplvarid = remains_tv_name.id
         LEFT JOIN modx_site_tmplvars as product_tax_tv_name on product_tax_tv_name.name = 'product_tax'
         LEFT JOIN modx_site_tmplvar_contentvalues as product_tax_table
                   on resource.id = product_tax_table.contentid and product_tax_table.tmplvarid = product_tax_tv_name.id
WHERE deleted = 0
  AND published = 1
  AND class_key = 'msProduct'
  AND remains_table.value != 0
  AND remains_table.value IS NOT NULL
  AND resource.id IN (18559, 18560)
LIMIT 1 OFFSET 1

Из приятного:

Прирост скорости по сравнению с $modx->getCollection() более чем в 10 раз (!) не удивительно соб-но =\.
Если колонка WHERE присутствует хоть в одном из SELECT, то ему автоматически подставится префикс, а значит отсутствие конфликтов, и можно указывать прямо так:
$builder->where('id', '=', 1);
т.е. без указания таблицы, к которой принадлежит эта колонка

Из неприятного:

  1. Нет выборки картинок ms2_product_files, для моей задачи этого не надо было, по этому и не писал
  2. Он написан за час, а эта заметка за 10 минут, не исключено наличие ошибок, а качество кода оставляет желать лучшего
  3. Если колонка ресурса будет пересекаться по названию например с колонкой TV вы наверняка получите ошибку, будьте внимательны
  4. Дефолтное значение tv параметра не подтягивается, подставляйте его в коде при выгрузке, а лучше вообще не используйте
Ну и в целом код написан больше для тех, кто понимает что делает и знает где и что подкрутить, в случае если в его запросе что то сформируется не так

Код смотреть тут
Требования: php >= 7.1
Pavel Zarubin
24 февраля 2022, 02:10
modx.pro
4
626
+10
Поблагодарить автора Отправить деньги

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

Павел Гвоздь
24 февраля 2022, 17:16
0
Прирост скорости по сравнению с $modx->getCollection() более чем в 10 раз
А почему сравнивается с getCollection, а не с newQuery?
    Pavel Zarubin
    24 февраля 2022, 17:20
    0
    Я честно говоря уже настолько долго не работал с modx что понятия не имею что за newQuery, если есть возможность и время, я был бы рад увидеть пример подобного запроса на newQuery, если там надо руками джойнить таблицы с тв, то какой в нем смысл по сравнению с написанием обычного SQL?
    Главная причина почему пришлось писать билдер, это адская структура таблиц тв полей и сложность джоина каждого из параметров
      Павел Гвоздь
      24 февраля 2022, 17:23
      +4
      Я задавал вопрос именно в контексте разницы скорости, а не удобства, ибо думается мне, что разница в скорости с твоим решением либо будет минимальна, либо вообще её не будет.
        Pavel Zarubin
        24 февраля 2022, 17:55
        0
        Очевидно что не будет, а еще она также будет минимальна если все эти лефт джоины прописать руками в mysql, только вот newQuery так себе билдер
        И код на newQuery будет скорее всего похож по количеству и структуре на выходной SQL
        почему сравнивается getCollection, а не с newQuery
        Потому что в случае с getCollection не надо джойнить tv, а можно их получить через getTVValue через модель собственно
          Pavel Zarubin
          24 февраля 2022, 17:57
          0
          Я ж и попросил показать код на newQuery, мне не очень понятно зачем я должен его использовать, если я могу написать то же самое по объему и сложности на обычном SQL
            Павел Гвоздь
            24 февраля 2022, 19:02
            0
            Я не говорю, что ты должен его использовать. Я просто заметил, что ты сравниваешь getCollection, который предназначен не совсем для подобных дел, и свой быстрый билдер запросов, когда стоило последний сравнивать с более подходящим конкурентом – newQuery.

            $q = $modx->newQuery('msProduct')
                ->leftJoin('modTemplateVar', 'price_opt_tv_name', 'price_opt_tv_name.name = "price_opt"')
                ->leftJoin('modTemplateVarResource', 'price_opt_table', 'price_opt_table.tmplvarid = price_opt_tv_name.id AND price_opt_table.contentid = msProduct.id')
                ->where([])
                ...
            примерно так будет.
              Pavel Zarubin
              24 февраля 2022, 19:16
              0
              примерно так будет.
              Ну вот об этом я и говорю, какой смысл использовать тогда newQuery если все то же самое и в таком же количестве можно написать напрямую в SQL?

              getCollection, который предназначен не совсем для подобных дел
              А для каких дел он тогда предназначен, если не может построить оптимальный запрос?

              когда стоило последний сравнивать с более подходящим конкурентом – newQuery.
              Не знаю, я все таки считаю что сравнивать с newQuery не корректно, newQuery просто транслирует php команды в SQL код, он не автоматизирует ничего и не упрощает
      Павел Голубев
      25 февраля 2022, 15:45
      0
      А 200к товаров хранятся в отдельной таблице или в modx_site_content?
        Pavel Zarubin
        25 февраля 2022, 20:11
        0
        Все стандартно, кроме прикрученного эластика для поиска все нормально справляется если к этому вопрос )
          Павел Голубев
          26 февраля 2022, 11:43
          0
          Ага, спасибо. А эластик используется для фасетного поиска?
            Pavel Zarubin
            26 февраля 2022, 16:11
            0
            Нет, обычный полнотекстовой поиск, там не такие товары и не такой заказчик чтобы заморачиваться с фильтрами и фасетным поиском) Наверное первый, кто дойдет до задачи фасетного поиска на этом проекте, просто перетащит его на какой нибудь условный ларавель, чтобы не иметь в будущем проблем)))
        Авторизуйтесь или зарегистрируйтесь, чтобы оставлять комментарии.
        11