Создание комбинаций таблицы/столбцов с помощью SQL Query или Laravel SQL Query Builder

У меня есть существующая схема для вариантов продуктов.

Я хочу создать комбинацию каждого времени производства, количества и вариантов вариации.

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

группы_таблиц

+------------+
| id | title |
+----+-------+
| 1  | rug   |
+----+-------+

стол_дней

+----+----------+------+
| id | group_id | day  |
+----+----------+------+
| 1  | 1        | 1    |
| 2  | 1        | 2    |
| 3  | 1        | 3    |
+----+----------+------+

table_quantities

+----+----------+-----------+
| id | group_id | quantity  |
+----+----------+-----------+
| 1  | 1        | 100       |
| 2  | 1        | 200       |
| 3  | 1        | 300       |
| 4  | 1        | 400       |
+----+----------+-----------+

table_attributes

+----+----------+-----------+
| id | group_id | title     |
+----+----------+-----------+
| 1  | 1        | Color     |
| 2  | 1        | Size      |
+----+----------+-----------+

table_attribute_values

+----+----------+--------------+--------+
| id | group_id | attribute_id | title  |
+----+----------+--------------+--------+
| 1  | 1        | 1            | Red    |
| 2  | 1        | 1            | Yellow |
| 3  | 1        | 1            | Black  |
| 4  | 1        | 2            | Small  |
| 5  | 1        | 2            | Medium |
+----+----------+--------------+--------+

Я подготовил примерную схему. Однако я не получаю желаемого результата.

скрипт SQL

Вот что я сделал:

SELECT
       GROUP_CONCAT(DISTINCT days_group) as days_list,
       GROUP_CONCAT(DISTINCT quantities_group SEPARATOR ',') as quantities_list,
       GROUP_CONCAT(DISTINCT attribute_values_group SEPARATOR ',') as attribute_values_list
FROM
    table_groups
    LEFT JOIN (
            SELECT days.day, days.group_id,
                   GROUP_CONCAT(days.day) as days_group
            FROM table_days days GROUP BY days.id
        ) joindays ON joindays.group_id = table_groups.id

    LEFT JOIN (
            SELECT quantities.quantity, quantities.group_id,
                   GROUP_CONCAT(quantities.quantity) as quantities_group
            FROM table_quantities quantities GROUP BY quantities.id
        ) joinquantities ON joinquantities.group_id = table_groups.id

    LEFT JOIN table_attributes attributes ON attributes.group_id = table_groups.id

    LEFT JOIN (
            SELECT attribute_id, group_id,
                   GROUP_CONCAT(attribute_values.title) as attribute_values_group
            FROM table_attribute_values attribute_values
            GROUP BY attribute_values.attribute_id, attribute_values.id
        ) joinattributevalues ON joinattributevalues.attribute_id = attributes.id

GROUP BY joinattributevalues.attribute_id;

Результаты запроса:

+---------------+-----------+-----------------+-----------------------+
| group_id      | days_list | quantities_list | attribute_values_list |
+---------------+-----------+-----------------+-----------------------+
| 1             | 1,2,3     | 100,200,300,400 | Red,Yellow,Black      |
| 2             | 1,2,3     | 100,200,300,400 | Small,Medium          |
+---------------+-----------+-----------------+-----------------------+

Правильный результат, который я хочу, должен быть следующим. Вы можете помочь с этим?

+-----------+---------------------+--------+
| group_id  | combinations        | price  |
+-----------+---------------------+--------+
| 1         | 1-100-Red-Small     |        |
+-----------+---------------------+--------+
| 1         | 1-100-Red-Medium    |        |
+-----------+---------------------+--------+
| 1         | 1-100-Yellow-Small  |        |
+-----------+---------------------+--------+
| 1         | 1-100-Yellow-Medium |        |
+-----------+---------------------+--------+
| 1         | 1-100-Black-Small   |        |
+-----------+---------------------+--------+
| 1         | 1-100-Black-Medium  |        |
+-----------+---------------------+--------+
| 1         | 1-200-Red-Small     |        |
+-----------+---------------------+--------+
| 1         | 1-200-Red-Medium    |        |
+-----------+---------------------+--------+
| 1         | 1-200-Yellow-Small  |        |
+-----------+---------------------+--------+
| 1         | 1-200-Yellow-Medium |        |
+-----------+---------------------+--------+
| 1         | 1-200-Black-Small   |        |
+-----------+---------------------+--------+
| 1         | 1-200-Black-Medium  |        |
+-----------+---------------------+--------+
| 1         | 1-300-Red-Small     |        |
+-----------+---------------------+--------+
| 1         | 1-300-Red-Medium    |        |
+-----------+---------------------+--------+
| 1         | 1-300-Yellow-Small  |        |
+-----------+---------------------+--------+
| 1         | 1-300-Yellow-Medium |        |
+-----------+---------------------+--------+
| 1         | 1-300-Black-Small   |        |
+-----------+---------------------+--------+
| 1         | 1-300-Black-Medium  |        |
+-----------+---------------------+--------+
| 1         | 1-400-Red-Small     |        |
+-----------+---------------------+--------+
| 1         | 1-400-Red-Medium    |        |
+-----------+---------------------+--------+
| 1         | 1-400-Yellow-Small  |        |
+-----------+---------------------+--------+
| 1         | 1-400-Yellow-Medium |        |
+-----------+---------------------+--------+
| 1         | 1-400-Black-Small   |        |
+-----------+---------------------+--------+
| 1         | 1-400-Black-Medium  |        |
+-----------+---------------------+--------+
| 1         | 2-100-Red-Small     |        |
+-----------+---------------------+--------+
| 1         | 2-100-Red-Medium    |        |
+-----------+---------------------+--------+
| 1         | 2-100-Yellow-Small  |        |
+-----------+---------------------+--------+
| 1         | 2-100-Yellow-Medium |        |
+-----------+---------------------+--------+
| 1         | 2-100-Black-Small   |        |
+-----------+---------------------+--------+
| 1         | 2-100-Black-Medium  |        |
+-----------+---------------------+--------+
| 1         | 2-200-Red-Small     |        |
+-----------+---------------------+--------+
| 1         | 2-200-Red-Medium    |        |
+-----------+---------------------+--------+
| 1         | 2-200-Yellow-Small  |        |
+-----------+---------------------+--------+
| 1         | 2-200-Yellow-Medium |        |
+-----------+---------------------+--------+
| 1         | 2-200-Black-Small   |        |
+-----------+---------------------+--------+
| 1         | 2-200-Black-Medium  |        |
+-----------+---------------------+--------+
| 1         | 2-300-Red-Small     |        |
+-----------+---------------------+--------+
| 1         | 2-300-Red-Medium    |        |
+-----------+---------------------+--------+
| 1         | 2-300-Yellow-Small  |        |
+-----------+---------------------+--------+
| 1         | 2-300-Yellow-Medium |        |
+-----------+---------------------+--------+
| 1         | 2-300-Black-Small   |        |
+-----------+---------------------+--------+
| 1         | 2-300-Black-Medium  |        |
+-----------+---------------------+--------+
| 1         | 2-400-Red-Small     |        |
+-----------+---------------------+--------+
| 1         | 2-400-Red-Medium    |        |
+-----------+---------------------+--------+
| 1         | 2-400-Yellow-Small  |        |
+-----------+---------------------+--------+
| 1         | 2-400-Yellow-Medium |        |
+-----------+---------------------+--------+
| 1         | 2-400-Black-Small   |        |
+-----------+---------------------+--------+
| 1         | 2-400-Black-Medium  |        |
+-----------+---------------------+--------+
| 1         | 3-100-Red-Small     |        |
+-----------+---------------------+--------+
| 1         | 3-100-Red-Medium    |        |
+-----------+---------------------+--------+
| 1         | 3-100-Yellow-Small  |        |
+-----------+---------------------+--------+
| 1         | 3-100-Yellow-Medium |        |
+-----------+---------------------+--------+
| 1         | 3-100-Black-Small   |        |
+-----------+---------------------+--------+
| 1         | 3-100-Black-Medium  |        |
+-----------+---------------------+--------+
| 1         | 3-200-Red-Small     |        |
+-----------+---------------------+--------+
| 1         | 3-200-Red-Medium    |        |
+-----------+---------------------+--------+
| 1         | 3-200-Yellow-Small  |        |
+-----------+---------------------+--------+
| 1         | 3-200-Yellow-Medium |        |
+-----------+---------------------+--------+
| 1         | 3-200-Black-Small   |        |
+-----------+---------------------+--------+
| 1         | 3-200-Black-Medium  |        |
+-----------+---------------------+--------+
| 1         | 3-300-Red-Small     |        |
+-----------+---------------------+--------+
| 1         | 3-300-Red-Medium    |        |
+-----------+---------------------+--------+
| 1         | 3-300-Yellow-Small  |        |
+-----------+---------------------+--------+
| 1         | 3-300-Yellow-Medium |        |
+-----------+---------------------+--------+
| 1         | 3-300-Black-Small   |        |
+-----------+---------------------+--------+
| 1         | 3-300-Black-Medium  |        |
+-----------+---------------------+--------+
| 1         | 3-400-Red-Small     |        |
+-----------+---------------------+--------+
| 1         | 3-400-Red-Medium    |        |
+-----------+---------------------+--------+
| 1         | 3-400-Yellow-Small  |        |
+-----------+---------------------+--------+
| 1         | 3-400-Yellow-Medium |        |
+-----------+---------------------+--------+
| 1         | 3-400-Black-Small   |        |
+-----------+---------------------+--------+
| 1         | 3-400-Black-Medium  |        |
+-----------+---------------------+--------+

Примечание. Количество групп, атрибутов и значений атрибутов не ограничено. Результат примера может быть примерно таким:

Attributes:

+-------+------+-------+--------+
| Color | Size | Model | Gender |
+-------+------+-------+--------+

Combinations:

+------------------------------+
| 1-100-Red-Small-Model 1-Male |
+------------------------------+
| 1-100-Red-Small-Model 2-Male |
+------------------------------+

Не обязательно делать это с SQL-запросом. Мы также можем сделать это с помощью метода Laravel Query Builder.

Заранее спасибо за помощь.

Проверьте образец скрипта SQL


mysql sql laravel sku combinations
person Mert A.    schedule 14.11.2020    source источник
comment
Требуется ли MySQL 5.6 или у вас есть доступ к MySQL 8.0 и/или другим БД?   -  person CoffeeNeedCoffee    schedule 17.11.2020
comment
@CoffeeNeedCoffee Мой первый выбор — mysql 5.7, но при необходимости я буду использовать его в mysql 8. Это также можно сделать с помощью методов построения запросов Laravel. Я не спал много дней. Для меня было бы роскошью выбрать версию mysql :)   -  person Mert A.    schedule 17.11.2020
comment
@CoffeeNeedCoffee будет полезно изучить и этот вопрос stackoverflow.com/q/64851724/6320082   -  person Mert A.    schedule 17.11.2020

Ответы (1)


arrow_upward
3
arrow_downward

Это должно сделать это. Не будет работать в mysql 5.7, потому что рекурсивные CTE были включены позже, но это позволит вам иметь переменное количество атрибутов и attribute_values ​​для group_id. Скрипка находится здесь.

with recursive allAtts as (
  /* Get our attribute list, and format it if we want; concat(a.title, ':', v.title) looks quite nice */
  SELECT 
    att.group_id,
    att.id,
    CONCAT(v.title) as attDesc,
    dense_rank() over (partition by att.group_id order by att.id) as attRank
FROM table_attributes att 
INNER JOIN table_attribute_values v 
    ON v.group_id = att.group_id 
    AND v.attribute_id = att.id 
 ),
 cte as (
 /* Recursively build our attribute list, assuming ranks are sequential and we properly linked our group_ids */
    select group_id, id, attDesc, attRank from allAtts WHERE attRank = 1
   
         union all 
   
    select 
        allAtts.group_id, 
        allAtts.id, 
        concat_ws('-', cte.attDesc, allAtts.attDesc) as attDesc,
        allAtts.attRank
   from cte 
   join allAtts ON allAtts.attRank = cte.attRank +1
      AND cte.group_id = allAtts.group_id
)
   
/* Our actual select statement, which RIGHT JOINs against the table_groups 
   so we don't lose entries w/o attributes */   
select 
    grp.id,
    concat_ws('-', d.day, qty.quantity, cte.attDesc) as combinations
from cte 
inner join (select group_id, max(attRank) as attID
            from cte
            group by group_id) m on cte.group_id = m.group_id and m.attID = cte.attrank
RIGHT JOIN table_groups grp ON grp.id = cte.group_id 
LEFT JOIN table_days d on grp.id = d.group_id
LEFT JOIN table_quantities qty on grp.id = qty.group_id;
person CoffeeNeedCoffee    schedule 16.11.2020
comment
большое спасибо! теперь я обновлю версию mysql. и тогда я буду тестировать его прямо сейчас. - person Mert A.; 17.11.2020
comment
я должен тебе много. ты обалденный! - person Mert A.; 17.11.2020