I’m going to run the following MySQL query with Doctrine (it works as expected on MySQL side):
select latest.product_id, latest.last_read_at
from attributes latest
left join attributes bigger
on latest.product_id = bigger.product_id and
latest.last_read_at <= bigger.last_read_at and
latest.id < bigger.id
where bigger.last_read_at is null;
Here is my try with Doctrine:
$queryBuilder = $this->entityManager->createQueryBuilder();
$categoryVersions = $queryBuilder
->select('latest.product_id', 'latest.last_read_at')
->from($entityClass, 'latest')
->leftJoin(
$entityClass,
'bigger',
Join::ON,
$queryBuilder->expr()->andX(
$queryBuilder->expr()->eq('latest.product_id', 'bigger.product_id'),
$queryBuilder->expr()->lte('latest.last_read_at', 'bigger.last_read_at'),
$queryBuilder->expr()->lt('latest.id', 'bigger.id')
)
)
->where('bigger.last_read_at is NULL')
->getQuery()
->getResult();
Which transforms into DQL:
SELECT latest.product_id, latest.last_read_at
FROM MyNamespaceEntityAttribute latest
LEFT JOIN MyNamespaceEntityAttribute bigger ON
latest.product_id = bigger.product_id AND
latest.last_read_at <= bigger.last_read_at AND
latest.id < bigger.id
WHERE bigger.last_read_at is NULL
And here is the result:
[DoctrineORMQueryQueryException]
[Syntax Error] line 0, col 156: Error: Expected end of string, got 'ON'
Can you please help me with building the correct DQL?