My left join is not working in Symfony4

Question

I'm new to Symfony and am trying to do a basic left join to pull in a clients name based on an client_id in the Jobs table.

App\Entity\Jobs:

class Jobs
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\ManyToOne(targetEntity="App\Entity\Clients", inversedBy="jobs")
     * @ORM\JoinColumn(nullable=false)
     */
    private $client;

Should join to App\Entity\Clients:

class Clients
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\OneToMany(targetEntity="App\Entity\Jobs", mappedBy="client")
     */
    private $jobs;

In my App\Repository\JobsRepository class, the following function is attempting the left join:

use App\Entity\Jobs;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Symfony\Bridge\Doctrine\RegistryInterface;

class JobsRepository extends ServiceEntityRepository
{
    public function __construct(RegistryInterface $registry)
    {
        parent::__construct($registry, Jobs::class);
    }

    public function allWithClientName()
    {
        return $this->createQueryBuilder('job')
                ->leftJoin('job.client_id', 'client')
                ->getQuery()
                ->execute();
    }
}

The error I'm getting returned is:

[Semantical Error] line 0, col 60 near 'client': Error: Class App\Entity\Jobs has no association named client_id

Any ideas? As far as my limited understanding goes, the annotations should form the required join.

Answer 1

I don't see any call to get the entityManager.

You either, forgot to call getRepository() before createQueryBuilder() or forgot to add from in your query.

Avoid to use alias that could be mistaken for entity names

Use double quote to surround your query. Because you will have an error when you will happen to write something like where('e.param="string"') as double quote isn't allowed for string in DQL.

Change $client_id to $client. Going by how doctrine does things, if left as is, you will have client_id_idas SQL column when updating schema.

Avoid plurals entities... Donctrine don't like them... ;)

Here is a full code correction

Jobs

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

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Clients", inversedBy="id")
     * @ORM\JoinColumn(nullable=false)
     */
    private $client;
}

Client

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

    /**
     *@ORM\OneToMany(targetEntity="App\Entity\Jobs", mappedBy="client")
     */
    private $jobs
}

JobsRepository Solution 1

use App\Entity\Jobs

class JobsRepository extends \Doctrine\ORM\EntityRepository {
    public function repoQuery() {
        $em=$this->getEntityManager();

        return $em->getRepository(Jobs::class)
                  ->createQueryBuilder("j")
                  ->leftJoin("j.client", "c")
                  ->getQuery()
                  ->execute();
    }
}

JobsRepository Solution 2

use App\Entity\Jobs

class JobsRepository extends \Doctrine\ORM\EntityRepository {
    public function repoQuery() {
        $em=$this->getEntityManager();

        return $em->createQueryBuilder()
                  ->from(Jobs::class, "j")
                  ->leftJoin("j.client", "c")
                  ->getQuery()
                  ->execute();
    }
}

Answer 2

The below code changes resolved it for me. It appears that

• the annotations required are @ORM\Entity on the class itself, pointing at the repository class
• for the property the join annotation is the only one required (@ORM\ManyToOne)

As per Preciels comment, the @ORM\Entity(repositoryClass=?) annotation at the top of the class is incredibly important.

Working code

App\Entity\Jobs:

/**
 * @ORM\Entity(repositoryClass="App\Repository\JobsRepository")
 */
class Jobs
{

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Clients", inversedBy="client")
     */
    private $client;

App\Entity\Clients:

/**
 * @ORM\Entity(repositoryClass="App\Repository\ClientsRepository")
 */
class Clients
{

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\Jobs", mappedBy="client", cascade={"persist", "remove"})
     */
    private $client;

App\Repository\JobsRepository:

/**
 * @return array
 */
public function allWithClientName() {


    return $this->createQueryBuilder('j')
                ->select('j as job', 'c.name as client')
                ->leftJoin('j.client', 'c')
                ->getQuery()
                ->execute();


}

Answer 3

use Doctrine\Common\Collections\ArrayCollection;
////////////////////////////////////////////////////

class Clients
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     * @ORM\OneToMany(targetEntity="App\Entity\Jobs", mappedBy="client_id")
     */
    private $jobs; // Here change your $id -> to $jobs 

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

In your Repository:

public function allWithClientName()
{
    return $this->createQueryBuilder('job')
                ->leftJoin('job.client_id', 'my_client')
                ->addSelect('my_client') 
                ->getQuery()
                ->execute();
}