Меню
Что нового?

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

  • Автор темы Автор темы Slon747
  • Дата начала Дата начала

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 Цена
Т.е. сначала товар, потом детализация по ценам этого товара.
Как правильно это сделать, чтобы в цикле по выборке товаров не вызывать запрос по ценам?
Многократное левое соединение товаров с ценами для каждого типа цен мне совсем не нравится.
Есть ли элегантное решение?
 
Последнее редактирование:
Slon747 написал(а):
Как правильно это сделать, чтобы в цикле по выборке товаров не вызывать запрос по ценам?
Нажмите для раскрытия...
Ой как приятно слышать правильное направление мыслей.
Slon747 написал(а):
Многократное левое соединение товаров с ценами для каждого типа цен мне совсем не нравится.
Нажмите для раскрытия...
Пусть соединения вас не пугают, при грамотной нормализации таблиц они вовсе не страшные.

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?
 
Valick написал(а):
Пусть соединения вас не пугают, при грамотной нормализации таблиц они вовсе не страшные.
Нажмите для раскрытия...
В какой-то БД тип цен может быть 1, а в какой-то и 15, зависит от желаний пользователей.

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

Товары не имеющие цены в выборке будут отсутвовать.
Дальше уже срадствами РНР фомируете тебуемый вид.
Нажмите для раскрытия...
Да с этим все понятно. Я просто надеялся, вдруг MySQL+PHP позволяет создавать выборку, которую можно обходить с группировками (как в 1С). Значит, не может.

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

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

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

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

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

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

Valick написал(а):
Так что учитесь грамтно организовывать стркутуру таблиц, учите правила нормализации и будет всем хорошо.
Нажмите для раскрытия...
Да ученый я. Просто у каждой СУБД свои возможности.
Но спасибо.
 
если взять нормальный 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);
 

Интересно. Спасибо.
Т.е. сначала получаем товары со всеми ценами. В выборку не попадут товары с отсутствующими ценами.
Далее группируем (о таком в php не знал).
Потом обходим уже выборку всех товаров и привязываем цены.
А массив $prices он индексированный? Глупый вопрос, но не знаю таких тонкостей в php. Там же в цикле будет поиск в большом массиве цен.
 
WMix, ты же понимаешь, что твоя рекомендация не выдерживает никакой критики?
 
WMix написал(а):
а если там не только имя
Нажмите для раскрытия...
а если только имя? зачем вообще говорить об "а если"? у меня тоже достаточно бурная фантазия, и я могу тут наворотить кода на 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);
 
те про сущности ты не слышал, про размеры hashtable тоже ничего, пагинации это не поддается, да и не надо, размер трафика между базой и fpm не интересен.. ну чтож тоже решение, sehr gut!
Valick написал(а):
у меня тоже достаточно бурная фантазия, и я могу тут наворотить кода на 18 страниц, но есть конкретный вопрос.
Нажмите для раскрытия...
я этого говна столько написал, что уже не задумываюсь о подходе. есть набор есть сущность, просто маппинг, попробуй, это работает всегда

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

hashtable на 1000 строк, не более 3х тактов процессора обычно говорят о сложности O(1)
 
WMix, если ты на форуме именно для того что бы давать "совершенно верные, но совершенно бесполезные ответы" исключительно ради того, что бы показать свою крутость, то кто я такой что бы тебе мешать?)
 
не вижу смысла спорить, ну чес слово, ну в какой книжке ты прочитал про такие структуры?
Valick написал(а):
$result[$row['productName']][$row['priceName']] = $row['price'];
Нажмите для раскрытия...
ну правда, о чем это? как с этим работать? попробуй ORM ты поймешь, что весь мир кружится на одном и томже алгоритме. в orm на месте hashtable используется SplObjectStorage что еще немного тяжелее, но это спички.
 
Войдите или зарегистрируйтесь для ответа.
Сверху