Complexe Left Outer join request

Question

I am trying to do 2 complex SQL requests and I am struggling on one of them. First my data is organized as following : I have questions (MCQ) and Users can answer to them. To do so they create a Session and add Answer (related to the session and one MCQ).

MCQ <<(mcq_id)> Answer <(session_id)>> Session <(user_id)>> User

So a User can own multiple answers to a single MCQ (through multiple sessions)

I now want to do 2 things :

Get a random MCQ that as never been answered in the current session
Get a random MCQ that as never ever been done by the user (meaning in all the sessions he owns)

1 - Get a random MCQ that as never been answered in the current session

This one is working fine believing the testing I've done.

public function getRandomMCQ(Session $session) {
    return $this->createQueryBuilder('m')
        // Don t pay attention to the 3 first sections, it is filtering that the MCQs are "published" for the user 
        ->select('m', 'd')
        ->leftJoin('m.test', 't')
        ->where('m.test IS NULL OR (t.type = :type AND t.status = :status AND t.closingDate < :date)')

        ->leftJoin('m.DP', 'd')
        ->leftJoin('d.test', 'dt')
        ->andWhere('m.DP IS NULL OR (dt.type = :type AND dt.status = :status AND dt.closingDate < :date)')

        ->setParameter('type', 'test')
        ->setParameter('status', 'published')
        ->setParameter('date', new \DateTime())

        ->andWhere('m.course IN (:courses)')
        ->setParameter('courses', $session->getCourses()->toArray())
        // End of don t pay attention zone

        //Exclude already answered MCQs
        ->leftJoin('m.answers', 'a', Join::WITH, 'a.session = :session')
        ->andWhere('a.id IS NULL')
        ->setParameter('session', $session)

        ->orderBy('RAND()')
        ->setMaxResults(1)

        ->getQuery()
        ->getOneOrNullResult()
    ;
}

2 - Get a random MCQ that as never ever been done by the user (meaning in all the sessions he owns)

This is the one I'm completely stuck on, I try like the first one to do an Outer Left Join but on the session Join this time. But the results are completely erratic and are not fitting the conditions I want. I really don't understand what I am doing wrong.

public function getRandomNotDoneMCQ(Session $session, User $user) {
    return $this->createQueryBuilder('m')
        ->select('m', 'd')
        // Don t pay attention to the 3 first sections, it is filtering that the MCQs are "published" for the user  
        ->leftJoin('m.test', 't')
        ->where('m.test IS NULL OR (t.type = :type AND t.status = :status AND t.closingDate < :date)')

        ->leftJoin('m.DP', 'd')
        ->leftJoin('d.test', 'dt')
        ->andWhere('m.DP IS NULL OR (dt.type = :type AND dt.status = :status AND dt.closingDate < :date)')

        ->setParameter('type', 'test')
        ->setParameter('status', 'published')
        ->setParameter('date', new \DateTime())

        ->andWhere('m.course IN (:courses)')
        ->setParameter('courses', $session->getCourses()->toArray())
        // End of don t pay attention zone

        //Exclude already answered MCQs in any time
        ->leftJoin('m.answers', 'a')
        ->leftJoin('a.session', 's', Join::WITH, 's.user = :user')
        ->andWhere('s.id IS NULL')
        ->setParameter('user', $user)

        ->orderBy('RAND()')
        ->setMaxResults(1)

        ->getQuery()
        ->getOneOrNullResult()
    ;
}

Thank you very much for any help you can give, I can provide the raw SQL or any precision you ask if necessary.

Complexe Left Outer join request

0 Answers