CODEWITHSUNDEEP
phpsymfonydoctrine
loleN
loleN

Reputation: 11

Doctrine and left join with condition

I have a problem in the relationship when I use condition. My Post entity:

/**
 * @ORM\ManyToOne(targetEntity=User::class, inversedBy="posts")
 * @ORM\JoinColumn(nullable=false)
 */
private $user;

/**
 * @ORM\Column(type="smallint", nullable=true)
 */
private $type;

and User entity

/**
 * @ORM\OneToMany(targetEntity=Post::class, mappedBy="user")
 */
private $posts;

The problem exists when the repository uses this condition:

return $this->createQueryBuilder('p')
    ->addSelect('p', 'u', 'up')
    ->leftJoin('p.user', 'u')
    ->leftJoin('u.posts', 'up', 'WITH', 'p.type = up.type')
    ->getQuery()
    ->getResult()
;

With such data in the database image.png

I get these results. In the first post, for the user the posts should be of the same type. (there should be no posts with id 3 and 5). If I use getArrayResult() instead of getQuery(), everything works fine, but I need entities. So it's probably due to optimization. Does anyone know how to deal with it?

Additionally adds github source

Upvotes: 1

Views: 1700

Answers (1)

redecs
redecs

Reputation: 111

I think you need to use Criteria to filter your collection (and add the necessary filters on the join performed by the ORM when mapping the data not when calling the method in the repository).

You can find a hands-on example of Criteria usage in Symfony here (even the video is not available you do have the code and the explanations).

In the end, moving the WITH condition from the join (correct from the SQL standpoint) to a Criteria (useful with the ORM) should solve the problem you are having.

Upvotes: 1

Related Questions