[FIXED] get children from many-to-many self-referenced entity in doctrine

Issue

I have an entity Article is self-referenced.

Article {
    
    //primary key
    private $id;

    //unique
    private $slug;

    /**
     * @ORM\ManyToMany(targetEntity=self::class, inversedBy="parents")
     * @ORM\JoinTable(
     *     name = "article_article",
     *     joinColumns = {@ORM\JoinColumn(name = "article_source", referencedColumnName = "id")},
     *     inverseJoinColumns = {@ORM\JoinColumn(name = "article_target", referencedColumnName = "id")}
     * )
     */

    private ?Collection $children;

    /**
     * @ORM\ManyToMany(targetEntity=self::class, mappedBy="children")
     */
    private ?Collection $parents;
}

I get article slug from request, and i need to get all related articles for this slug. I am trying to do this with dql, but even simple query

$queryBuilder = $this->createQueryBuilder('a');
        $queryBuilder->where('a.children = 1');

gives me

Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected."

So I need this sql

select a.slug from article a where a.id in (
    select aa.article_target from article a2 inner join article_article aa on a2.id = aa.article_source
        where a2.slug = 'article_slug'
    )

translate into dql, can anybody help me? And how can i address to table article_article from dql?

p.s. This variant is working, but not suitable for me, for some reasons, i need dql

$article = $repository->findOneBy($criteria);
$children = $article->getChildren();

Solution

In Doctrine query builder, you use entities (the PHP class value) as parameters. You want to find articles that have a particular Article in its Collection of parents/children. You want to use the MEMBER OF operator.

Repository:

  public function findOneBySlug($slug): ?Article
  {
    return $this->createQueryBuilder('a')
                ->andWhere('a.slug = :val')
                ->setParameter('val', $slug)
                ->getQuery()
                ->getOneOrNullResult()
      ;
  }

  public function findChildrenOf(Article $article)
  {
    return $this->createQueryBuilder('a')
                ->where(':art MEMBER OF a.parents')
                ->setParameter('art', $article)
                ->getQuery()
                ->getResult()
      ;
  }

  public function findParentsOf(Article $article)
  {
    return $this->createQueryBuilder('a')
                ->where(':art MEMBER OF a.children')
                ->setParameter('art', $article)
                ->getQuery()
                ->getResult()
      ;
  }

Controller:

$article = $repository->findOneBySlug($slug);
$children = $repository->findChildrenOf($article);
$parents = $repository->findParentsOf($article);

Doctrine will automatically determine the fields and tables it needs to query, and will build the query for the configured database.

Answered By - Arleigh Hix