CODEWITHSUNDEEP
symfonydoctrine-ormdql
VinZ
VinZ

Reputation: 380

How to access an attribute of a LEFT JOIN on a ManyToMany relation?

I have three entities linked with various relations :

-Band-
name
...
tours (ManyToMany with Tour)
shows (OneToMany with Show)

-Tour-
name
...
bands (ManyToMany with Band)
$shows(OneToMany with Show)

-Show-
date
...
band(ManyToOne with Band, nullable)
tour(ManyToOne with Tour, nullable)

I can set up Show for Band (then show_tour is NULL) and also Show for Tour (then show_band is NULL).

Now, I'd like to get all Show for a given Band. My DQL is like this :

public function findAllShowsToComeFor($band)
{
    $date = new \DateTime('now');
    return $this->createQueryBuilder('s')
        ->leftJoin('s.band', 'band')
        ->where('band.id = :bid')
        ->setParameter('bid', $band->getId())
        ->leftJoin('s.tour', 'tour')
        ->where('tour.bands = :tid')
        ->setParameter('tid', $band->getId())
        ->andWhere('s.day >= :date')
        ->setParameter('date', $date->format('Y-m-d'))
        ->orderBy('s.day', 'ASC')
        ->getQuery()
        ->getResult();
}

Of course, this raises a Syntax Error ([Semantical Error] line 0, col 92 near 'bands = :tid': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.), because of these lines :

->leftJoin('s.tour', 'tour')
->where('tour.bands = :tid')
->setParameter('tid', $band->getId())

I would need to do something like :

->leftJoin('s.tour', 'tour')
->where('tour.bands.id IN :tid')
->setParameter('tid', $band->getId())

but this is not possible...

Can anyone help?

Upvotes: 0

Views: 63

Answers (2)

sast
sast

Reputation: 478

There is MEMBER OF, I believe you want something like this:

$this->createQueryBuilder('s')
        ->leftJoin('s.tour', 'tour')
        ->where('s.band = :band OR :band MEMBER OF tour.bands')
        ->setParameter('band', $band)
        ->andWhere('s.day >= :date')
        ->setParameter('date', $date->format('Y-m-d'))
        ->orderBy('s.day', 'ASC')
        ->getQuery()
        ->getResult();

Upvotes: 1

Bacteries
Bacteries

Reputation: 603

You don't need to filter Tour on band ID if you already done it on Band.

So this will give you this :

$this->createQueryBuilder('s')
    ->join('s.band', 'band')
    ->where('band.id = :bid')
    ->leftJoin('s.tour', 'tour')
    ->where('s.day >= :date')
    ->orderBy('s.day', 'ASC')
    ->setParameter('date', $date->format('Y-m-d'))
    ->setParameter('bid', $band->getId())
    ->getQuery()
    ->getResult();

With this you will get an ArrayCollection of Show, and if your do a ->getTour on it you may have a Tour (or NULL).

Upvotes: 0

Related Questions