CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-orm
Michael Emerson
Michael Emerson

Reputation: 1813

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

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?

Upvotes: 4

Views: 4872

Answers (1)

hasumedic
hasumedic

Reputation: 2167

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.

Upvotes: 7

Related Questions