CODEWITHSUNDEEP
symfonydoctrine
Remg
Remg

Reputation: 176

symfony2 doctrine query builder collection comparaison

I have theses entities :

a User can speak one or many languages

a Job can require one or Many languages

i would like to filter, for a specific Job, all the users having all the required languages for this job.

Example :

then :

here is my query builder:

public function getMatchingUsersFromJob($job) {
    $qb = $this->_em->createQueryBuilder();
    $qb->select('j')
        ->from('MyBundle:User', 'u')
        ->join('u.languages', 'l')
    ;
    if( $job->getLanguages()->count() > 0 ){
        $i = 1;
        foreach( $job->getLanguages() as $language) {
            $qb->andWhere('l.name = :language'.$i)
                ->setParameter('language'.$i, $language->getName() )
            ;
            $i++;
        }
    }
    return $qb->getQuery()->getResult();
}

Problem is : i always get an empty result...

How can i compare arraycollections ? and be sure to select all User having all the required languages ?

Upvotes: 1

Views: 117

Answers (2)

rLinhares
rLinhares

Reputation: 367

You're using $qb->andWhere('l.name = :language'.$i).

It means you want the l.name must be iqual to many $language->getName() that your $job has.

You could transform the id's in a string to use $qb->Where('l.name IN :languages').

Upvotes: 2

Lunfel
Lunfel

Reputation: 2677

You could use a left join.

Job's required languages left join user spoken languages. If any row has a null joined, then your user is not eligible for the job. If all rows has a match, then your user is eligible for the job.

Upvotes: 0

Related Questions