How to write a DQL query with entity relationships?

Question

symfony

Good afternoon.
Need help making a request. There is a product table and a category table with two levels. I am attaching the YML clippings from which the entities in Symfony are generated.
item

manyToOne:
    category:
        targetEntity: Category
        cascade: {  }
        mappedBy: null
        inversedBy: item
        joinColumns:
            category:
                referencedColumnName: id
        orphanRemoval: false

In turn
Category

oneToMany:
    childrenCategory:
        targetEntity: Category
        mappedBy: parentCategory
    item:
        targetEntity: Item
        mappedBy: category
manyToOne:
     parentCategory:
        targetEntity: Category
        cascade: {  }
        mappedBy: null
        inversedBy: childrenCategory
        joinColumns:
            parent_category:
                referencedColumnName: id
        orphanRemoval: false

To find products by final category, everything is simple here. They are in the item table itself.

$query = $query->where('i.category = :category')
                ->setParameter('category', $categoryId);

But I want to find products by parent category. In theory, we need to do a join on category.id on item.category and then add a condition with my parentId.
Sketched like this:

$query = $query->join('i.category', 'c', 'WITH', 'i.id = c.parentCategory')
            ->where('c.parentCategory = :mainCategory')
            ->setParameter('mainCategory', $mainCategoryId);

As a result, nothing, as we decrypt the request, we see:

SELECT DISTINCT c0_.id AS id0, c0_.id AS id1 FROM crbr_item c0_ INNER JOIN crbr_category c1_ ON c0_.category = c1_.id AND (c0_.id = c1_.parent_category) WHERE c1_.parent_category = '20

There is clearly an extra line
AND (c0_.id = c1_.parent_category)
How to get rid of it? I don’t want to use pure SQL, but still DQL, especially since there are relationships, including the selection of children, and you can do it the right way. Thank you!

Answer 1

$query->innerJoin('i.category', 'c')
...