Create a Query using QueryBuilder with a Many-to-Many relation in Symfony2 and Doctrine

Question

Good evening everybody!

I've got a little problem. I would like to filter a query's result using a custom DQL Query in Symfony2 framework.

Here is the state of my database:

Extract of the database

I've got the SQL Query that returns the wanted result:

SELECT * FROM question WHERE question_id NOT IN (SELECT question_id FROM questions_joueurs WHERE joueur_id = 1)

I just would like to implement that in my QuestionRepository.

Thanks a lot for your next help !

CloudCompany

FuzzyTree · Accepted Answer · 2014-05-03 07:33:16Z

3

You can achieve this using NOT EXISTS and MEMBER OF. Something like this

$qb->select('q.question_id, q.question_intitule')
    ->from('MyBundleNameSpace\Entity\Question', 'q')
    ->where('NOT EXISTS (SELECT 1 FROM MyBundleNameSpace\Entity\Jouer j WHERE j MEMBER OF q.jouers)');

answered May 3, 2014 at 7:33

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CloudCompany · Accepted Answer · 2014-05-06 07:15:59Z

1

Thank you for your answer FuzzyTree! It works! I had adapted it in order to put it into my QuestionRepository.

Here is my method:

public function findNotAnsweredByJoueurs(Partie $partie, $level)
{
    $qb = $this->createQueryBuilder('q');
    $qb->where('q.level < ' . $level);
    foreach($partie->getJoueurs() as $joueur)
    {
        $qb->andWhere('NOT EXISTS (SELECT ' . $joueur->getId() . ' FROM Cloud\Bundle\MoneyDropBundle\Entity\Joueur j WHERE j MEMBER OF q.joueurs)');
    }

    return $qb->getQuery()
              ->getResult();
}

edited May 6, 2014 at 7:15

answered May 3, 2014 at 14:20

CloudCompany

3673 silver badges16 bronze badges

1 Comment

FuzzyTree Over a year ago

The foreach isn't necessary in fact you're overwriting the where condition with each iteration (to stack where conditions use 'andWhere' after the first 'where').

Collectives™ on Stack Overflow

Create a Query using QueryBuilder with a Many-to-Many relation in Symfony2 and Doctrine

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related