Документация для специалистов

Песочница

Вопросы клиентов

Tips & tricks

Blog by Fi1osof

Еще один пример сложных SQL-запросов

Выборка данных из таблицы БД

Подключение к дополнительной базе

Заставляем SQL-запросы возвращать цифры, а не только строки

Ищу разработчика ModX Evo + php + b3 + mysql + nginx + sphinx в офис, Санкт-Петербург

Удаление записей сразу из нескольких таблиц средствами xPDO

А что такое верстка на b3?

Здравствуйте!
 Вряд ли вы здесь найдете Эво-специалиста. У нас здесь исключительно Revo. Вам эффективней было бы на modx.im разместить объявление. Там в основном эволюционеры сидят.

Здравствуйте!
 Вряд ли вы здесь найдете Эво-специа

Поняла, спасибо большое.

bootstrap)

Вот же шифры то

Не за что.

Здесь вся веселость в том, что на выходе получается матрица.Если бы количество складов было четко определенным, все решилось бы одним запросом с несколькими открытыми соединениями (LEFT JOIN, RIGHT JOIN).
 Здесь же, поскольку количество складов не определено, остается только руками формировать массив.

Здесь вся веселость в том, что на выходе получаетс

SELECT N.id, N.name,
	CONCAT(
        "[",
        	GROUP_CONCAT(
                CONCAT("{shop:'",CONVERT(S.name USING utf8),"',"),
        	   CONCAT("count:'",CONVERT(P.count USING utf8),"'}")
                )
        ,"]"
        ) as data
FROM `modx_products_nomenclature` N	
	LEFT JOIN `modx_shops` S ON S.active = 1
    LEFT JOIN `modx_warehouse_products` P ON P.product_id = N.id and S.warehouse_id = P.warehouse_id
GROUP BY N.id


id 1
name G1
data [{shop:'A', count:'10'}]
.....
id 3
name G1
data [{shop:'A', count:'10'}, {shop:'B', count:'10'}]


хотелось бы конечно что бы в дата в случае если количество 0 то писало {shop: 'B', count: '0'}

Сам запрос SELECT N.id, N.name,
	CONCAT(
        "[",
        	GROUP_CONCAT(
                CONCAT("{shop:'",CONVERT(S.name USING utf8),"',"),
        	   CONCAT("count:'",CONVERT(P.count USING utf8),"'}")
                )
        ,"]"
        ) as data
FROM `modx_products_nomenclature` N	
	LEFT JOIN `modx_shops` S ON S.active = 1
    LEFT JOIN `modx_warehouse_products` P ON P.product_id = N.id and S.warehouse_id = P.warehouse_id
GROUP BY N.id возвращает вот такое id 1
name G1
data [{shop:'A', count:'10'}]
.....
id 3
name G1
data [{shop:'A', count:'10'}, {shop:'B', count:'10'}] хотелось бы конечно что бы в дата в случае если количество 0 то писало {shop: 'B', count: '0'}

Сам запрос SELECT N.id, N.name,
	CONCAT(
        "

А вот так не получается?:
 

SELECT N.id, N.name,
        CONCAT(
        "[",
                GROUP_CONCAT(
                CONCAT("{shop:'",CONVERT(S.name USING utf8),"',"),
                   CONCAT("count:'", if(P.count > 0, CONVERT(P.count USING utf8), 0),"'}")
                )
        ,"]"
        ) as data
FROM `modx_products_nomenclature` N     
        LEFT JOIN `modx_shops` S ON S.active = 1
    LEFT JOIN `modx_warehouse_products` P ON P.product_id = N.id and S.warehouse_id = P.warehouse_id
GROUP BY N.id

А вот так не получается?: SELECT N.id, N.name,
        CONCAT(
        "[",
                GROUP_CONCAT(
                CONCAT("{shop:'",CONVERT(S.name USING utf8),"',"),
                   CONCAT("count:'", if(P.count > 0, CONVERT(P.count USING utf8), 0),"'}")
                )
        ,"]"
        ) as data
FROM `modx_products_nomenclature` N     
        LEFT JOIN `modx_shops` S ON S.active = 1
    LEFT JOIN `modx_warehouse_products` P ON P.product_id = N.id and S.warehouse_id = P.warehouse_id
GROUP BY N.id

А вот так не получается?: SELECT N.id, N.name,
   

CONCAT("count:'", CONVERT(IFNULL(P.count, 0) USING utf8),"'}")


Ага так и сделал. CONCAT("count:'", CONVERT(IFNULL(P.count, 0) USING utf8),"'}")

Ага так и сделал. CONCAT("count:'", CONVERT(IFNULL

И как? Зашуршало?

Вообще отлично… особенно устроила скорость
 Вот окончательный вариант запроса 
 

$q = $modx->newQuery('ProductsNomenclature');
    $q->select(
        array(
            "`ProductsNomenclature`.`name` as product",
            "`ProductsNomenclature`.`code` as code",
            "`ProductsGroup`.`name` as groupname",
            "CONCAT('[',
                GROUP_CONCAT(
                    CONCAT('{\"shop\":\"',`Shop`.`name`,'\",'),
                    CONCAT('\"count\":', IFNULL(`WarehouseProducts`.`count`, 0),','),
                    CONCAT('\"unit\":\"', `ProductsNomenclature`.`unit_measure`,'\"}')
                )
            ,']') as data",
        )
    );
    $q->innerJoin('Shops', 'Shop', '`Shop`.`active` = 1');
    $q->innerJoin('ProductsGroup', 'ProductsGroup', '`ProductsGroup`.`id` = `ProductsNomenclature`.`group_id`');
    $q->leftJoin('WarehouseProducts', 'WarehouseProducts', '`Shop`.`warehouse_id` = `WarehouseProducts`.`warehouse_id` and `ProductsNomenclature`.`id` = `WarehouseProducts`.`product_id`');
    $q->where(
        array(
            'active' => 1,
        )
    );
    $q->sortby('`ProductsNomenclature`.`group_id`', 'ASC');
    $q->groupby('`ProductsNomenclature`.`id`');


Вообще отлично… особенно устроила скорость
 Вот окончательный вариант запроса $q = $modx->newQuery('ProductsNomenclature');
    $q->select(
        array(
            "`ProductsNomenclature`.`name` as product",
            "`ProductsNomenclature`.`code` as code",
            "`ProductsGroup`.`name` as groupname",
            "CONCAT('[',
                GROUP_CONCAT(
                    CONCAT('{\"shop\":\"',`Shop`.`name`,'\",'),
                    CONCAT('\"count\":', IFNULL(`WarehouseProducts`.`count`, 0),','),
                    CONCAT('\"unit\":\"', `ProductsNomenclature`.`unit_measure`,'\"}')
                )
            ,']') as data",
        )
    );
    $q->innerJoin('Shops', 'Shop', '`Shop`.`active` = 1');
    $q->innerJoin('ProductsGroup', 'ProductsGroup', '`ProductsGroup`.`id` = `ProductsNomenclature`.`group_id`');
    $q->leftJoin('WarehouseProducts', 'WarehouseProducts', '`Shop`.`warehouse_id` = `WarehouseProducts`.`warehouse_id` and `ProductsNomenclature`.`id` = `WarehouseProducts`.`product_id`');
    $q->where(
        array(
            'active' => 1,
        )
    );
    $q->sortby('`ProductsNomenclature`.`group_id`', 'ASC');
    $q->groupby('`ProductsNomenclature`.`id`');

Вообще отлично… особенно устроила скорость
 Вот ок

Ну и здорово!

Снова здравствуйте. Вернемся к этой выборке.
 В ходе работы нашлась какая то злостная «бажина» ))
 

Проблема вот в чем… Если последним в строке значения стоит товар которого нет на складе то запрос валится.
 

// ошибка
[6] => Array
        (
            [product] => ХХХ
            [code] => ХХХ
            [groupname] => ХХХ
            [data] => [{"shop":"Магазин1","count":0,"unit":"шт."},{"shop":"Магазин2","count":50000,"unit":"шт."},{"shop":"Магазин3","count":50000,"unit":"шт."},{"shop":"Магазин4","count":50000,"unit":"шт."},{"shop":"Магазин5","count":50000,"unit":"шт."},{"shop":"Магазин6","count":50000,"unit":"шт."},{"shop":"Магазин7","count":]
        )
//а вот тут все хорошо
[9] => Array
        (
            [product] => ХХХ
            [code] => ХХХ
            [groupname] => ХХХ
            [data] => [{"shop":"Магазин3","count":50000,"unit":"шт."},{"shop":"Магазин4","count":50000,"unit":"шт."},{"shop":"Магазин5","count":50000,"unit":"шт."},{"shop":"Магазин6","count":50000,"unit":"шт."},{"shop":"Магазин7","count":0,"unit":"шт."},{"shop":"Магазин1","count":0,"unit":"шт."},{"shop":"Магазин2","count":5000]
        )


последовательность магазинов меняется это видно с примеров.
 

подскажите в чем тут косяк?

Снова здравствуйте. Вернемся к этой выборке.
 В ходе работы нашлась какая то злостная «бажина» )) Проблема вот в чем… Если последним в строке значения стоит товар которого нет на складе то запрос валится. вот пример: // ошибка
[6] => Array
        (
            [product] => ХХХ
            [code] => ХХХ
            [groupname] => ХХХ
            [data] => [{"shop":"Магазин1","count":0,"unit":"шт."},{"shop":"Магазин2","count":50000,"unit":"шт."},{"shop":"Магазин3","count":50000,"unit":"шт."},{"shop":"Магазин4","count":50000,"unit":"шт."},{"shop":"Магазин5","count":50000,"unit":"шт."},{"shop":"Магазин6","count":50000,"unit":"шт."},{"shop":"Магазин7","count":]
        )
//а вот тут все хорошо
[9] => Array
        (
            [product] => ХХХ
            [code] => ХХХ
            [groupname] => ХХХ
            [data] => [{"shop":"Магазин3","count":50000,"unit":"шт."},{"shop":"Магазин4","count":50000,"unit":"шт."},{"shop":"Магазин5","count":50000,"unit":"шт."},{"shop":"Магазин6","count":50000,"unit":"шт."},{"shop":"Магазин7","count":0,"unit":"шт."},{"shop":"Магазин1","count":0,"unit":"шт."},{"shop":"Магазин2","count":5000]
        ) последовательность магазинов меняется это видно с примеров. подскажите в чем тут косяк?

Снова здравствуйте. Вернемся к этой выборке.
 В хо

походу проблема в количестве символов. строка тупо обрезается.


походу проблема в количестве символов. строка тупо

Судя по всему именно так.

а как решить?
 есть как бы параметр max_allowed_packet это в нем проблема?
 

или на стандартном хостинге ничего поделать нельзя?

а как решить?
 есть как бы параметр max_allowed_packet это в нем проблема? или на стандартном хостинге ничего поделать нельзя?

а как решить?
 есть как бы параметр max_allowed_pa

А при чем тут это, когда 99% проблема в типе данных колонки data. Какой тип данных там указан и какая длина?

А при чем тут это, когда 99% проблема в типе данны

нету такой колонки вообще ни в одной таблице… это собирается с нескольких значений

нету такой колонки вообще ни в одной таблице… это 

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

SET SESSION group_concat_max_len = 100000;

Но это путь в никуда ИМХО.

А, ну да, у вас же там конкатинация. Значит гуглите смену длины для таких операций. Вот один из ответов.
 Выполняйте запрос SET SESSION group_concat_max_len = 100000; Но это путь в никуда ИМХО.

А, ну да, у вас же там конкатинация. Значит гуглит

( понятно что в никуда… сегодня магазинов 7 а завтра 27… и все приехали…
 а как можно заменить такую выборку? посоветуйте пожалуйста

( понятно что в никуда… сегодня магазинов 7 а завт

Вообще как вариант просто взять себе облачный сервер и настроить на нем мускул под свои нужды, включая увеличение длины строки для concat-функций.
 

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

А можно и вовсе составить запрос так, чтобы на каждый продукт получилось N-число записей, в каждой из которых будет ID магазина, кол-во и т.п. Типа так:
 

select 
product_id, shop_id, count(*) as total
from products p
inner join shop_products sp on sp.product_id = p.id
inner join shops s on sp.shop_id = s
group by product_id, shop_id

И полученные данные уже обрабатываете в цикле, набивая конечный массив, так же как у нас TV-шки набиваются.

Вообще как вариант просто взять себе облачный сервер и настроить на нем мускул под свои нужды, включая увеличение длины строки для concat-функций. Но можно просто в цикле для всех магазинов наджоинить таблицу с формированием полей по маске, чтобы на выходе обработать полученные данные и набить в конечный запрос. А можно и вовсе составить запрос так, чтобы на каждый продукт получилось N-число записей, в каждой из которых будет ID магазина, кол-во и т.п. Типа так: select 
product_id, shop_id, count(*) as total
from products p
inner join shop_products sp on sp.product_id = p.id
inner join shops s on sp.shop_id = s
group by product_id, shop_id И полученные данные уже обрабатываете в цикле, набивая конечный массив, так же как у нас TV-шки набиваются.

Вообще как вариант просто взять себе облачный серв

а ничего что при последнем варианте будет так много обращений к БД? товаров может быть многооо

а ничего что при последнем варианте будет так мног

А где вы увидели много запросов к БД?
 

И, вам уже выбирать какой вариант больше нравится.

А где вы увидели много запросов к БД? И, вам уже выбирать какой вариант больше нравится.

А где вы увидели много запросов к БД? И, вам уже в

ага все я понял как работает 3 вариант…
 Спасибо Вам огромное Николай за столь результативную беседу.

ага все я понял как работает 3 вариант…
 Спасибо В

Пожалуйста!

У меня также есть проблема с подключением к сторонней базе. Делаю по инструкции, но все равно подключиться не получается((

<?php

define('MODX_CORE_PATH', '/path/to/revo/core/');
define('MODX_CONFIG_KEY','config');
require_once MODX_CORE_PATH . 'model/modx/modx.class.php';

// Criteria for foreign Database
$host = 'localhost';
$username = 'cc_test';
$password = '1q2wpass';
$dbname = 'cc_test';
$port = 3306;
$charset = 'utf-8';

$dsn = "mysql:host=$host;dbname=$dbname;port=$port;charset=$charset";
$xpdo = new xPDO($dsn, $username, $password);

// Test your connection
echo $o = ($xpdo->connect()) ? 'Есть контакт' : 'Нихрена не работает';

// Issue queries against the foreign database:
$results = $xpdo->query("SELECT id FROM some_table"); 
$recordCount = $results->rowCount();
print $recordCount;

У меня также есть проблема с подключением к сторонней базе. Делаю по инструкции, но все равно подключиться не получается(( Может кто подсказать?

У меня также есть проблема с подключением к сторон

Там ошибка. Должно быть $charset = 'utf8';, а не $charset = 'utf-8';

Там ошибка. Должно быть $charset = 'utf8';, а не $

Спасибо!!! заработало!!! :)

Искал по запросам в документации, толком не нашел, или искал криво что ли. Хорошо написал, понятно.

Искал по запросам в документации, толком не нашел,

Такое в документации не описано. А FIND_IN_SET() вообще в списке разрешенных методов не числится.

Такое в документации не описано. А FIND_IN_SET() в

Я обычно перепроверяю любое числовое значение уже на javascript: parseint()

Я обычно перепроверяю любое числовое значение уже 

Тоже верно, но вот это тоже на всякий случай учту для себя.

Тоже верно, но вот это тоже на всякий случай учту 

Apache

delete

excel

FIND_IN_SET

group_concat_max_len

InnerJoin

JOIN

LeftJoin

modx

modx evo

MSSQL

MySQL

mysql delete from two tables

nginx

where

xPDO

xpdoquery

вакансия

сложные запросы

# Николай Ланец aka Fi1osof
**Lead Fullstack Developer / Ментор / Евангелист современного JS‑стека**

---

## О себе

Я — не просто Senior Developer с 2007 года. Я — тот, кто строит продукты будущего на базе современных технологий и помогает другим подниматься на новую ступень айти-карьеры. Создатель и идейный вдохновитель freecode.academy — платформы, где обучение перестает быть скучной обязаловкой и становится настоящим технологическим приключением.

---

## Мой стек и философия разработки

Основу моего рабочего арсенала составляют:

- [Javascript](/technologies/ck1faudv928l40a89c4u9zxlm), [TypeScript](/technologies/ckcvwwx010kov08009u7y2rav)
- [React](/technologies/ck1fawbdw28v20a891k4wlh27), [Next.js](/technologies/ckg2jd4ffr9260730aog66dri)
- [Node.js](/technologies/ckt55wm59272831wo9gg9r8ynx), [GraphQL](/technologies/ck1fawhqk28vu0a8995lqvlsj), [Apollo Client](/technologies/ck2zdcg0vpvc10a89xjmhj1e0), [Nexus](/technologies/ckl2pbh0fxew60730ifl3zozp), [Prisma](/technologies/ck2zdbmi7pv8r0a89ntc2r0f1)
- DevOps: [Docker](/technologies/ck2y1x9lho9cy0a8953xzfjr8), [docker-compose](/technologies/ck2y1xqino9g80a89tsftxin2), [Caddy](/technologies/ck2zd8ovqpuyl0a8909748sbu), [Ubuntu](/technologies/cktu8giks3593911vmle30ztqxt)
- UI-инструменты: [Styled-Components](/technologies/ckk8myysio7lw0730csszvrdw), [Material-UI](/technologies/ckljv3jwd7bgf0730huxlko9n), [react-hook-form](/technologies/ckmx8cn9fmwrv073032e67722)
- Всё актуальное для разработки, CI/CD и автоматизации 
- Эксперименты с [WebGL](/technologies/ckan9anntc12f08003saqttrq), [A‑Frame](/technologies/ck2tgoiz8j37y0a89c7gxsrxc), [Three.js](/technologies/ck2tgns1nj32z0a89vmw7xw3f), [Unreal Engine 4](/technologies/ck7puswy6g4yb07004cdp6lbr), [Elixir](/technologies/ckn0olavrnnzi0730xs8rctn8), [C++](/technologies/cknin1y8lu2fr07307mskn2fn)

---

## Разработка и менторство

- Более 15 лет **hands-on** разработки и проджект‑менеджмента
- Главный разработчик и преподаватель платформы [freecode.academy](https://freecode.academy)
- **Ведущий менти-пул на платформе** — лично выращиваю новых звезд отрасли

### Мои менти и их результаты

| Менти (ФИО \| username)                               | Уровень | Рейтинг |
|-------------------------------------------------------|---------|---------|
| [Попов Дмитрий \| linklib](/users/cjoe890c706ji0d962oismzoe)           | 3       | 315     |
| [Сэт \| defk1lla](/users/ckzh1ita312978121imr9hpqfc64)                 | 3       | 670     |
| [Ксения Матвеева \| Ксения Матвеева](/users/ckz4awwlh10627771imr4wvpb7cj) | 2       | 0       |
| [Михаил \| Balamut](/users/cl1npq92z10258261vmbq0w6vfh2)               | 2       | 0       |
| [Ольга Ершова \| angel](/users/cjoe87zhk00250d969arluevk)              | 1       | 0       |
| [Егор \| rogedkone](/users/cksrdp47d3308261ijuq7wfrxdl)                | 3       | 0       |
| [Roman \| Roman43](/users/ckur2qtt64721431xoboyjcte52)                 | 2       | 0       |
| [Бут Игорь \| santiment](/users/ckttd9x20893951vmltb8t7sha)            | 1       | 0       |

**Доношу простую мысль: успех — это не магия, это осознанная системная работа и кайф от роста.**
Мои менти выводят решения в продакшн, пробиваются в лидеры рейтингов — кому не хочется быть в такой команде?

---

## Почему я?

- **Прокачаю проект** — от TDD архитектуры до культурного CI/CD
- **Вдохновлю команду** — и научу мыслить по‑новому, где ИИ — напарник, а не угроза
- **Выращу мидлов и сеньоров** — доказано платформой и рейтингами моих учеников

**Сила — в сообществе, а техника — это просто смазка для ваших побед. Присоединяйтесь!**

---

## Контакты

- 📧 [n.lanets@freecode.academy](mailto:n.lanets@freecode.academy)
- Телеграм: [@freecode_academy](https://t.me/freecode_academy)

---

**P.S.**: Мой опыт — ваш драйвер роста. Хотите в команду? Ищете менторство? Пишите. Растём вместе!