За последние 24 часа нас посетили 20256 программистов и 1084 робота. Сейчас ищут 792 программиста ...

Выбрать данные из трех таблиц с выводом столбцами (many to many)

Тема в разделе "MySQL", создана пользователем voron121, 20 янв 2021.

  1. voron121

    voron121 Активный пользователь

    С нами с:
    18 ноя 2016
    Сообщения:
    29
    Симпатии:
    1
    Доброго времени суток, уважаемые форумчане! Столкнулся, казалось бы, с простецкой задачей, но втупил и не могу понять как составить правильно запрос. Прошу помощи.

    Описание проблемы: есть 4 таблицы, между которыми есть связь через таблицу product_to_options:
    products :
    Код (Text):
    1. CREATE TABLE `products` (
    2.   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    3.   `name` VARCHAR(250) CHARACTER SET utf8 NOT NULL,
    4.   PRIMARY KEY (`id`)
    5. ) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
    product_to_options:
    Код (Text):
    1. CREATE TABLE `product_to_options` (
    2.   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    3.   `product_id` INT(10) UNSIGNED NOT NULL,
    4.   `option_value_id` INT(10) UNSIGNED NOT NULL,
    5.   `option_field_id` INT(10) UNSIGNED NOT NULL,
    6.   PRIMARY KEY (`id`),
    7.   UNIQUE KEY `product_id_2` (`product_id`,`option_value_id`),
    8.   KEY `product_id` (`product_id`)
    9. ) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

    options_value
    Код (Text):
    1. CREATE TABLE `options_value` (
    2.   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    3.   `name` VARCHAR(30) COLLATE utf8_bin NOT NULL,
    4.   PRIMARY KEY (`id`)
    5. ) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
    options_fields
    Код (Text):
    1. CREATE TABLE `options_fields` (
    2.   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    3.   `name` VARCHAR(30) COLLATE utf8_bin NOT NULL,
    4.   PRIMARY KEY (`id`)
    5. ) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
    В таблице product_to_options хранится связь товара к ид опции товара и к ид значения опции товара. Например:

    Код (Text):
    1. id    |   product_id   |   option_value_id   |   option_value_field_id
    2. 1       1                      10                             12
    3. 2       1                      5                               9
    4. ...

    Нужно выбрать все товары и опции к ним с параметрами, но при этом данные должны быть выведенны в таком виде:

    product_name | options_fields_name_1 | options_value_1 | options_fields_name_2 | options_value_2 |

    Пробую делать вот такой запрос:
    Код (Text):
    1. SELECT     products.id,
    2.     products.name,
    3.     options_value.name,
    4.     options_value.name
    5. FROM products
    6.     JOIN product_to_options ON product_to_options.product_id = products.id
    7.     JOIN options_value ON product_to_options.option_value_id = options_value.id
    8.     JOIN options_fields ON product_to_options.option_field_id = options_fields.id
    9. ;
    Получаю закономерный результат вида:
    Код (Text):
    1. id   |   name   |   name   |   name  
    2. 1      Товар 1     Цвет        красный
    3. 1      Товар 1     Вес         100кг
    4. 2      Товар 2     Цвет        синий
    5. 2      Товар 2     Вес         10кг
    6. ....
    Вопрос: как мне составить запрос, чтобы убрать дубли для товаров и для дублей вывести значение для опций в каждом столбце? И возможно ли это?
    Понятно что дубли можно убрать при помощи GROUP BY но вот как быть с выводом значения опций в столбцах а не в строках - не понтно :(
    то есть должно быть так:
    Код (Text):
    1.  
    2. id   |   name   |   op_name1   |   opt_val_1      |    op_name2   |    opt_val_2
    3. 1      Товар 1     Цвет        красный                 Вес                   100кг
    4. 2      Товар 2     Цвет        синий                     Вес                   10кг
    5. ...
    Используется MySQL 5.7
     
  2. Drunkenmunky

    Drunkenmunky Активный пользователь

    С нами с:
    12 авг 2020
    Сообщения:
    1.476
    Симпатии:
    281
    Разбейте задачу на части - объедините только две таблицы, удостоверившись в желаемом результате.
    Можно создать из него представление.
    И так со всеми по очереди.
    Потом объедините представления.
     
  3. artoodetoo

    artoodetoo Суперстар
    Команда форума Модератор

    С нами с:
    11 июн 2010
    Сообщения:
    11.068
    Симпатии:
    1.231
    Адрес:
    там-сям
    так убрать или что-то делать?
    если между таблицами отношение один ко многим или многие ко многим, то "дубли" это естественная картина. принудительно делать их недублями значит что-то сломать.
    в таких случаях надо сначала решить для себя что конкретно ты хочешь получить: первую запись из возможных, сумму по столцу и т.д
    --- Добавлено ---
    иногда "дубли" надо оставить как есть, а обрабатывать их уже в Представлении, например показывать иерархию. или превращать столбцы в колонки

    реляционные СУБД не приспособлены для вывода записей "по горизонтали"
    только для фиксированного набора колонок можно сочинить хитрый запрос с несколькими джойнами к той же таблице и доп. условиями
    Код (Text):
    1.  
    2. SELECT master.id, master.title, MIN(details1.amount) AS color, MIN(details2.amount) AS weight
    3. FROM master
    4. LEFT JOIN details AS details1 ON details1.master_id = master.id AND details1.type='color'
    5. LEFT JOIN details AS details2 ON details2.master_id = master.id AND details2.type='weight'
    6. GROUP BY master.id
     
  4. mkramer

    mkramer Суперстар
    Команда форума Модератор

    С нами с:
    20 июн 2012
    Сообщения:
    8.548
    Симпатии:
    1.754
    Сделать, как поступают фреймворки - отдельно запросить товары, отдельно для них опции, потом распределить на стороне PHP