Syntax Error line 0, col 60: Error: Expected end of string, got 'ON' - Symfony 4/Doctrine2

Question

I have problem with query builder using Doctrine 2 in Symfony 4.4 and Omines/Datatables Bundle.

I have two entities, User and Log, which look like this:

<?php

namespace App\Entity;

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

/**
 * @ORM\Entity(repositoryClass=UserRepository::class)
 */
class User
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

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

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

    /**
     * @ORM\OneToMany(targetEntity=Log::class, mappedBy="user")
     */
    private $logs;



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

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

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

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

        return $this;
    }

    public function getLastname(): ?string
    {
        return $this->lastname;
    }

    public function setLastname(string $lastname): self
    {
        $this->lastname = $lastname;

        return $this;
    }

    /**
     * @return Collection|Log[]
     */
    public function getLogs(): Collection
    {
        return $this->logs;
    }

    public function addLog(Log $log): self
    {
        if (!$this->logs->contains($log)) {
            $this->logs[] = $log;
            $log->setUser($this);
        }

        return $this;
    }

    public function removeLog(Log $log): self
    {
        if ($this->logs->contains($log)) {
            $this->logs->removeElement($log);
            // set the owning side to null (unless already changed)
            if ($log->getUser() === $this) {
                $log->setUser(null);
            }
        }

        return $this;
    }
}

Entity Log:

<?php

namespace App\Entity;

use App\Repository\LogRepository;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass=LogRepository::class)
 */
class Log
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;
    
    /**
     * @ORM\Column(type="datetime")
     */
    private $logStart;

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

    /**
     * @ORM\ManyToOne(targetEntity=User::class, inversedBy="logs")
     * @ORM\JoinColumn(name="user_id", referencedColumnName="id", nullable=false)
     */
    private $user;

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

    public function setLogStart(\DateTimeInterface $logStart): self
    {
        $this->logStart = $logStart;

        return $this;
    }

    public function getIp(): ?string
    {
        return $this->ip;
    }

    public function setIp(string $ip): self
    {
        $this->ip = $ip;

        return $this;
    }

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

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

        return $this;
    }
}

I also use the omines/datatables bundle (Documentation and link to github)

I tried to build a query with a left join to my User entity. My code in the controller is as following:

$table = $this->datatableFactory->create([])
            ->add('id', TextColumn::class, ['label' => '#', 'className' => 'bold', 'searchable' => true])
            ->add('firstname lastname', TextColumn::class, ['label' => $translator->trans('Customer name'), 'className' => 'bold', 'searchable' => true])
            ->add('logStart', DateTimeColumn::class, ['label' => $translator->trans('Time'), 'className' => 'bold', 'searchable' => false])
            ->createAdapter(ORMAdapter::class, [
                'entity' => Log::class,
                'query' => function (QueryBuilder $queryBuilder) {
                    $queryBuilder
                        ->select('l, u.firstname, u.lastname')
                        ->from(Log::class, 'l')
                        ->leftJoin(User::class, 'u', Join::ON, 'l.user = u');                        
                }
            ]);
            $table->handleRequest($request);
        
        
        if ($table->isCallback()) {
            return $table->getResponse();
        }  

And I got the following error: Syntax Error line 0, col 60: Error: Expected end of string, got 'ON'

But when I changed the following: ->leftJoin(User::class, 'u', Join::ON, 'l.user = u');

to: ->leftJoin(User::class, 'u', Join::WITH, 'l.user = u.id'); I get the following error:

Cannot read property "id" from an array. Maybe you intended to write the property path as "[id]" instead.

Does anyone have an idea what I'm doing wrong? Thank you for every help :)

EDIT:

I found a solution on github and I changed my code to:

            ->createAdapter(ORMAdapter::class, [
            'hydrate' => \Doctrine\ORM\Query::HYDRATE_ARRAY,
            'entity' => Log::class,
            'query' => function (QueryBuilder $queryBuilder) {
                $queryBuilder            
                    ->select('l, u')
                    ->from(Log::class, 'l')
                    ->leftJoin('l.user', 'u');                                                                   
            }
        ]);

but this didn't change anything for me. I still don't have access to the User entity (in this case for example firstname and lastname).

Answer 1

Problem solved. In many to one relation we must use 'field' option in column. For example:

$table = $dataTableFactory->create()
    ->add('firstName', TextColumn::class, ['label' => 'Firstname', 'field' => 'user.firstname'])
    ->add('logStart', DateTimeColumn::class, ['label' => 'Log start'])
    ->createAdapter(ORMAdapter::class, [
        'entity' => Log::class,
    ])
    ->handleRequest($request);