Symfony\Doctrine createQueryBuilder() select 'not in' from a OneToMany relation

Question

I have three entities : Trophy | Competition | Season

One Competition is created for one trophy for one season (you can't have two competitions with same combination "season + trophy").

Competition as a ManyToOne relation with Trophy, and a ManyToOne relation with Season.

Trophy and Season have no direct relation.

I want to display two dropdowns on a page with the content of the second one being dependent from the value of the first : First dropdown allow to select a trophy type (which is a property of Trophy entity), second dropdown must list seasons that are "still available" for trophy type selected (meaning by that "list all seasons for which there are no competition for this trophy type")

I've got almost all working (listener in the Formtype, ajax etc) I've created a specific function allWithoutThisCompetitionType() in SeasonRepository. Function is correctly called every-time user select a new value in dropdown BUT... I don't know anything about SQL nor dql, so I'm struggling to find the correct formulation for my query. I've tried with notin(), with "sub" or "nested" queries... I definitely don't know what I'm doing...

How can I do something like ? :

$qb = $em->getRepository(\App\Entity\Seasonmanager\Season::class)->createQueryBuilder('s')
            ->where('s.competitions.trophy != :trophy')
    ->setParameter('trophy', $trophy);

= Here are all the seasons for which no competition has been already created with this trophy

Thank you for your help.

Trophy entity :

/**
 * @ORM\Entity(repositoryClass="App\Repository\Seasonmanager\TrophyRepository")
 */
class Trophy
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $uniqueid;


 // other properties here...    
//////////////////////////////////////////////////////////////////////////////////

//// LIAISONS VERS D'AUTRES ENTITY ////

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\Seasonmanager\Competition", mappedBy="trophy", orphanRemoval=true)
     */
    private $competitions;

Competition entity :

/**
 * @ORM\Entity(repositoryClass="App\Repository\Seasonmanager\CompetitionRepository")
 * @UniqueEntity(
 *    fields={"trophy","season"},
 *    errorPath="trophy",
 *    message="Une compétition existe déjà pour ce trophée et cette saison"
 * )
 */
class Competition
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

// other properties here...

//////////////////////////////////////////////////////////////////////////////////

//// LIAISONS VERS D'AUTRES ENTITY ////

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Seasonmanager\Trophy", inversedBy="competitions")
     * @ORM\JoinColumn(nullable=false)
     */
    private $trophy;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Seasonmanager\Season", inversedBy="competitions")
     * @ORM\JoinColumn(nullable=false)
     */
    private $season;

Season entity :

/**
 * @ORM\Entity(repositoryClass="App\Repository\Seasonmanager\SeasonRepository")
 * @UniqueEntity("yearin")
 */
class Season
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="integer", length=4)
     */
    private $yearout;

    /**
     * @ORM\Column(type="string", length=8)
     */
    private $uniqueid;

// other properties here...

//////////////////////////////////////////////////////////////////////////////////

//// LIAISONS VERS D'AUTRES ENTITY ////

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\Seasonmanager\Competition", mappedBy="season", orphanRemoval=true)
     */
    private $competitions;

The SeasonRepository where I try to add my query :

namespace App\Repository\Seasonmanager;

use App\Entity\Seasonmanager\Season;
use App\Entity\Seasonmanager\Trophy;
use App\Entity\Seasonmanager\Competition;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Common\Persistence\ManagerRegistry;

/**
 * @method Season|null find($id, $lockMode = null, $lockVersion = null)
 * @method Season|null findOneBy(array $criteria, array $orderBy = null)
 * @method Season[]    findAll()
 * @method Season[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class SeasonRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Season::class);
    }

    public function allWithoutThisCompetitionType($type): array
    {
        $em = $this->getEntityManager();
        $trophys = $em
            ->getRepository(Trophy::class)
            ->findBy(['uniqueid' => $type],['id'=>'DESC'])
        ;
        $trophy = reset($trophys);

        $qb = $em->getRepository(\App\Entity\Seasonmanager\Season::class)->createQueryBuilder('s')
            ->where('s.competitions.trophy',  $trophy);
        $query = $qb->getQuery();
        $result = $query->getResult();
        $donnees = $result;
        return $donnees;

    }

Answer 1

Here is the query, though, I'm not 100% sure it will match your need.
Let me know in comment if something is wrong, I will edit my answer.

public function allWithoutThisCompetitionType($trophy) {
    // Split init from the rest of the query in case you need to use `$qb->expr()`
    $qb=$this->createQueryBuilder("season");

    $qb->leftJoin("season.competition", "competition") // Join competition
       ->join("competition.trophy", "trophy") // Join Trophy
       ->andWhere($qb->expr()->orX( // Or (either one of the following satements)
            $qb->expr()->isNull("competition.id"),
            $qb->expr()->notIn("trophy.uniqueid", ":trophy")))
       ->setParameter("trophy", $trophy);

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