Doctrine 2.1 where foreign key id = ?, edit: Fixed in Doctrine 2.2

Question

I have looked at many answers here related to what seems to be a serious lack of functionality in Doctrine 2.1, which may be the result of the correct OOP accuracy associated with relational sanity.

I have two tables with many relationships, articles and members. A participant can have many published articles. Owner side annotations

/** * @var \Member * @ORM\ManyToOne(targetEntity="Member") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="member_id", referencedColumnName="id") * }) */ private $member; 

I want to get all active articles for member 6, this is a simple query in SQL:

 SELECT * FROM mbr_article WHERE active = 1 AND member_id = 6 ORDER BY article_id DESC 

What I finished was

 $rep = $this->getDoctrine()->getRepository('SMWMemberBundle:Article'); $q = $rep->createQueryBuilder('a') ->leftJoin('a.member','m') ->where('m.id = ?1') ->andWhere('a.active = 1') ->orderBy('a.id', 'DESC') ->setParameter(1, $id) ->getQuery(); 

which generated

 SELECT m0_.id AS id0, m0_.active AS active1, m0_.update_time AS update_time2, m0_.content AS content3, m0_.member_id AS member_id4 FROM mbr_article m0_ LEFT JOIN mbr_member m1_ ON m0_.member_id = m1_.id WHERE m1_.id = ? AND m0_.active = 1 ORDER BY m0_.id DESC 

which works and probably not much slower, but no JOIN is needed, as I already have a Member object. When I tried it differently, all of my articles were not just active.

I saw answers such as Can you get the foreign key of an object in Doctine2 without loading this object? which uses getEntityIdentifier , and mentions the improvements coming in 2.2, where I could say IDENTITY(member) .

Is there any reasonable way to do this in Doctrine 2.1? Will the extension allow andWhere('IDENTITY(member) = ?') In the query builder?

Edit:

Thanks @Ocramius, → where ('IDENTITY (a.member) =? 1') works in Doctrine 2.2

Answer 1

IDENTIFICATION works in WHERE clauses created by the query designer in Doctrine 2.2, for example.

 $q = $rep->createQueryBuilder('a') ->where('IDENTITY(a.member) = ?1') ->andWhere('a.active = 1') ->orderBy('a.id', 'DESC') ->setParameter(1, $id) ->getQuery(); 

creates the following SQL:

 SELECT m0_.id AS id0, m0_.active AS active1, m0_.update_time AS update_time2, m0_.content AS content3, m0_.member_id AS member_id4 FROM mbr_article m0_ WHERE m0_.member_id = ? AND m0_.active = 1 ORDER BY m0_.id DESC