我有一个设计糟糕的数据库,其中采用了 Entity-Attribute-Value 模式,我需要配置 Doctrine 2.5 .
下表用于说明用户如何将其属性存储在属性表中 . 以下是PHP类来说明 .
CREATE TABLE users (
user_id INT UNSIGNED AUTOINCREMENT PRIMARY KEY
);
INSERT INTO users (user_id) VALUES (123);
CREATE TABLE user_properties (
user_id INT UNSIGNED NOT NULL,
property_name VAR_CHAR(100) NOT NULL,
property_value VAR_CHAR(100),
PRIMARY KEY (user_id, property_name),
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
INSERT INTO user_properties (user_id, property_name, property_value)
VALUES
(123, 'first_name', 'Joey Jo-Jo'),
(123, 'date_of_birth', '1978-01-01');
我想要做的是使这些属性具体实体可以作为User实体上的单独属性访问 .
<?php
class User
{
/**
* @var \Doctrine\Common\Collections\ArrayCollection|AbstractUserProperies[]
*/
protected $properties;
public function getFirstName()
{
$firstNameProperty = $this->seekPropertyByType(UserFirstName::class);
return $firstNameProperty ? $firstNameProperty->getValue() : null;
}
public function getDateOfBirth()
{
$dobProperty = $this->seekPropertyByType(UserDateOfBirth::class);
return $dobProperty ? $dobProperty->getValue() : null;
}
protected function seekPropertyByType($className)
{
foreach ($this->properties as $property) {
if ($property instanceof $clasaName) {
return $property
}
}
return null;
}
}
abstract class AbstractUserProperty
{
/**
* @var User
*/
protect $user;
protected $value;
public function getValue()
{
return $this->value;
}
}
class UserFirstName extends AbstractUserProperty
{
public function setValue()
{
$this->value = (string)$value;
}
}
class UserDateOfBirth extends AbstractUserProperty
{
public function setValue(\DateTime $dob)
{
$this->value = $dob;
}
}
以下是我尝试使用Doctrine配置此设置 .
<entity name="AbstractUserProperty" table="user_properties" inheritance-type="SINGLE_TABLE">
<discriminator-column name="property_name" type="string" />
<discriminator-map>
<discriminator-mapping value="first_name" class="UserFirstName"/>
<discriminator-mapping value="date_of_birth" class="UserDateOfBirth"/>
</discriminator-map>
<id name="user" type="integer" association-key="true" />
<id name="property_name" type="string" />
<many-to-one field="user" target-entity="User" inversed-by="properties">
<join-columns>
<join-column name="user_id" referenced-column-name="user_id" />
</join-columns>
</many-to-one>
</entity>
<entity name="UserFirstName">
<field name="value" column="property_value" type="string"/>
</entity>
<entity name="UserDateOfBirth">
<field name="value" column="property_value" type="datetime"/>
</entity>
<entity name="User">
<one-to-many field="properties" target-entity="AbstractUserProperty" mapped-by="user" fetch="EAGER">
<cascade>
<cascade-persist/>
</cascade>
</one-to-many>
</entity>
这个配置的问题是我们将从Doctrine得到一个错误:
在字段或鉴别器列映射中实体'AbstractUserProperty'上的列'property_name'的重复定义 .
这是因为 <discriminator-column>
和 <id>
都描述了 property_name
. 但是如果我不这样做,我每 User
只能有一个属性,我需要多个属性 .
我也无法将 <id>
移到 UserFirstName
或 UserDateBirth
,因为Doctrine会抱怨 AbstractUserProperty
没有ID - 即使它是抽象的 .
我唯一能想到的是改变 user_properties
表以包含一个代理主键 .