How to sort my doctrine result by number of associated entities and limit by timespan
I have two entities, Class and Student. One Class can have multiple Students, so it's a oneToMany relation. (Don't bother, that it should be a manyToMany relationship, I'm just drawing an example.)
# YAML notation for Entity 'Class' ... oneToMany: students: targetEntity: MyBundle\Entity\Student mappedBy: class # YAML notation for Entity 'Student' ... date: datetime # this is the date where Student joined a Class
For fetching all Classes, I'm writing my own query like this:
SELECT c FROM MyBundle:Class c WHERE c.whatever = :parameter ORDER BY c.id DESC
Now I'm trying to fetch a list of "rising" Classes. That means, that all Classes, that have been joined by Students within the last five days - ordered (DESC) by the number of joined Students.
So that the result would look like:
Class.id Class.count(Student) Latest Join -------- -------------------- ----------- 3 19 2013-12-07 1 12 2013-12-08 4 8 2013-12-07 2 5 2013-12-09
How would I go there? I tried something somewhat like this:
SELECT c, COUNT(c.students) AS students, MAX(s.date) FROM MyBundle:Class c WHERE c.whatever = :param AND DATE(s.date) > :fivedayspan ORDER BY students DESC
(Note: I implement DoctrineExtensions' Date function)
I am setting the parameter:
$q->setParameter('fivedayspan', date('Y-m-d', strtotime('-5 days')));
But I'm getting an error:
[Semantical Error] line 0, col 36 near 'students)': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.
Will this work?
SELECT c, COUNT(c.students) AS c.N, MAX(c.date) FROM MyBundle:Class c WHERE c.whatever = :param AND DATE(c.date) > :fivedayspan ORDER BY c.N DESC