0

I have 2 entities/tables:

News
 - id
 - company_id

NewsReader
 - id
 - news_id
 - reader_id

I want to get count unread news for current user.

I write query with query builder:



    $companyId = 1;
        $userId = 1;
        $qb = $this->_em->createQueryBuilder();
        $qb->from(News::class, 'n');

        $qb->select($qb->expr()->count('n.id'))
            ->leftJoin(
                NewsReader::class,
                'nr',
                \Doctrine\ORM\Query\Expr\Join::ON,
                'nr.news_id = n.id AND nr.reader_id = :userId'
            )
            ->andWhere('n.company_id = :companyId')
            ->andWhere('nr.id IS NULL')
            ->setParameter('companyId', $companyId)
            ->setParameter('userId', $userId);

        $query = $qb->getQuery();

        return (int)$query->getSingleScalarResult();

DQL is there:


    SELECT 
     COUNT(n.id) 
    FROM App\Entity\News n 
    LEFT JOIN App\Entity\NewsReader nr 
       ON nr.news_id = n.id AND nr.reader_id = :userId 
    WHERE n.company_id = :companyId AND nr.id IS NULL

But i've got an error:

[Syntax Error] line 0, col 77: Error: Expected end of string, got 'ON'

Whats wrong?

If i write raw query to database in console - all works correctly.

Nikolas Sumrak
  • 100
  • 2
  • 7
  • 4
    Does this answer your question? [Doctrine 2 JOIN ON error](https://stackoverflow.com/questions/8939148/doctrine-2-join-on-error) – Jeroen May 11 '20 at 05:51
  • You do not join with entities, rather you join with properties and it will resolve underlying related Entity. Answer would be : `LEFT JOIN 'n.news_reader'` given you have setup relationships with doctrine. – Maulik Parmar May 11 '20 at 07:15
  • I assume that you didn't link your entities at all. So instead of ON use WITH – Jakumi May 11 '20 at 17:25

0 Answers0