mysql - 具有条件和限制的 Symfony/Doctrine ManyToMany

Question

我创建了一个包含许多电子邮件的 Person 模型的概念。我还有一个 Company 模型，可以与 People 共享相同的电子邮件(Person 模型)。

简而言之:People OneToMany PeopleEmail ManyToOne Email(经典的多对多单向连接表)

people { id, firstname, lastname, created }
people_emails { person_id, email_id, main, created }
emails { id, email, created }

还有:

companies { id, name, employee_count, created }
companies_emails { company_id, email_id, main, created }
emails { id, email, created }

问题是，我想在连接表中存储一个名为“main”的 bool 值，如下所示:

person_id | email_id | main | created
    8     |     5    | true | 2014-10-21 16:54:21

...这样我就可以这样做:

Mark Wilson (Person) has 5 emails.
3 of them are his company e-mails (shared): contact@company.com, office@company.com, it@company.com
2 of them are his own.
1 he answers only in his leisure time
1 is his MAIN email: i.e. m.wilson@company.com

我不想获取所有这 5 封电子邮件，而是想轻松获取主要电子邮件，就好像它是一个普通的 Person 模型列一样:

firstname: Mark (string)
lastname: Wilson (string)
emails: (array)
primary_email: (email object)

我不能在其他任何地方存储“主要”属性，正如我想指出的那样，马克和他的电子邮件之间的关系是“主要”，而不是电子邮件本身。

现在，我确实存储了这个值，但问题是，如何创建如下实体属性:

class Person {

    (...)

    /**
     * @ORM\ManyToMany(targetEntity="Email")
     * @ORM\JoinTable(name="people_emails",
     *      joinColumns={@ORM\JoinColumn(name="person_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="email_id", referencedColumnName="id", unique=true)}
     *      )
     */
    private $primary_email;

    /**
     * @ORM\ManyToMany(targetEntity="Email")
     * @ORM\JoinTable(name="people_emails",
     *      joinColumns={@ORM\JoinColumn(name="person_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="email_id", referencedColumnName="id", unique=true)} // A condition?
     *      )
     */
    private $emails;

    public function getPrimaryEmail() {
        return $this->primary_email; // Single row limit?
    }

    public function getEmails() {
        return $this->emails;
    }

    (...)
}

问题是，我真的很想把它作为一个实体属性，只是为了在任何可能的情况下使用，而不需要为整个 Person 模型编写自定义 repostory 函数。

或者这可能是一种完全错误的方式。我想在 Twig 中使用该属性，例如:

{{person.primary_email.email}}

总结一下:我想根据 joinTable 列存储 ManyToMany 单行关系。

怎么做？

Answer 1

有很多方法可以做到这一点，关于您的设计选择也有很多重要的事情要说。无论哪种方式，这里都是您可以使用两个连接表实现此目的的一种方法的示例(如果您想使用外键，则需要两个)。

<?php

interface AddressLink
{
    public function getEmail();
    public function isMain();
}


trait EmailAddressOwner
{
    protected abstract function getAddressLinks();

    public function getPrimaryEmailAddress()
    {
        return $this->getAddressLinks()->filter(function (AddressLink $addressLink) {
            return $addressLink->isMain();
        })->first();
    }

    public function getEmailAddresses()
    {
        return $this->getAddressLinks()->map(function (AddressLink $addressLink) {
            return $addressLink->getEmail();
        });
    }
}

class PersonEmailAddress implements AddressLink
{
    private $person; // ManyToOne
    private $email; // ManyToOne
    private $main; // bool
}

class CompanyEmailAddress implements AddressLink
{
    private $company; // ManyToOne
    private $email; // ManyToOne
    private $main; // bool
}


class Email
{
    private $id;
    private $address;
}

class Person
{
    /**
     * @ORM\OneToMany(targetEntity="PersonEmailAddress")
     */
    private $emailAddressLinks;

    use EmailAddressOwner;

    public function getAddressLinks()
    {
        return $this->emailAddressLinks;
    }

}

class Company
{
    /**
     * @ORM\OneToMany(targetEntity="CompanyEmailAddress")
     */
    private $emailAddressLinks;

    use EmailAddressOwner;

    public function getAddressLinks()
    {
        return $this->emailAddressLinks;
    }
}

另一种方法是为您的 Email 实体添加一个 ManyToMany 关系，并为主要电子邮件地址添加一个 ManyToOne 关系。

如果你在 twig 中回答你的问题，请在评论中回答你

{{ person.primaryEmail.email }}

它实际上会在您的 Person 对象上调用 getPrimaryEmail() 方法。您可以像我上面概述的那样实现。这样你就不需要拥有这个额外的属性。