Doctrine - Query with condition on related entity

Question

I have this schema:

 Entity        Doctrine-generated        Entity
+++++++++       ++++++++++++++++        +++++++
+Student+ ----- +Student/Events+ ------ +Event+
+++++++++       ++++++++++++++++        +++++++

What this says is that a Student can have many events, and is represented with a ManyToMany relationship. The Student is the owning side of the relationship.

Each Event is added with an incremental id and associated to a Student. Of course, the mapping table is generated automatically by doctrine due to the ManyToMany.

I'm using event sourcing for this, so each Student has a status, that status is the Student's last event.

Now I would like to fetch all Students that have a given status, say, for instance, "IN_REVIEW". Remember that the status is represented by the Student's last event.

¿How can I do this with doctrine in just one trip to the database? More specifically, I would like to do this with a query builder.

PD: I have the following code:

  $qb->select('a')
  ->from("DnDRaHApiBundle:Student", "a")
  ->leftJoin("a.status", "s");

But can't figure out how to query on the mapping table. I've already thought on fetching all Events of a Student in reverse id order, then use that to query for a Student, but I don't like that approach, there has to be a better way.

So you have multiple events & event types for a student & you want to fetch all students that have a certain event type, where that event type is their most recent event? How does the Application entity fit into this? — Richard
@Richard I copy pasted my code without changing it, I'll just edit it now... But in that case, Application == Student — danielrvt

Fabrice Kabongo Fabrice Kabongo · Accepted Answer · 2015-05-20T07:37:16

With what i understand, i can propose you this:

$qb->select('Distinct a')
  ->from("DnDRaHApiBundle:Application", "a")
  ->leftJoin("a.status", "s")
  ->where('s.name = :name')//change name and put your real attribute
  ->orderBy('s.id','Desc')//the most recent have a the higher id
  ->setParameter('name','APPROVED');

Doctrine - Query with condition on related entity

1 Answers