具有多对多关系的Symfony实体存储库

Question

I have two entities product and document which are related via a many-to-many relationship with a JOIN table. My product entity looks like below, my document entity currently has no reference back to the product.

/**
 * @ORM\Entity
 * @ORM\Table(name="product")
 * @ORM\Entity(repositoryClass="\MyApp\CmsBundle\Repository\ProductRepository")
 */
class Product
{
    // ...

    /**
     * @ManyToMany(targetEntity="Document")
     * @JoinTable(name="products_documents",
     *      joinColumns={@JoinColumn(name="product_id", referencedColumnName="id")},
     *      inverseJoinColumns={@JoinColumn(name="document_id", referencedColumnName="id")}
     *      )
     **/
     protected $documents;

     // ...

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

    public function addDocument(Document $document)
    {
        $this->documents[] = $document;

        return $this;
    }

    public function removeDocument(Document $document)
    {
        $this->documents->removeElement($document);
    }

    public function getDocuments()
    {
        return $this->documents;
    }

When I present the product on the front-end of the site I also list it's documents. However I need to be able to filter them out based on their attributes status and privacy.

So I've been trying to create an entity repository to handle this logic but everything I've tried so far has failed. What I need to achieve is a query similar to this, but in a Symfony/Doctrine format:

SELECT d.*
FROM documents d, products_documents pd,
WHERE pd.product_id = :product_id
  AND pd.document_id = d.id,
  AND d.status = 'PUBLISHED',
  AND d.privacy = 'PUBLIC';

Ideally I would like to be able to do something as simple as this from my controller:

// get documents to display on front-end
$documents = $em->getRepository('MyAppCmsBundle:Product')->getDocumentsForProduct($product);

I've got the call to the function working, I'm just not clued up on how to retrieve the data I want.

UPDATE

This is what I've got so far, whilst maintaining working code, but instead of returning documents it returns all products. I'm not sure how to add the condition to specify it to the product I pass in, or to return the documents and not the product.

ProductRepository.php

public function getDocumentsForProduct(Product $product, $authenticated = false)
{
    $query = $this->createQueryBuilder('p')
    ->join('MyApp\CmsBundle\Entity\Document', 'd')
    ->where('d.status = :status')
    ->andWhere('d.privacy = :privacy')
    ->setParameters(array(
        'status' => 'PUBLISHED',
        'privacy' => 'PUBLIC',
    ))
    ->getQuery();

    return $query->getResult();
}

Answer 1

If you want it to return documents, you should put it in your DocumentRepository.

The 'createQueryBuilder' method autmatically selects entities of the type that belongs to the repository. So in your case, that's Product. If you put the same code in the DocumentRepository, you should get Document entities.

I don't know if there is a way to change this using QueryBuilder. But you can use DQL instead like this:

$this->getEntityManager()->createQuery('SELECT p FROM YourBundle:Document WHERE ...');