问题描述
Mysql数据库具有由对象类型定义的对象，然后将这些对象类型结合起来以设置其属性.它基于Spencer指出的实体属性值"模型.
My Mysql database has objects which are defined by an object type which is then joined to set it's properties. And it is based on the Entity Attribute Value model as pointed out by Spencer.
数据库要比下面的复杂一些，但这应该使我的问题更清楚.
The database is a little more complicated then below but this should make my question a little more clear.
我有一个mysql表objects，其中包含有关对象的一些基本信息，例如create_date，title，author，当然还有id.
I have a mysql table objects which contains some basic information about an object say create_date, title,author and of course an id.
我还有一个表object_properties，其中包含一些有关对象的更多数据.
I also have a table object_properties which contains some more data about objects.
我在搜索它们时遇到了一些麻烦.例如，一个对象可能同时具有end_date和start_date作为单独的属性.这些在object_properties中每个都有自己的行.
I have some trouble searching through them.For example, an object might has both an end_date and start_date as a seperate property. These would each have their own row in object_properties.
由于它们各自都有自己的行，因此如何匹配结束日期和开始日期?
How would one match the end and start date as they each have their own row?
通常我会这样查询它； select * from table where start_date > value AND end_date < value但是我不能这样做，因为每个属性都是单独的一行.
Normally I would query it like this; select * from table where start_date > value AND end_date < value But I can't do this because each property is a separate row.
我尝试了以下查询； `
I tried the following query; `
select *
from object_properties
where object_id = 1
and ( (object_properties.type_id = 1)
and (object_properties.value = 2)
and ( (object_properties.type_id = 2)
and (object_properties.value = 2)
)
)
在上面的示例中，type_id = 1将是开始日期，而type_id =2将是结束日期，但这似乎不起作用.我当时以为我看错了方向.
In the example above the type_id = 1 would be the start date and type_id =2 would be the end date but this did not seem to work.I was thinking I am looking at it the wrong way.
我意识到我的问题的标题可能是错误的，我真的不知道该如何存储数据.
I realize the title of my question might be wrong, I don't really know how storing data this way is called.
这是我根据马修斯的答案使用的解决方案:
这是我最后得到的结果:`
Here is what I ended up with: `
SELECT
o.title,
o.id,
op1.value AS start_date,
op2.value AS end_date
FROM object o
JOIN object_meta_values op1 ON op1.object_id=o.id AND op1.object_type_meta_id=28
JOIN object_meta_values op2 ON op2.object_id=o.id AND op2.object_type_meta_id=29
WHERE object_type_id = 11
AND (12 <= op1.value AND op1.value < 16 )
OR (12 < op2.value AND op2.value <= 16 )
OR (op1.value <= 12 AND 16 <= op2.value)
`.非常感谢！
推荐答案
您可以使用多个左联接来做到这一点.
You can use multiple left joins to do this.
SELECT
o.create_date,
o.title,
o.author,
op1.value AS start_date,
op2.value AS end_date,
...
FROM object o
LEFT JOIN object_properties op1 ON op1.object_id=o.object_id AND op1.type_id=1
LEFT JOIN object_properties op2 ON op2.object_id=o.object_id AND op2.type_id=2
不具有属性的值最终将为空.
Values that do not have properties will end up being null.
此方法也不需要第二个查询即可获取属性.
This method you don't have to have a second query to get the properties either.
编辑如果您不想检索空值，请省略left join的left部分.
EDITIf you do not want to retrieve null values, then omit the left part of the left join.
这篇关于Mysql EAV将行匹配为字段或实体的属性值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！