Mysql Выбрать товары и цены одним запросом с группировкой по товару

Slon747

Новичок
Есть 3 таблицы: товары, типы цен и цены.
Код:
CREATE TABLE products (
  uuid varchar(36) NOT NULL DEFAULT '',
  name varchar(100),
  PRIMARY KEY (uuid)
)

CREATE TABLE pricetypes (
  uuid varchar(36) NOT NULL DEFAULT '',
  name varchar(50),
  PRIMARY KEY (uuid)
)

CREATE TABLE prices (
  pricetype varchar(36),
  product varchar(36),
  price double DEFAULT 0
)

ALTER TABLE prices
ADD UNIQUE INDEX UK_prices (pricetype, product);
У каждого товара может быть несколько цен (разные типы цен), а может не быть ни одной.
Требуется получить все товары и цены примерно в таком виде:
Код:
-Товар1
     -ТипЦен1 Цена
     -ТипЦен2 Цена
     -ТипЦен3 Цена
-Товар2
     -ТипЦен1 Цена
     -ТипЦен2 Цена
Т.е. сначала товар, потом детализация по ценам этого товара.
Как правильно это сделать, чтобы в цикле по выборке товаров не вызывать запрос по ценам?
Многократное левое соединение товаров с ценами для каждого типа цен мне совсем не нравится.
Есть ли элегантное решение?
 
Последнее редактирование:

Valick

Новичок
Как правильно это сделать, чтобы в цикле по выборке товаров не вызывать запрос по ценам?
Ой как приятно слышать правильное направление мыслей.
Многократное левое соединение товаров с ценами для каждого типа цен мне совсем не нравится.
Пусть соединения вас не пугают, при грамотной нормализации таблиц они вовсе не страшные.

SQL:
SELECT pt.name, ts.name, ps.price
FROM prices AS ps
LEFT JOIN pricetypes AS ts ON ts.uuid = ps.pricetype
LEFT JOIN product AS pt ON pt.uuid = ps.product
ORDER BY pt.uuid, ts.uuid
Товары не имеющие цены в выборке будут отсутвовать.
Дальше уже срадствами РНР фомируете тебуемый вид.

P.S. почему uuid, а не обычный целочисленный id?
 

Slon747

Новичок
Пусть соединения вас не пугают, при грамотной нормализации таблиц они вовсе не страшные.
В какой-то БД тип цен может быть 1, а в какой-то и 15, зависит от желаний пользователей.

SQL:
SELECT pt.name, ts.name, ps.price
FROM prices AS ps
LEFT JOIN pricetypes AS ts ON ts.uuid = ps.pricetype
LEFT JOIN product AS pt ON pt.uuid = ps.product
ORDER BY pt.uuid, ts.uuid
Товары не имеющие цены в выборке будут отсутвовать.
Дальше уже срадствами РНР фомируете тебуемый вид.
Да с этим все понятно. Я просто надеялся, вдруг MySQL+PHP позволяет создавать выборку, которую можно обходить с группировками (как в 1С). Значит, не может.

P.S. почему uuid, а не обычный целочисленный id?
Справочники будут заполняться на разных устройствах в разных БД, данные которых будут аккумулироваться воедино в MySQL.

А если создать временную таблицу, выгрузить в нее цены, проиндексировать и в цикле выборки товаров искать цены? Тогда ведь все будет происходить в ОЗУ. Есть ли смысл?

P.S. Не знаю почему, но при тестировании плохого варианта (запрос цен в цикле) при кол-ве товаров 1000 и цен 3*1000 скрипт выполняется настолько быстро (0.069 сек.) что даже подозрительно. Замерял через microtime(true).
Хотя, возможно это фокусы "Open Server" и в реальной работе будет не так.
 
Последнее редактирование:

Valick

Новичок
В какой-то БД тип цен может быть 1, а в какой-то и 15, зависит от желаний пользователей.
В данном случае вполне можно использовать выборку типов цен отдельным запросом. Не надо сходиить с ума и стараться всё запихать в один запрос ради того что бы можно было сказать у меня один запрос.

Значит, не может.
MySQL вполне себе умеет группировку и "огого-как умеет", была бы только причина использовать GROUP BY.

Не знаю почему, но при тестировании плохого варианта (запрос цен в цикле) при кол-ве товаров 1000 и цен 3*1000 скрипт выполняется настолько быстро (0.069 сек.) что даже подозрительно.
1000 товаров - это кошкины слёзы. Протестируйте хотя бы с 50000. И не забывайте, что в интернат магазине одновременно может находиться огромное количество пользователей.
Ну а когда дойдёте до пагинации выборки, останеться только удавиться с запросами в цикле, когда-нибудь они обязательно "выстрелят вам в ногу".
Так что учитесь грамтно организовывать стркутуру таблиц, учите правила нормализации и будет всем хорошо.
 

Slon747

Новичок
MySQL вполне себе умеет группировку и "огого-как умеет", была бы только причина использовать GROUP BY.
Под группировкой я имел в виду визуальное представление, типа дерева.
Тут GROUP BY, конечно, не поможет.

1000 товаров - это кошкины слёзы. Протестируйте хотя бы с 50000. И не забывайте, что в интернат магазине одновременно может находиться огромное количество пользователей.
Ну а когда дойдёте до пагинации выборки, останеться только удавиться с запросами в цикле, когда-нибудь они обязательно "выстрелят вам в ногу".
Это не интернет-магазин. Специфика приложения не подразумевает 50 тыс товаров, 10 тыс. товаров вполне могут быть.
И данная логика не должна выполняться часто, это не для вывода прайса, а для обмена данных.

Так что учитесь грамтно организовывать стркутуру таблиц, учите правила нормализации и будет всем хорошо.
Да ученый я. Просто у каждой СУБД свои возможности.
Но спасибо.
 

WMix

герр M:)ller
Партнер клуба
если взять нормальный orm он все сделает, если обходиться голым php то чтоб сложить 2 выборки используют foreach и ключи массива

тебе надо написать 2 запроса опираясь на теже данные.
допустим список всех товаров с ценами на букву a
SQL:
#1 цены

SELECT ps.product, ts.name as type, ps.price
FROM prices AS ps
LEFT JOIN pricetypes AS ts ON ts.uuid = ps.pricetype
LEFT JOIN product AS pt ON pt.uuid = ps.product
where pt.name like 'a%';
собирая массив сгруппируй записи по id продукта
PHP:
$prices = [];
while($row = $db->next()){ //не знаю чем пользуешся, придумал
    $prices[$row['product']][] = $row; //группируем
}
SQL:
# 2 товары
select uuid, name  from product where name like 'a%';
PHP:
$products = [];
while($row = $db->next()){
  $row['prices'] = $prices[$rpw['uuid']];  // ну и маппим
  $products[] = $row;
}
print_r($products);
 

Slon747

Новичок
Интересно. Спасибо.
Т.е. сначала получаем товары со всеми ценами. В выборку не попадут товары с отсутствующими ценами.
Далее группируем (о таком в php не знал).
Потом обходим уже выборку всех товаров и привязываем цены.
А массив $prices он индексированный? Глупый вопрос, но не знаю таких тонкостей в php. Там же в цикле будет поиск в большом массиве цен.
 

Valick

Новичок
WMix, ты же понимаешь, что твоя рекомендация не выдерживает никакой критики?
 

Valick

Новичок
давай, критикуй
Ты делаешь соединение с таблицей продуктов для выборки по условию, но не забираешь имена товаров, а забираешь эти же имена товаров отдельным запросом? Почему я вообще об этом говорю?
 

Valick

Новичок
а если там не только имя
а если только имя? зачем вообще говорить об "а если"? у меня тоже достаточно бурная фантазия, и я могу тут наворотить кода на 18 страниц, но есть конкретный вопрос. И даже конкретный пример вывода
Код:
-Товар1
     -ТипЦен1 Цена
     -ТипЦен2 Цена
     -ТипЦен3 Цена
-Товар2
     -ТипЦен1 Цена
     -ТипЦен2 Цена
P.S
Не надо сходиить с ума и стараться всё запихать в один запрос ради того что бы можно было сказать у меня один запрос.
 
Последнее редактирование:

Valick

Новичок
PHP:
$query = "SELECT pt.name AS productName
            , ts.name AS priceName
            , ps.price
                FROM prices AS ps
                LEFT JOIN pricetypes AS ts ON ts.uuid = ps.pricetype
                LEFT JOIN product AS pt ON pt.uuid = ps.product
                ORDER BY pt.uuid, ts.uuid";

$resource = $mysql->query($query);
$result = [];
if ($resource) {
    while ($row = $resource->fetch_assoc()) {
        $result[$row['productName']][$row['priceName']] = $row['price'];
    }
}

print_r($result);
 

WMix

герр M:)ller
Партнер клуба
те про сущности ты не слышал, про размеры hashtable тоже ничего, пагинации это не поддается, да и не надо, размер трафика между базой и fpm не интересен.. ну чтож тоже решение, sehr gut!
у меня тоже достаточно бурная фантазия, и я могу тут наворотить кода на 18 страниц, но есть конкретный вопрос.
я этого говна столько написал, что уже не задумываюсь о подходе. есть набор есть сущность, просто маппинг, попробуй, это работает всегда

но есть конкретный вопрос.
Шерлок Холмс и доктор Ватсон летели на воздушном шаре. Но был такой сильный туман, что они заблудились. Но вот порыв ветра, облака рассеиваются, Шерлок Холмс и доктор Ватсон приземляются и видят человека, пасущего овец.
— Скажите, любезнейший, а где мы находимся? — спрашивает доктор Ватсон.
Подумав, пастух отвечает:
— В гондоле воздушного шара.
Новый порыв ветра подхватывает шар, и Шерлок Холмс с доктором Ватсоном летят дальше.
— Ох, уж эти программисты! — восклицает Холмс.
— Постойте, Холмс, но как вы догадались?
— Это элементарно, Ватсон! Он ответил, подумав. И дал совершенно верный, но совершенно бесполезный ответ.
 
Последнее редактирование:

WMix

герр M:)ller
Партнер клуба
Там же в цикле будет поиск в большом массиве цен.
что такое большой? сколько товаров на странице? 100? и допустим по 10 цен на товар... много. но не критично, если больше, то вероятнее всего чтото делаешь не так

hashtable на 1000 строк, не более 3х тактов процессора обычно говорят о сложности O(1)
 

Valick

Новичок
WMix, если ты на форуме именно для того что бы давать "совершенно верные, но совершенно бесполезные ответы" исключительно ради того, что бы показать свою крутость, то кто я такой что бы тебе мешать?)
 

WMix

герр M:)ller
Партнер клуба
не вижу смысла спорить, ну чес слово, ну в какой книжке ты прочитал про такие структуры?
$result[$row['productName']][$row['priceName']] = $row['price'];
ну правда, о чем это? как с этим работать? попробуй ORM ты поймешь, что весь мир кружится на одном и томже алгоритме. в orm на месте hashtable используется SplObjectStorage что еще немного тяжелее, но это спички.
 
Сверху