Filtering on many-to-many association with Doctrine2

Question

I have an Account entity which has a collection of Section entities. Each Section entity has a collection of Element entities (OneToMany association). My problem is that instead of fetching all elements belonging to a section, I want to fetch all elements that belong to a section and are associated with a specific account. Below is my database model.

Thus, when I fetch an account, I want to be able to loop through its associated sections (this part is no problem), and for each section, I want to loop through its elements that are associated with the fetched account. Right now I have the following code.

$repository = $this->objectManager->getRepository('MyModule\Entity\Account');
$account = $repository->find(1);

foreach ($account->getSections() as $section) {
    foreach ($section->getElements() as $element) {
        echo $element->getName() . PHP_EOL;
    }
}

The problem is that it fetches all elements belonging to a given section, regardless of which account they are associated with. The generated SQL for fetching a section's elements is as follows.

SELECT t0.id AS id1, t0.name AS name2, t0.section_id AS section_id3
FROM mydb.element t0
WHERE t0.section_id = ?

What I need it to do is something like the below (could be any other approach). It is important that the filtering is done with SQL.

SELECT e.id, e.name, e.section_id
FROM element AS e
INNER JOIN account_element AS ae ON (ae.element_id = e.id)
WHERE ae.account_id = ?
AND e.section_id = ?

I do know that I can write a method getElementsBySection($accountId) or similar in a custom repository and use DQL. If I can do that and somehow override the getElements() method on the Section entity, then that would be perfect. I would just very much prefer if there would be a way to do this through association mappings or at least by using existing getter methods. Ideally, when using an account object, I would like to be able to loop like in the code snippet above so that the "account constraint" is abstracted when using the object. That is, the user of the object does not need to call getElementsByAccount() or similar on a Section object, because it seems less intuitive.

I looked into the Criteria object, but as far as I remember, it cannot be used for filtering on associations.

So, what is the best way to accomplish this? Is it possible without "manually" assembling the Section entity with elements through the use of DQL queries? My current (and shortened) source code can be seen below. Thanks a lot in advance!

/**
 * @ORM\Entity
 */
class Account
{
    /**
     * @var int
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue
     */
    protected $id;

    /**
     * @var string
     * @ORM\Column(type="string", length=50, nullable=false)
     */
    protected $name;

    /**
     * @var ArrayCollection
     * @ORM\ManyToMany(targetEntity="MyModule\Entity\Section")
     * @ORM\JoinTable(name="account_section",
     *      joinColumns={@ORM\JoinColumn(name="account_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="section_id", referencedColumnName="id")}
     * )
     */
    protected $sections;

    public function __construct()
    {
        $this->sections = new ArrayCollection();
    }

    // Getters and setters
}


/**
 * @ORM\Entity
 */
class Section
{
    /**
     * @var int
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    protected $id;

    /**
     * @var string
     * @ORM\Column(type="string", length=50, nullable=false)
     */
    protected $name;

    /**
     * @var ArrayCollection
     * @ORM\OneToMany(targetEntity="MyModule\Entity\Element", mappedBy="section")
     */
    protected $elements;

    public function __construct()
    {
        $this->elements = new ArrayCollection();
    }

    // Getters and setters
}


/**
 * @ORM\Entity
 */
class Element
{
    /**
     * @var int
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    protected $id;

    /**
     * @var string
     * @ORM\Column(type="string", length=50, nullable=false)
     */
    protected $name;

    /**
     * @var Section
     * @ORM\ManyToOne(targetEntity="MyModule\Entity\Section", inversedBy="elements")
     * @ORM\JoinColumn(name="section_id", referencedColumnName="id")
     */
    protected $section;

    /**
     * @var \MyModule\Entity\Account
     * @ORM\ManyToMany(targetEntity="MyModule\Entity\Account")
     * @ORM\JoinTable(name="account_element",
     *      joinColumns={@ORM\JoinColumn(name="element_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="account_id", referencedColumnName="id")}
     * )
     */
    protected $account;

    // Getters and setters
}

Answer 1

If I understand correctly, you want to be able to retrieve all Elements of all Sections of an Account, but only if those Elements are associated with that Account, and this from a getter in Account.

First off: An entity should never know of repositories. This breaks a design principle that helps you swap out the persistence layer. That's why you cannot simple access a repository from within an entity.

Getters only

If you only want to use getters in the entities, you can solve this by adding to following 2 methods:

class Section
{
    /**
     * @param  Account $accout
     * @return Element[]
     */
    public function getElementsByAccount(Account $accout)
    {
        $elements = array();

        foreach ($this->getElements() as $element) {
            if ($element->getAccount() === $account) {
                $elements[] = $element->getAccount();
            }
        }

        return $elements;
    }
}

class Account
{
    /**
     * @return Element[]
     */
    public function getMyElements()
    {
        $elements = array()

        foreach ($this->getSections() as $section) {
            foreach ($section->getElementsByAccount($this) as $element) {
                $elements[] = $element;
            }
        }

        return $elements;
    }
}

Repository

The solution above is likely to perform several queries, the exact amount depending on how many Sections and Elements are associated to the Account.

You're likely to get a performance boost when you do use a Repository method, so you can optimize the query/queries used to retrieve what you want.

An example:

class ElementRepository extends EntityRepository
{
    /**
     * @param  Account $account [description]
     * @return Element[]
     */
    public function findElementsByAccount(Account $account)
    {
        $dql = <<< 'EOQ'
SELECT e FROM Element e
JOIN e.section s
JOIN s.accounts a
WHERE e.account = ?1 AND a.id = ?2
EOQ;

        $q = $this->getEntityManager()->createQuery($dql);
        $q->setParameters(array(
            1 => $account->getId(),
            2 => $account->getId()
        ));

        return $q->getResult();
    }
}

PS: For this query to work, you'll need to define the ManyToMany association between Section and Account as a bidirectional one.

Proxy method

A hybrid solution would be to add a proxy method to Account, that forwards the call to the repository you pass to it.

class Account
{
    /**
     * @param  ElementRepository $repository
     * @return Element[]
     */
    public function getMyElements(ElementRepository $repository)
    {
        return $repository->findElementsByAccount($this);
    }
}

This way the entity still doesn't know of repositories, but you allow one to be passed to it.

When implementing this, don't have ElementRepository extend EntityRepository, but inject the EntityRepository upon creation. This way you can still swap out the persistence layer without altering your entities.