'Query QueryBuilder with NOT IN and usage in controller

After several weeks of research, I turn to you to try to understand the QueryBuilder part and implement it on my application.

Concretely, here is what I would like to do: Depending on the agent(s) chosen by the user, display targets with the same nationality. My agent entity and my target entity each have a nationality. They are both connected thanks to Doctrine on my Mission entity.

I think I should use NOT IN in my request but don't know how to do it. I show you what I did without results.

/**
     * Récupère les nationalités de l'agent 
     */
    public function findNationality()
    {
        $this
            ->createQueryBuilder ('m')
            ->select ('*')
            ->join('m.agents', 'a')
            ->join('m.cibles', 'c')
            ->where('a.nationality = c.nationality')
            ->getQuery()
            ->getResult();
    }

My MissionType

public function buildForm(FormBuilderInterface $builder, array $options): void
    {
        $builder
            ->add('title')
            ->add('description')
            ->add('code_name')
            ->add('country')
            ->add('speciality', EntityType::class, [
                'label' => 'Choisir une spécialité: ',
                'placeholder' => 'Choisir une spécialité',
                'class' => Specialite::class,
                'choice_label' => 'name'
            ])
            ->add('agents', EntityType::class, [
                'label' => 'Choisir un ou des agent(s): ',
                'class' => Agent::class,
                'choice_label' => 'identification_code',
                'multiple' => true,
                'expanded' => true
            ])
            ->add('contacts', EntityType::class, [
                'label' => 'Selectionner le(les) contact(s): ',
                'class' => Contact::class,
                'choice_label' => 'code_name',
                'multiple' => true,
                'expanded' => true
            ])
            ->add('cibles', EntityType::class, [
                'label' => 'Selectionner la(les) cible(s): ',
                'class' => Cible::class,
                'choice_label' => 'code_name',
                'multiple' => true,
                'expanded' => true
            ])
            ->add('planques', EntityType::class, [
                'label' => 'Selectionner la(les) planque(s): ',
                'class' => Planque::class,
                'choice_label' => 'code',
                'multiple' => true,
                'expanded' => true,
            ])
            ->add('start_date', DateType::class, [
                'widget' => 'single_text'
            ])
            ->add('end_date', DateType::class, [
                'widget' => 'single_text'
            ])
            ->add('status', EntityType::class, [
                'label' => 'Statut de la Mission: ',
                'class' => Status::class,
                'choice_label' => 'name'
            ])
            ->add('type', EntityType::class, [
                'label' => 'Type de Mission: ',
                'class' => TypeMission::class,
                'choice_label' => 'name'
            ]);
    }

My Mission Entity

class Mission
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

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

    /**
     * @ORM\Column(type="text")
     */
    private $description;

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

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

    /**
     * @ORM\Column(type="date")
     */
    private $start_date;

    /**
     * @ORM\Column(type="date")
     */
    private $end_date;

    /**
     * @ORM\OneToMany(targetEntity=Agent::class, mappedBy="mission", cascade={"persist", "merge"})
     */
    private $agents;

    /**
     * @ORM\OneToMany(targetEntity=Cible::class, mappedBy="mission", cascade={"persist", "merge"})
     */
    private $cibles;

    /**
     * @ORM\OneToMany(targetEntity=Contact::class, mappedBy="mission", cascade={"persist", "merge"})
     */
    private $contacts;

    /**
     * @ORM\OneToMany(targetEntity=Planque::class, mappedBy="mission", cascade={"persist", "merge"})
     */
    private $planques;

    /**
     * @ORM\ManyToOne(targetEntity=Specialite::class, inversedBy="missions", cascade={"persist", "merge"})
     */
    private $speciality;

    /**
     * @ORM\ManyToOne(targetEntity=Status::class, inversedBy="missions", cascade={"persist", "merge"})
     */
    private $status;

    /**
     * @ORM\ManyToOne(targetEntity=TypeMission::class, inversedBy="missions", cascade={"persist", "merge"})
     */
    private $type;

    public function __construct()
    {
        $this->agents = new ArrayCollection();
        $this->cibles = new ArrayCollection();
        $this->contacts = new ArrayCollection();
        $this->planques = new ArrayCollection();
    }

I would like the list of targets to update dynamically according to the choice of agents. Any help will be welcome and I thank you in advance because I admit going around in circles and no longer know where I am.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source