CODEWITHSUNDEEP
symfonydoctrine
FosAvance
FosAvance

Reputation: 2469

How to filter relationship in doctrine

I have a Post entity and inside of it I have a relationship:

/**
 * @ORM\OneToOne(targetEntity="Comment", mappedBy="comment", cascade={"remove"}, orphanRemoval=true)
 * @ORM\JoinColumn(name="comment_id", referencedColumnName="id", onDelete="SET NULL", nullable=true)
 */
private Comment $comment;

I'm fetching multiple Posts and with each Post entity I only get one Comment entity. I need to fetch comments which have "state": "published".

I tried to do it with join:

$qb
->leftJoin(Comment::class,
    'comments',
    Join::WITH,
    'post.comment = comments.id AND comments.state = :publishedState'
)
->setParameter('publishedState', 'published')

It does not work, it always returns one comment regardless of state.

So I need to fetch Post entites but if Comment.state != 'published' then Comment relationship will be empty, otherwise it won't. How can I filter comments by state?

Laravel has this:

Posts::with(['comments' => function($query) {
  $query->where('state', 'published');
}])->get();

Upvotes: 0

Views: 960

Answers (2)

Agnohendrix
Agnohendrix

Reputation: 610

You could split join and where in two separated methods:

$qb
->leftJoin(Comment::class,
'comments',
Join::WITH,
'post.comment = comments.id'
)
->andWhere('comments.state = :publishedState')
->setParameter('publishedState', 'published')

Upvotes: 0

Twisterr1000
Twisterr1000

Reputation: 49

You should change the relationship to be a OneToMany relationship. The post entity should read something along the lines of:

/**
 * @ORM\OneToMany(targetEntity="Comment", mappedBy="post")
 */
private $comments;

With the comment entity looking something like this:

/**
 * @ORM\ManyToOne(targetEntity="Post", inversedBy="comments")
 * @ORM\JoinColumn(name="post_id", referencedColumnName="id")
 */
private Post $post;

Upvotes: 1

Related Questions