CODEWITHSUNDEEP
phpsqlsymfonyjoindoctrine
Mason
Mason

Reputation: 1126

How to make a join query on Symfony with Doctrine?

I'm kinda stuck on using the querybuilder to create a join query. There is a table called "Person" and a table called "Vacancy". If a person is linked to the vacancy the person.id and vacancy.id is saved in a table called "Candidacy". So how do I get all the persons who are linke to vacancy.id 1?

So where do I have to initiate the AppBundle:Candidacy entity?

$entity = $em
    ->getRepository('AppBundle:Person')
    ->createQueryBuilder('p')
    ->join('p.id', 'c')
    ->where('c.vacancyId= 1')
    ->getQuery()
    ->getResult();

Thank you in advance.

Upvotes: 1

Views: 115

Answers (2)

tchartron
tchartron

Reputation: 717

Example in your repository :

public function getPersonVacancy($personId) {
    $qb = $this->createQueryBuilder('p');
    $qb->leftJoin('p.vacancy', 'v');
    $qb->select('v.name', 'v.id');
    $qb->where('p.id = :personId');
    $qb->setParameter('personId', $personId);
    return  $qb ->getQuery()->getResult();

This is to give you an example it may not work with copy paste. Also, all join methods are explained in doctrine documentation, feel free to read it http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/query-builder.html

Upvotes: 1

Viktar Pryshchepa
Viktar Pryshchepa

Reputation: 343

Looks like you have many-to-many relation. You should link your Person and Vacancy table through Candidacy to Person and then Vacancy to Candidacy.

small example: Entity User is attached to Engagement via UserEngagement table.

        $qb = $this->_em->createQueryBuilder()
        ->select('u')
        ->from($this->_entityName, 'u')
        ->join('u.userEngagements', 'ue')
        ->join('ue.engagement', 'en')
        ...

Upvotes: 1

Related Questions