使用SQL Query或Laravel SQL Query Builder创建表/列组合

Question

我有一个现有的产品变型方案。

我想创建每个生产时间，数量和变化选项的组合。

我将通过访问产品的数量，生产时间，差异和差异选项来创建选择表单。

table_groups

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

table_days

+----+----------+------+
| 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查询生成器方法来执行此操作。

在此先感谢您的帮助。

检查样本SQL Fiddle

Answer 1

这应该做。在mysql
5.7中将不起作用，因为直到后来才包含递归CTE，但这将使您每个group_id拥有可变数量的属性和attribute_values。小提琴在这里。

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;