Multiple joins in a ManyToMany relation

Question

I have 3 entities, User, Parking and Voiture. User had a ManyToMany relation with parking while Parking has a OneToMany reamtion with voiture.

what I'm trying to do:

Get the cars (voitures) that belong to all parkings the currect user is related to

how I'm trying to do it:

Using querybuilder, but I still don't know how to make it work

here are my entities

Entity User:

<?php

/**
 * @ORM\Entity
 * @UniqueEntity(fields="username", message="Username already taken")
 */
class User implements UserInterface
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;
    public function getId()
    {
        return $this->id;
    }


    /**
     * @ORM\Column(type="string", length=191, unique=true)
     * @Assert\NotBlank()
     */
    private $username;


    /**
     * @ORM\ManyToMany(targetEntity="App\Entity\Parking", mappedBy="agents")
     */
    private $parkings;
    public function __construct()
    {
        $this->parkings = new ArrayCollection();
    }



    /**
     * @return Collection|Parking[]
     */
    public function getParkings(): Collection
    {
        return $this->parkings;
    }

    public function addParking(Parking $parking): self
    {
        if (!$this->parkings->contains($parking)) {
            $this->parkings[] = $parking;
            $parking->addAgent($this);
            return $this;
        }

        return $this;
    }

    public function removeParking(Parking $parking): self
    {
        if ($this->parkings->contains($parking)) {
            $this->parkings->removeElement($parking);
            $parking->removeAgent($this);
        }

        return $this;
    }
}

Entity Parking:

<?php

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

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

    /**
     * @ORM\ManyToMany(targetEntity="App\Entity\user", inversedBy="parkings")
     */
    private $agents;

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\Voiture", mappedBy="parking", orphanRemoval=true)
     */
    private $voitures;


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

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


    /**
     * @return Collection|user[]
     */
    public function getAgents(): Collection
    {
        return $this->agents;
    }

    public function addAgent(user $agent): self
    {
        if (!$this->agents->contains($agent)) {
            $this->agents[] = $agent;
        }

        return $this;
    }

    public function removeAgent(user $agent): self
    {
        if ($this->agents->contains($agent)) {
            $this->agents->removeElement($agent);
        }

        return $this;
    }

    /**
     * @return Collection|Voiture[]
     */
    public function getVoitures(): Collection
    {
        return $this->voitures;
    }

    public function addVoiture(Voiture $voiture): self
    {
        if (!$this->voitures->contains($voiture)) {
            $this->voitures[] = $voiture;
            $voiture->setParking($this);
        }

        return $this;
    }

    public function removeVoiture(Voiture $voiture): self
    {
        if ($this->voitures->contains($voiture)) {
            $this->voitures->removeElement($voiture);
            // set the owning side to null (unless already changed)
            if ($voiture->getParking() === $this) {
                $voiture->setParking(null);
            }
        }

        return $this;
    }

}

And Entity Voiture

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

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

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


    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Parking", inversedBy="voitures")
     * @ORM\JoinColumn(nullable=false)
     */
    private $parking;

    /**
     * @ORM\Column(type="boolean")
     */
    private $parked;



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

    public function getMatricule(): ?string
    {
        return $this->matricule;
    }

    public function setMatricule(string $matricule): self
    {
        $this->matricule = $matricule;

        return $this;
    }



    public function getParking(): ?Parking
    {
        return $this->parking;
    }

    public function setParking(?Parking $parking): self
    {
        $this->parking = $parking;

        return $this;
    }

}

My question to you is, why do you have a many-to-many relationship? In database design, when you have a many-to-many relationship like this, there's usually a good way to break them up, such as linking a user to a parking event as a one user to many parking events and many parking events to one parking, which resolves this many to many issue if many agents are in fact required in one parking object. Your current set up makes me think one agent per parking is adequate though. — Dan Scott, May 01 '19 at 12:36
well, user can administer many parkings at the same time (it's the client's idea) and parking can have many users. I know it's impractical but I'm gonna have to do it and see if I get the choice to make it simpler when I'm finished. — Nova, May 01 '19 at 12:40

score 1 · Answer 1 · answered May 01 '19 at 21:37

Start from Voiture entity and inner join the Parking and User entities using their associations:

$queryBuilder = $this->getDoctrine()->getRepository('App:Voiture')->createQueryBuilder('v');
$queryBuilder->innerJoin('v.parking', 'p');
$queryBuilder->innerJoin('v.agents', 'a');

Finally, you can filter on the relation either through a condition:

$queryBuilder->where('a.id = :userId');
$queryBuilder->setParameter("userId", 1);

$cars = $queryBuilder->getQuery()->getResult();

or place the condition on the $queryBuilder->innerJoin('v.agents', 'a', 'WITH', 'a.id = :userId'); see doctrine inner join with condition

References

SQL join: where clause vs. on clause

ASSILI Taher · Accepted Answer · 2019-05-02T04:51:41.367

I propose to add an intermediate entity UserParking between User and Parking entities. So we have a OneToMany relationship between User and UserParking and a OneToMany relationship between Parking and UserParking instead of ManyToMany relationship between User and Parking.

The entities will be similar to code below:

Entity User:

<?php

/**
 * @ORM\Entity
 * @UniqueEntity(fields="username", message="Username already taken")
 */
class User implements UserInterface
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;
    public function getId()
    {
        return $this->id;
    }


    /**
     * @ORM\Column(type="string", length=191, unique=true)
     * @Assert\NotBlank()
     */
    private $username;


    /**
     * @ORM\OneToMany(targetEntity="App\Entity\UserParking", mappedBy="agent")
     * @ORM\JoinColumn(nullable=true)
     */
    private $user_parking;

// getter and setter
}

Entity Parking:

<?php

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

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

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\UserParking", mappedBy="parking")
     * @ORM\JoinColumn(nullable=true)
     */
    private $user_parking;

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\Voiture", mappedBy="parking", orphanRemoval=true)
     */
    private $voitures;

// getters and setters

}

Entity UserParking

/**
 * UserParking
 *
 * @ORM\Table(name="user_parking")
 * @ORM\Entity(repositoryClass="App\Repository\UserParkingRepository")
 */
class UserParking
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\User", inversedBy="user_parking")
     * @ORM\JoinColumn(nullable=false)
     */
    private $agent;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Parking", inversedBy="user_parking")
     * @ORM\JoinColumn(nullable=false)
     */
    private $parking;

    // getter and setter
}

And Entity Voiture

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

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

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


    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Parking", inversedBy="voitures")
     * @ORM\JoinColumn(nullable=false)
     */
    private $parking;

    /**
     * @ORM\Column(type="boolean")
     */
    private $parked;
}

So to get the cars (voitures) that belong to all parkings the current user is related to you need to:

1- Get the current user object.

2- Get the UserParking array from the user object .

3- Get the Parking objects from the UserParking array.

4- Get the cars from the Parking objects.

The code will be similar to this:

$em = $this->getDoctrine()->getManager();

/* Get user from the session */
$user = $this->getUser();

$userParkings = $user->getUserParking();
$parkings = [];
foreach ($userParkings as $item) {
    $parking = $item->getParking();
    $parkings[count($parkings)] = $parking;
}
// you can get voitures from parkings

Thanks it works, same with Jannes' answer. I chose this as the right answer since I used it. — Nova, May 05 '19 at 09:19

Multiple joins in a ManyToMany relation

2 Answers2