SQL问题:一对多关系和EAV模型 [英] SQL issue: one to many relationship and EAV model
问题描述
晚上好,
我是Web编程的新手,需要您的帮助来解决SQL查询所固有的问题.
我使用的数据库引擎是MySQL,我通过PHP访问它,在这里我将解释数据库的简化版本,以解决问题.
让我们假设使用包含三个表的数据库:teams
,teams_information
,attributes
.更精确地:
Good evening guys,
I'm a newbie to web programming and I need your help to solve a problem inherent to SQL query.
The database engine I'm using is MySQL and I access it via PHP, here I'll explain a simplified version of my database, just to fix ideas.
Let's suppose to work with a database containing three tables: teams
, teams_information
, attributes
. More precisely:
1)teams
是一个表格,其中包含有关意大利足球队(足球,而非美式足球:D)的一些基本信息,它由三个字段组成:"id"(int, primary key)
,名称"(varchar,团队名称),nickname
(Varchar
,团队昵称);
1) teams
is a table containing some basic information about italian football teams (soccer, not american football :D), it is formed by three fields: 'id' (int, primary key)
, 'name' (varchar, team name), nickname
(Varchar
, team nickname);
2)attributes
是一个表格,其中包含有关足球队的可能信息的列表,例如city
(球队进行主场比赛的城市),captain
(队长的全名),f_number
(风扇数)等.该表由三个字段组成:id (int, primary key)
,attribute_name
(varchar
,属性的标识符),attribute_desc
(文本,属性含义的解释).该表的每个记录代表一个足球队的单个可能属性;
2) attributes
is a table containing a list of possible information about a football team, such as city
(the city where team plays its home match), captain
(team captain's fullname), f_number
(number of fans) and so on. This table is formed by three fields: id (int, primary key)
, attribute_name
(varchar
, an identifier for the attribute), attribute_desc
(text, an explanation of the meaning of attribute). Each record of this table represents a single possible attribute of a football team;
3)teams_information
是一个表,其中提供了一些有关team
表中列出的球队的信息.该表包含三个字段:id(int
,主键),team_id
(int
,标识团队的外键),attribute_id
(int
,标识属性之一的外键)在attributes
表中列出),attribute_value
(varchar
,属性的值).每条记录代表单个团队的单个属性.通常,不同的团队将拥有不同数量的信息,因此对于某些团队而言,大量属性将可用,而对于其他团队而言,仅少数属性将可用.
3) teams_information
is a table where some information, about teams listed in team
table, are available. This table contains three fields: id (int
, primary key), team_id
(int
, a foreign key which identifies a team), attribute_id
(int
, a foreign key which identifies one of the attributes listed in attributes
table), attribute_value
(varchar
, the value of the attribute). Each record represents a single attribute of a single team. In general, different teams will have a different number of information, so for some teams a large number of attributes will be available while for other teams only a small number of attributes will be available.
请注意,teams
和teams_information
之间的关系是一对多的,并且attributes
和teams_information
之间存在相同的关系
Note that relation between teams
and teams_information
is one to many and the same relation exists between attributes
and teams_information
好吧,考虑到此模型,我的目的是实现一个网格(也许使用ExtJS 4.1)以向用户显示意大利足球队的列表,该网格的每个记录将代表一个足球队,并将包含所有可能的属性:一些字段可能为空(因为对于所考虑的团队而言,因为对于所考虑的团队而言,对应属性是未知的),而其他字段将包含存储在teams_information
表中的值(对于所考虑的团队而言).
根据上面的网格,字段是:id,team_name和一些字段,这些字段代表属性"表中列出的所有不同属性.
Well, given this model my purpose is to realize a grid (maybe with ExtJS 4.1) to show user the list of italian football team, each record of this grid will represent a single football team and will contain all possible attributes: some fields may be empty (because, for considered team, the correspondent attribute is unknown), while the others will contain the values stored in teams_information
table (for the considered team).
According to the above grid's field are: id, team_name and a number of fields to represent all the different attributes listed in 'attributes' table.
我的问题是:我可以通过使用单个SQL查询(也许是一个正确的SELECT
查询,以从数据库表中获取我需要的所有数据)来实现这样的网格吗?
谁能建议我如何编写类似的查询(如果存在)?
My question is: can I realize such a grid by using a SINGLE SQL query (maybe a proper SELECT
query, to fetch all data I need from database tables) ?
Can anyone suggest me how to write a similar query (if it exists) ?
预先感谢您的帮助.
致谢.
恩里科.
推荐答案
您的问题的简短答案是否",MySQL中没有简单的构造可实现所需的结果集.
The short answer to your question is no, there is no simple construct in MySQL to achieve the result set you are looking for.
但是可以仔细地(精心地)制作这样的查询.这是一个例子,我相信您将能够解密它.基本上,对于要返回的每个属性,我都会在选择列表中使用相关子查询.
But it is possible to carefully (painstakingly) craft such a query. Here is an example, I trust you will be able to decipher it. Basically, I'm using correlated subqueries in the select list, for each attribute I want returned.
SELECT t.id
, t.name
, t.nickname
, ( SELECT v1.attribute_value
FROM team_information v1
JOIN attributes a1
ON a1.id = v1.attribute_id AND a1.attribute_name = 'city'
WHERE v1.team_id = t.id ORDER BY 1 LIMIT 1
) AS city
, ( SELECT v2.attribute_value
FROM team_information v2 JOIN attributes a2
ON a2.id = v2.attribute_id AND a2.attribute_name = 'captain'
WHERE v2.team_id = t.id ORDER BY 1 LIMIT 1
) AS captain
, ( SELECT v3.attribute_value
FROM team_information v3 JOIN attributes a3
ON a3.id = v3.attribute_id AND a3.attribute_name = 'f_number'
WHERE v3.team_id = t.id ORDER BY 1 LIMIT 1
) AS f_number
FROM teams t
ORDER BY t.id
对于多值"属性,您必须分别拉出该属性的每个实例. (使用LIMIT指定是否要检索第一个,第二个等).
For 'multi-valued' attributes, you'd have to pull each instance of the attribute separately. (Use the LIMIT to specify whether you are retrieving the first one, the second one, etc.)
, ( SELECT v4.attribute_value
FROM team_information v4 JOIN attributes a4
ON a4.id = v4.attribute_id AND a4.attribute_name = 'nickname'
WHERE v4.team_id = t.id ORDER BY 1 LIMIT 0,1
) AS nickname_1st
, ( SELECT v5.attribute_value
FROM team_information v5 JOIN attributes a5
ON a5.id = v5.attribute_id AND a5.attribute_name = 'nickname'
WHERE v5.team_id = t.id ORDER BY 1 LIMIT 1,1
) AS nickname_2nd
, ( SELECT v6.attribute_value
FROM team_information v6 JOIN attributes a6
ON a6.id = v6.attribute_id AND a6.attribute_name = 'nickname'
WHERE v6.team_id = t.id ORDER BY 1 LIMIT 2,1
) AS nickname_3rd
我在这里以昵称为例,因为美国足球俱乐部经常有多个昵称,例如芝加哥消防足球俱乐部(Chicago Fire Soccer Club)的绰号是:"The Fire","LaMáquinaRoja","Men in Red","CF97"等).
I use nickname as an example here, because American soccer clubs frequently have more than one nickname, e.g. Chicago Fire Soccer Club has nicknames: 'The Fire', 'La Máquina Roja', 'Men in Red', 'CF97', et al.)
无需回答您的问题,但...
NOT AN ANSWER TO YOUR QUESTION, BUT ...
我之前已经提到过很多次,我有多少不喜欢使用EAV数据库实现? IMO应该是一个非常简单的查询,但会变成一个潜在的轻量级调光查询的过于复杂的动物.
Have I mentioned numerous times before, how much I dislike working with EAV database implementations? What should IMO be a very simple query turns into an overly complicated beast of a potentially light dimming query.
创建一个表格,其中每个属性"都是一个单独的列,这会容易得多吗?然后返回合理结果集的查询看起来会更合理...
Wouldn't it be much simpler to create a table where each "attribute" is a separate column? Then queries to return reasonable result sets would look more reasonable...
SELECT id, name, nickname, city, captain, f_number, ... FROM team
但是真正让我感到震惊的是,某些开发人员决定将LDQ作为视图隐藏"在数据库中以启用更简单"的查询的前景.
But what really makes me shudder is the prospect that some developer is going to decide that the LDQ should be "hidden" in the database as a view, to enable the "simpler" query.
如果您走这条路,请拒绝任何要求,您可能必须将此查询作为视图存储在数据库中.
If you go this route, PLEASE PLEASE PLEASE resist any urge you may have to store this query in the database as a view.
这篇关于SQL问题:一对多关系和EAV模型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!