How to incorporate a join table in a doctrine query that has no entity

Question

Not sure if the title here is a little ambiguous, but basically what I'm trying to do is grab data from the database but I need to reference a join table which does not have its own entity, as it's only referenced in another entity.

Here is my set up. I have a Symfony2 project and I have a list of stages in a database and each stage can be in one or multiple categories. I need to grab all stages from a specific category and order them by their relevant sort order. If I was doing this directly in mySQL I would write:

SELECT s.stage FROM stage s LEFT JOIN stage_category sc ON sc.stage_id = s.id WHERE sc.category_id = 1 ORDER BY s.sort_order

But the problem is, "stage_category" join table has no entity, so if I reference it in my Doctrine query it tells me it does not exist. I am writing it as:

$qb = $this->createQueryBuilder('s')
            ->select('s')
            ->join('stage_category','sc')
            ->where('sc.category_id = :cat')
            ->setParameter('cat', $cat);

Although, not entirely sure if this is correct - it's in my repository class in a method called findStagesInCategory().

/**
 * @var \Doctrine\Common\Collections\Collection
 *
 * @ORM\ManyToMany(targetEntity="AppBundle\Entity\Category", inversedBy="stage", cascade={"remove", "persist"})
 * @ORM\JoinTable(name="stage_category")
 * @ORM\JoinColumn(onDelete="SET NULL")
 */
private $category;

And on the inverse side:

/**
 * @var \Doctrine\Common\Collections\Collection
 *
 * @ORM\ManyToMany(targetEntity="Stage", mappedBy="category", cascade={"remove", "persist"})
 */
private $stage;

Is there any plausible way of being able to get the data I need?

Answer 1

The reason why there's no entity in between Stage and Category is because there's nothing else in that relation that justifies the existence of an Entity (that's why it is a manyToMany).

What you want to do is to fetch join and filter the relationship:

$qb = $this->createQueryBuilder('s')
        ->select('s')
        ->join('s.category','c')
        ->where('c.category_id = :cat')
        ->setParameter('cat', $cat);

Alternatively, if stage_category would have a property that justified the existence of an entity, you would change your mapping to be a one-to-Many and many-To-One which would allow you to perform your original query using the query builder.