mysql - 联表中作为附加值包含的值的联表
问题描述
我遇到了一个问题,我既无法解决也无法在文档和问题中找到解决方案。简而言之,我正在构建一个电子商务解决方案,其中有以下模型:Product、Variant、Attribute、AttributeDictionary
很快,每当用户创建字典类型的属性时,它的答案就会保存在 AttributeDictionary 模型中。每个变体都有许多属性。
让我们介绍一下数据库模式:
mysql> DESC variants;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| quantity | int(11) | YES | | NULL | |
+----------+------------------+------+-----+---------+----------------+
mysql> DESC variants_attributes;
+----------------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| variants_id | int(10) unsigned | YES | | NULL | |
| attributes_id | int(10) unsigned | YES | | NULL | |
| attributes_dictionaries_id | int(10) unsigned | YES | MUL | NULL | |
| value | varchar(128) | YES | | NULL | |
+----------------------------+------------------+------+-----+---------+----------------+
mysql> DESC attributes;
+------------+----------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(128) | YES | | NULL | |
| field_type | enum('text','number','dictionary','boolean') | YES | | NULL | |
| unit | varchar(10) | YES | | NULL | |
| required | tinyint(1) | YES | | 0 | |
+------------+----------------------------------------------+------+-----+---------+----------------+
mysql> DESC attributes_dictionaries;
+---------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| attributes_id | int(10) unsigned | YES | MUL | NULL | |
| value | varchar(255) | YES | | NULL | |
+---------------+------------------+------+-----+---------+----------------+
和型号:
变体模型
public static relationMappings = {
attributes: {
relation: Model.ManyToManyRelation,
modelClass: 'Attribute',
join: {
from: 'variants.id',
through: {
from: 'variants_attributes.variants_id',
to: 'variants_attributes.attributes_id',
extra: {
value: 'value',
attributes_dictionaries_id: 'attributes_dictionaries_id',
},
},
to: 'attributes.id',
},
},
};
产品型号:
public static relationMappings = {
attributes: {
relation: Model.ManyToManyRelation,
modelClass: 'Attribute',
join: {
from: 'products.id',
through: {
from: 'products_attributes.products_id',
to: 'products_attributes.attributes_id',
extra: {
value: 'value',
attributes_dictionaries_id: 'attributes_dictionaries_id',
},
},
to: 'attributes.id',
},
},
variants: {
relation: Model.ManyToManyRelation,
modelClass: 'Variant',
join: {
from: 'products.id',
through: {
from: 'products_variants.products_id',
to: 'products_variants.variants_id',
},
extra: {
value: 'value',
attributes_dictionaries_id: 'attributes_dictionaries_id',
},
to: 'variants.id',
},
},
}
有像关系过滤器这样的解决方案,想知道一种修改功能,这将允许我加入属性字典值。
我想要实现的是每当我获取变体关系时,如果不为空,它会将 attributes_dictionaries_id 解析为值
关键是在文本、数字等类型的字段中,值是平面的,但对于字典,这些值是预定义的并存储在单独的数据库表中。
我不知道如何解决这个问题,因为选择保存为联合表中的 id。它可以保存为平面值,但它不会响应字典的更改。
解决方案
设法在变体模型属性关系映射中使用此代码解决此问题:
modify: (qb) => {
qb.select(
`attributes.*`,
'value',
Attribute.relatedQuery('dictionary')
.select('dictionary.value')
.where('dictionary.id', '=', raw('attributes_dictionaries_id'))
.as('resolvedValue')
);
},
我现在唯一想知道的是我是否可以合并resolvedValue
并value
有条件地显示它,有点(三元表示法)
return attributes_dictionaries_id ? resolvedValue : value
推荐阅读
- matlab - 将 .jar 文件添加到 Matlab
- python - Python TypeError:带有functools.partial的多个参数
- python-3.x - “没有模块命名...”从终端运行 python 时
- c# - 实体框架检查孩子是否属于父母
- c - “C 编程语言” - 计数字符异常?
- mpdf - Mpdf - 使用 css 渲染表格
- java - 如果有多个级别的相同 XML 节点,如何循环 JAXB 类
- python - 无法使用 python match() 解析字符串 - 出现错误 AttributeError: 'NoneType' object has no attribute 'group'
- kubernetes - 如何在运行 kubectl port-forward 之前检查 kubelet 的准备情况?
- python - 在 Python 中使用 OOP