How to make left join in dql Symfony

Question

I would like to understand dql logic and i would like to create this kind of request.

Bellow this is the SQL request i would like to do :

SELECT titlemission,descriptionmission,onsetdate,deadline,budgetmission,prioritymission,company FROM mission
left join mission_tag mt on mission.id = mt.mission_id
left join tag on mt.tag_id = tag.id
left join user on user.id = mission.iduser_id
where nomtag = "test20";

I read a lot of documentation but no one explain exatly how to use this QueryBuilder. Symfony documentation is too abstract for me.

I only know that you have to forget the way you edit the sql request and think in object and entity relation. But i don't know how to do that.

This is my entity Tag

<?php

namespace App\Entity;

use App\Repository\TagRepository;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;

#[ORM\Entity(repositoryClass: TagRepository::class)]
class Tag
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\Column(length: 100)]
    #[Assert\NotBlank]
    #[Assert\Length(min:2)]
    #[Assert\Valid]
    private ?string $nomtag = null;

    #[ORM\ManyToMany(targetEntity: Mission::class, mappedBy: 'tags', cascade: ['persist'])]
    #[ORM\JoinTable(name: 'mission_tag')]
    #[ORM\JoinColumn(name: 'tag_id',referencedColumnName: 'id')]
    #[ORM\InverseJoinColumn(name:'mission_id', referencedColumnName: 'id')] // Mission_id est couplé à l'id de mission
    private Collection $missions;

    public function __construct()
    {
        $this->missions = new ArrayCollection();
    }

//    #[ORM\ManyToMany(targetEntity: Mission::class, inversedBy:"$idtagmissionassign")]
//    private $genusScientists;

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getNomtag(): ?string
    {
        return $this->nomtag;
    }

    public function setNomtag(string $nomtag): self
    {
        $this->nomtag = $nomtag;

        return $this;
    }

    /**
     * @return Collection<int, Mission>
     */
    public function getMissions(): Collection
    {
        return $this->missionstag;
    }

    public function addMission(Mission $missions): self
    {
        if (!$this->missions->contains($missions)) {
            $this->missions->add($missions);
            $missions->addTag($this);
        }

        return $this;
    }

    public function removeMission(Mission $missions): self
    {
        if ($this->missions->removeElement($missions)) {
            $missions->removeTag($this);
        }

        return $this;
    }

    public function __toString(): string
    {
        return $this->nomtag;
    }
}

This is my entity user :

<?php

namespace App\Entity;

use App\Repository\UserRepository;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Bridge\Doctrine\Validator\Constraints\UniqueEntity;
use Symfony\Component\Security\Core\User\PasswordAuthenticatedUserInterface;
use Symfony\Component\Security\Core\User\UserInterface;

#[ORM\Entity(repositoryClass: UserRepository::class)]
#[ORM\Table(name: 'user')]
#[UniqueEntity(fields: ['email'], message: 'Un compte existe déjà avec cette adresse email')]
class User implements UserInterface, PasswordAuthenticatedUserInterface
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\Column(length: 180, unique: true)]
    private ?string $email = null;

    #[ORM\Column]
    private array $roles = [];

    /**
     * @var string The hashed password
     */
    #[ORM\Column]
    private ?string $password = null;

    #[ORM\Column(length: 100)]
    private ?string $name = null;

    #[ORM\Column(length: 100)]
    private ?string $firstname = null;

    #[ORM\Column(length: 150)]
    private ?string $company = null;

    #[ORM\Column(type: 'boolean')]
    private $isVerified = false;

    #[ORM\Column(length: 255)]
    private ?string $companyadress = null;

    #[ORM\OneToMany(mappedBy: 'iduser', targetEntity: Mission::class)]
    private Collection $missions;

    #[ORM\OneToMany(mappedBy: 'iduser', targetEntity: Service::class)]
    private Collection $services;

    #[ORM\ManyToMany(targetEntity: Service::class, mappedBy: 'idserviceassign')]
    private Collection $servicesassign;

    #[ORM\ManyToMany(targetEntity: Mission::class, mappedBy: 'idmissionassign')]
    #[ORM\JoinTable(name: 'mission_user')]
    #[ORM\JoinColumn(name: 'user_id',referencedColumnName: 'id')]
    #[ORM\InverseJoinColumn(name:'mission_id', referencedColumnName: 'id')]
    private Collection $missionsassign;

    public function __construct()
    {
        $this->missions = new ArrayCollection();
        $this->services = new ArrayCollection();
        $this->servicesassign = new ArrayCollection();
        $this->missionsassign = new ArrayCollection();
    }

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getEmail(): ?string
    {
        return $this->email;
    }

    public function setEmail(string $email): self
    {
        $this->email = $email;

        return $this;
    }

    /**
     * A visual identifier that represents this user.
     *
     * @see UserInterface
     */
    public function getUserIdentifier(): string
    {
        return (string) $this->email;
    }

    /**
     * @see UserInterface
     */
    public function getRoles(): array
    {
        $roles = $this->roles;
        // guarantee every user at least has ROLE_USER
        $roles[] = 'USER_DISCOVER';

        return array_unique($roles);
    }

    public function setRoles(array $roles): self
    {
        $this->roles = $roles;

        return $this;
    }

    /**
     * @see PasswordAuthenticatedUserInterface
     */
    public function getPassword(): string
    {
        return $this->password;
    }

    public function setPassword(string $password): self
    {
        $this->password = $password;

        return $this;
    }

    /**
     * @see UserInterface
     */
    public function eraseCredentials()
    {
        // If you store any temporary, sensitive data on the user, clear it here
        // $this->plainPassword = null;
    }

    public function getName(): ?string
    {
        return $this->name;
    }

    public function setName(string $name): self
    {
        $this->name = $name;

        return $this;
    }

    public function getFirstname(): ?string
    {
        return $this->firstname;
    }

    public function setFirstname(string $firstname): self
    {
        $this->firstname = $firstname;

        return $this;
    }

    public function getCompany(): ?string
    {
        return $this->company;
    }

    public function setCompany(string $company): self
    {
        $this->company = $company;

        return $this;
    }

    public function isVerified(): bool
    {
        return $this->isVerified;
    }

    public function setIsVerified(bool $isVerified): self
    {
        $this->isVerified = $isVerified;

        return $this;
    }

    public function getCompanyadress(): ?string
    {
        return $this->companyadress;
    }

    public function setCompanyadress(string $companyadress): self
    {
        $this->companyadress = $companyadress;

        return $this;
    }

    public function __toString(){
        return $this->name;
    }

    /**
     * @return Collection<int, Mission>
     */
    public function getMissions(): Collection
    {
        return $this->missions;
    }

    public function addMission(Mission $missions): self
    {
        if (!$this->missions->contains($missions)) {
            $this->missions->add($missions);
            $missions->setIduser($this);
        }

        return $this;
    }

    public function removeMission(Mission $missions): self
    {
        if ($this->missions->removeElement($missions)) {
            // set the owning side to null (unless already changed)
            if ($missions->getIduser() === $this) {
                $missions->setIduser(null);
            }
        }

        return $this;
    }

    /**
     * @return Collection<int, Service>
     */
    public function getServices(): Collection
    {
        return $this->services;
    }

    public function addService(Service $service): self
    {
        if (!$this->services->contains($service)) {
            $this->services->add($service);
            $service->setIduser($this);
        }

        return $this;
    }

    public function removeService(Service $service): self
    {
        if ($this->services->removeElement($service)) {
            // set the owning side to null (unless already changed)
            if ($service->getIduser() === $this) {
                $service->setIduser(null);
            }
        }

        return $this;
    }

    /**
     * @return Collection<int, Service>
     */
    public function getServicesassign(): Collection
    {
        return $this->servicesassign;
    }

    public function addServicesassign(Service $servicesassign): self
    {
        if (!$this->servicesassign->contains($servicesassign)) {
            $this->servicesassign->add($servicesassign);
            $servicesassign->addIdserviceassign($this);
        }

        return $this;
    }

    public function removeServicesassign(Service $servicesassign): self
    {
        if ($this->servicesassign->removeElement($servicesassign)) {
            $servicesassign->removeIdserviceassign($this);
        }

        return $this;
    }

    /**
     * @return Collection<int, Mission>
     */
    public function getMissionsassign(): Collection
    {
        return $this->missionsassign;
    }

    public function addMissionsassign(Mission $missionsassign): self
    {
        if (!$this->missionsassign->contains($missionsassign)) {
            $this->missionsassign->add($missionsassign);
            $missionsassign->addIdmissionassign($this);
        }

        return $this;
    }

    public function removeMissionsassign(Mission $missionsassign): self
    {
        if ($this->missionsassign->removeElement($missionsassign)) {
            $missionsassign->removeIdmissionassign($this);
        }

        return $this;
    }
}

And this is my entity Mission

<?php

namespace App\Entity;

use App\Repository\MissionRepository;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: MissionRepository::class)]
#[ORM\Table(name: 'mission')]
class Mission
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\Column(length: 255)]
    private ?string $titlemission = null;

    #[ORM\Column(length: 255)]
    private ?string $descriptionmission = null;

    #[ORM\Column(type: Types::DATETIME_MUTABLE)]
    private ?\DateTimeInterface $onsetdate = null;

    #[ORM\Column(type: Types::DATETIME_MUTABLE)]
    private ?\DateTimeInterface $deadline = null;

    #[ORM\Column]
    private ?int $budgetmission = null;

    /*#[ORM\Column(length: 255)]
    private ?string $codeapemission = null;*/

    #[ORM\Column(length: 255)]
    private ?string $prioritymission = null;

    #[ORM\ManyToMany(targetEntity: Tag::class, inversedBy: 'missions', cascade: ['persist'])]
    #[ORM\JoinTable(name: 'mission_tag')]
    #[ORM\JoinColumn(name: 'mission_id',referencedColumnName: 'id')]
    #[ORM\InverseJoinColumn(name:'tag_id', referencedColumnName: 'id')] //tag_id qui est couplé avec id de tag
    private Collection $tags;

    #[ORM\ManyToOne(targetEntity: User::class, inversedBy: 'missions', cascade: ['persist'], fetch:"EAGER")]
    #[ORM\JoinColumn(nullable: false)]
    private User $iduser;

    #[ORM\ManyToMany(targetEntity: User::class, inversedBy: 'missionsassign')]
    #[ORM\JoinTable(name: 'mission_user')]
    #[ORM\JoinColumn(name: 'mission_id',referencedColumnName: 'id')]
    #[ORM\InverseJoinColumn(name:'user_id', referencedColumnName: 'id')]
    private Collection $idmissionassign;

    #[ORM\Column(length: 100)]
    private ?string $remote = null;

    #[ORM\Column(type: Types::DATETIME_MUTABLE)]
    private ?\DateTimeInterface $datecreation = null;

    public function __construct()
    {
        $this->tags = new ArrayCollection();
        $this->idmissionassign = new ArrayCollection();
    }

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getTitlemission(): ?string
    {
        return $this->titlemission;
    }

    public function setTitlemission(string $titlemission): self
    {
        $this->titlemission = $titlemission;

        return $this;
    }

    public function getDescriptionmission(): ?string
    {
        return $this->descriptionmission;
    }

    public function setDescriptionmission(string $descriptionmission): self
    {
        $this->descriptionmission = $descriptionmission;

        return $this;
    }

    public function getOnsetdate(): ?\DateTimeInterface
    {
        return $this->onsetdate;
    }

    public function setOnsetdate(\DateTimeInterface $onsetdate): self
    {
        $this->onsetdate = $onsetdate;

        return $this;
    }

    public function getDeadline(): ?\DateTimeInterface
    {
        return $this->deadline;
    }

    public function setDeadline(\DateTimeInterface $deadline): self
    {
        $this->deadline = $deadline;

        return $this;
    }

    public function getBudgetmission(): ?int
    {
        return $this->budgetmission;
    }

    public function setBudgetmission(int $budgetmission): self
    {
        $this->budgetmission = $budgetmission;

        return $this;
    }

    /*public function getCodeapemission(): ?string
    {
        return $this->codeapemission;
    }

    public function setCodeapemission(string $codeapemission): self
    {
        $this->codeapemission = $codeapemission;

        return $this;
    }*/

    public function getPrioritymission(): ?string
    {
        return $this->prioritymission;
    }

    public function setPrioritymission(string $prioritymission): self
    {
        $this->prioritymission = $prioritymission;

        return $this;
    }

    /**
     * @return Collection<int, tag>
     */
    public function getIdtagmissionassign(): Collection
    {
        return $this->tags;
    }

    public function addIdtagmissionassign(tag $tags): self
    {
        if (!$this->tags->contains($tags)) {
            $this->tags->add($tags);
        }

        return $this;
    }

    public function removeIdtagmissionassign(tag $tags): self
    {
        $this->tags->removeElement($tags);

        return $this;
    }

    public function getIduser(): ?user
    {
        return $this->iduser;
    }

    public function setIduser(?user $iduser): self
    {
        $this->iduser = $iduser;

        return $this;
    }

    /**
     * @return Collection<int, user>
     */
    public function getIdmissionassign(): Collection
    {
        return $this->idmissionassign;
    }

    public function addIdmissionassign(user $idmissionassign): self
    {
        if (!$this->idmissionassign->contains($idmissionassign)) {
            $this->idmissionassign->add($idmissionassign);
        }

        return $this;
    }

    public function removeIdmissionassign(user $idmissionassign): self
    {
        $this->idmissionassign->removeElement($idmissionassign);

        return $this;
    }

    public function getRemote(): ?string
    {
        return $this->remote;
    }

    public function setRemote(string $remote): self
    {
        $this->remote = $remote;

        return $this;
    }

    public function __toString(){
        return $this->titlemission;
    }

    public function getDatecreation(): ?\DateTimeInterface
    {
        return $this->datecreation;
    }

    public function setDatecreation(\DateTimeInterface $datecreation): self
    {
        $this->datecreation = $datecreation;

        return $this;
    }
}

This is my DQL request but i use Querybuilder without understand the concept. Could you help me ?

public function selectMissionWhereTag($tag){
        return $this->createQueryBuilder('mi')
            ->select('mi.titlemission','mi.descriptionmission','mi.onsetdate','mi.deadline','mi.budgetmission','mi.prioritymission')
            ->leftJoin(User::class,'user', 'WITH', 'mi.iduser_id = user.id')
            ->leftJoin(Tag::class,'tag', 'WITH' , 'mi.tags = tag.id')
            ->where('tag.nomtag = :nomtag')
            ->setParameter('nomtag',$tag)
            ->getQuery()
            ->getResult();
    }

Many thanks :)

Answer 1

To better understand how the QueryBuilder works I would recommend reading through how Doctrine ORM Mapping works first: Basic Mapping and Association Mapping

QueryBuilder's Joins use this mapping in a way that looks pretty similar to how you would do things with getters in your PHP code.

For example in a controller to get the tags of a mission you would do something like $tags = $mission->getTags(); well in QueryBuilder you simply do ->leftJoin('mi.tags', 'tag') and that's it. You don't have to worry about WITH unless you need extra conditions.

As you might have guessed there is a lot more to it but this should be enough to get you going.