Transform SQL to DQL

Question

I tried to transform a SQL function into DQL but i always have errors...

I have 3 tables

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

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

    /**
     * @Gedmo\Slug(fields={"title"})
     * @ORM\Column(type="string", length=255, unique=true, nullable=true)
     */
    private $slug;

   /**
     * @ORM\Column(type="boolean", options={"default":"0"})
     */
    private $isItalian = false;

    /**
     * @ORM\Column(type="text")
     */
    private $content;

    /**
     * @ORM\ManyToMany(targetEntity=Contact::class, inversedBy="viewedArticles")
     */
    private $contact;
}

class Contact implements UserInterface, JsonSerializable
{

    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    protected ?int $id = null;

    /**
     * @ORM\Column(type="string", length=255, unique=true)
     */
    protected ?string $email = null;


    /**
     * @ORM\ManyToMany(targetEntity=Article::class, mappedBy="contact")
     */
    private $viewedArticles;
}

And a many to many table article_contact (a manyToMany table) with article_id and contact_id

i want to get all articles who are not in the article_contact table (it's all the articles who're not viewed by the contact)

I tried this request in SQL and it works, but it return an array and i need to return an object to use it in Symfony

$conn = $entityManager->getConnection();
        $sql = 'SELECT * 
                FROM article
                LEFT JOIN article_contact 
                ON article.id = article_id
                AND contact_id = '.$id.'
                WHERE article.is_italian = '.$isItalian.'
                AND article_id IS NULL';

        $stmt = $conn->prepare($sql);
        $result = $stmt->executeQuery();
        return $result->fetchAllAssociative();

DQL request :

return $this->createQueryBuilder('a')
            ->leftJoin('a.contact', 'contact')
            ->where('a.isItalian = 0')
            ->andWhere('contact = 1')
            ->andWhere('contact.viewedArticles IS NULL')
            ->getQuery()
            ->getResult()
        ;

it's the ->andWhere('contact.viewedArticles IS NULL') part who's not working, i have this error

[Semantical Error] line 0, col 113 near 'viewedArticles': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.

*but i always have error*, what are those errors? What are your Entity classes? — Will B., Nov 03 '21 at 02:18
I try some DQL but i don't have results that i want, the only thing that "works" is when i use SQL in my repository like the exemple i use in my post :/ i'm on the ArticleRepository — Dunkeur, Nov 03 '21 at 08:25
Does this answer your question? [Doctrine 2 DQL - Select rows where a many-to-many field is empty?](https://stackoverflow.com/questions/10514019/doctrine-2-dql-select-rows-where-a-many-to-many-field-is-empty) — Will B., Nov 03 '21 at 16:15
When checking against a collection use `contact.viewedArticles IS EMPTY` instead of `IS NULL`. Provided your entity has the `Contact::$viewedArticles` property. — Will B., Nov 03 '21 at 16:16
@WillB. i had post my entity properties at the beginning of my post i saw this link earlier, it does almost the thing that i want, but with this i can see all articles who are never viewed by a contact. I need one more condition : i want to see all articles who are not viewed by a SPECIFIC contact (in my example, i want to show all articles not viewed by contact with id=1) — Dunkeur, Nov 03 '21 at 17:31
I asked for the source code, not an abstraction of it. For your specific condition, you would limit the contact subset on the join, `->leftJoin('a.contact', 'contact', 'WITH', 'contact.id = :id')->setParameter('id', 1)` — Will B., Nov 03 '21 at 17:38
Well, you just give me the answer that i needed ! I will post the DQL completed in another answer Thank you @Will B. — Dunkeur, Nov 03 '21 at 17:55

score 0 · Answer 1 · answered Nov 03 '21 at 17:57

The answer is

$qb->select('a')
            ->from(Article::class,'a')
            ->leftJoin('a.contact', 'contact', 'WITH', 'contact.id = :id')
            ->setParameter('id', $contactId)
            ->andWhere('a.isItalian = :isItalian')
            ->setParameter('isItalian', $isItalian)
            ->groupBy('a.id')
            ->having('COUNT(contact.id) = 0')
        ;

        return $qb->getQuery()->getResult();

Thanks to Will B. !

Transform SQL to DQL

1 Answers1