Symfony QueryBuilder

Question

I'm making a website which show some projet with a menu. In this menu you can choose a language to filter projects. My problem is with the query to get the project for a specific language. I have 2 entity, Project and Language :

Project :

<?php

namespace App\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

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

    /**
     * @ORM\ManyToMany(targetEntity="App\Entity\Language",     inversedBy="projects")
     */
    private $languages;

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


    /**
     * @return Collection|Language[]
     */
    public function getLanguages(): Collection
    {
        return $this->languages;
    }

    public function addLanguage(Language $language): self
    {
        if (!$this->languages->contains($language)) {
            $this->languages[] = $language;
        }

        return $this;
    }

    public function removeLanguage(Language $language): self
    {
        if ($this->languages->contains($language)) {
            $this->languages->removeElement($language);
        }

        return $this;
    }
}

Language :

<?php

namespace App\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

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

    /**
     * @ORM\ManyToMany(targetEntity="App\Entity\Project", mappedBy="languages")
     */
    private $projects;


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

    /**
     * @return Collection|Project[]
     */
    public function getProjects(): Collection
    {
        return $this->projects;
    }

    public function addProject(Project $project): self
    {
        if (!$this->projects->contains($project)) {
            $this->projects[] = $project;
            $project->addLanguage($this);
        }

        return $this;
    }

    public function removeProject(Project $project): self
    {
        if ($this->projects->contains($project)) {
            $this->projects->removeElement($project);
            $projet->removeLangage($this);
        }

        return $this;
    }
}

In my database they are related by project_language, which have there id

And here my current query which doesn't work :

$init = 'language_C_1';
$array = explode('_', $init);

$repo = $this->getDoctrine()->getRepository(Project::class);

$init = $array[2];
$query = $repo->createQueryBuilder('p')
    ->innerJoin('project_language', 'pl', Join::WITH, 'p.id = pl.project_id')
    ->innerJoin('language', 'l', Join::WITH, 'pl.language_id = l.id')
    ->where('l.id = :init')
    ->setParameter('init', $init)
    ->getQuery();

$arrayProjets = $query->getResult();

Thank for your answer !

Answer 1

Doctrine allows you to do an abstraction of the database. You shouldn't think with database table but with entity instead : Your Project Entity has a $languages property so you can use it in your queryBuilder like this:

$repo
->createQueryBuilder('p')
// add this to also load the languages entities
->addSelect('l')
->innerJoin('p.languages', 'l')
->where('l.id = :languageId')
->setParameter('languageId', $languageId)
->getQuery();

I did the join on the property $languages of the Product entity : The join table (here project_language) is totally transparent!