Doctrine - Query One-To-Many, Unidirectional with Join Table association from inversed side

Question

My target is:

Create universal association where first entity (eg. Category) can be used many times for other Objects (eg. Post, Article)

Example

Post has categories and Article has categories, but Article and Post are totally different entities. (connection is not possible for both at the same time)

---

Mapping example:

Post

<?php
/** @Entity */
class Post
{
    // ...

    /**
     * @ManyToMany(targetEntity="Category")
     * @JoinTable(name="post_categories",
     *      joinColumns={@JoinColumn(name="post_id", referencedColumnName="id")},
     *      inverseJoinColumns={@JoinColumn(name="category_id", referencedColumnName="id", unique=true)}
     *      )
     */
    private $categories;

    public function __construct()
    {
        $this->categories= new \Doctrine\Common\Collections\ArrayCollection();
    }

    // ...
}

Article

<?php
/** @Entity */
class Article
{
    // ...

    /**
     * @ManyToMany(targetEntity="Category")
     * @JoinTable(name="article_categories",
     *      joinColumns={@JoinColumn(name="article_id", referencedColumnName="id")},
     *      inverseJoinColumns={@JoinColumn(name="category_id", referencedColumnName="id", unique=true)}
     *      )
     */
    private $categories;

    public function __construct()
    {
        $this->categories= new \Doctrine\Common\Collections\ArrayCollection();
    }

    // ...
}

Category

<?php
/** @Entity */
class Category
{
    // ...
    /**
     * @ORM\Column(type="string", length=100)
     */
    protected $name;
}

As you can see this is One-To-Many, Unidirectional with Join Table association. Now with this I can query for single Post categories and Article categories but Category dont know about Post or Article. This is nice because I can use Category repeatedly.

---

Where is a problem?

I need load ALL Posts or Articles which contain single Category or Categories.

Example

We have 20 Posts with Category named "symfony" (id:2) and 10 with with Category named "doctrine" (id:3). Now i need query to load all Posts with category "doctrine"

findPostsByCategoryId( $id );
// findPostsByCategoryId( 3 );

OR all Posts with both categories

findPostsByCategories( Array $array );
// findPostsByCategories( array(2,3) );

How can i do this?

I need solution for this case or solution to achieve my goal. Each tip is appreciated.

P.S. I have other related problem with this mapping described here

Validate UniqueEntity for One-To-Many, Unidirectional with Join Table

Del

It looks like you're on the right track - are you getting errors or not receiving information you're looking for? – Teenyweeny

Yes, errors but i cant create valid query without warnings and "information you're looking for" :) – Del

Could you post the actual warnings you are getting? – Teenyweeny

For example: "Error: Cannot select entity through identification variables without choosing at least one root entity alias." But i think even without the error, my query wouldn't load what i want – Del

Your best route is going to be creating custom repository functions that use the QueryBuilder or DQL to get what you need. It also sounds like you might be selecting from the wrong root alias, which is difficult to determine without seeing the full code that you are posting. – Teenyweeny

I try DQL and Repository but this is problem - I don't know how create valid query. In addition i think this is universal question - i created this "Category example" to show general problem – Del

Answer 1

Unless I'm misreading your question this seems pretty simple:

Get all posts with a specific category:

$qb = $this->getEntityManager()->createQueryBuilder();

$qb->select('p')
    ->from('SomeBundle:Post', 'p')
    ->join('p.categories', 'c')
    ->where('c.id = :categoryId')
    ->setParameter('categoryId', $categoryId)
    ->getQuery()
    ->getResult();

Get all posts with a range of categories:

$qb = $this->getEntityManager()->createQueryBuilder();

$qb->select('p')
    ->from('SomeBundle:Post', 'p')
    ->join('p.categories', 'c')
    ->where($qb->expr()->in('c.id', ':categoryIds'))
    ->setParameter('categoryIds', $categoryIds) // array of ids
    ->getQuery()
    ->getResult();

Literatim