Searching by association’s index in Doctrine 2

August 6th, 2013

I stumbled upon quite an interesting problem in Doctrine 2 with ManyToOne associations. Consider this entity:

class Comment {
  /**
   * @ORM\ManyToOne(targetEntity="Article", inversedBy="comments")
   * @ORM\JoinColumn(name="article_id", referencedColumnName="id")
   */
  protected $article;

  /**
   * @ORM\ManyToOne(targetEntity="User", inversedBy="comments")
   * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
   */
  protected $user;

  /**
   * @ORM\Column(type="text")
   */
  protected $comment;
}

A user can leave a comment on an article. Later, I attempt to query an article with comments made by a specific user. This is the DQL that I wrote:

SELECT a, c FROM Article a
LEFT JOIN a.comments c WITH c.user_id = :user_id
WHERE a.id = :article_id

This makes perfect sense, since Doctrine added a user_id to the database for me. But this DQL it throws me a semantical error: " Comment has no field or association named user_id".

I thought that I may have misconfigured my associations, but it wasn't the case. I even considered that Doctrine 2 might have a bug. After searching online for over an hour, I didn't find anyone having the same problem.

I tried something quite random. I changed "c.user_id = "user_id" to "c.user = :user_id". It worked! It's not intuitive, because you don't expect a comparison between an entity and an index to work. I'm not sure whether it's an official method, since I found nothing regarding this in the documentation. Any clarification is welcome.

Here is the final query for your reference:

SELECT a, c FROM Article a
LEFT JOIN a.comments c WITH c.user = :user_id
WHERE a.id = :article_id

Previous: Methodologies are not Infallible or Timeless Next: There’s more to startups than unicorns