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.

Answers


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

Need Your Help

Typecasting asp.net webControl to generic type with onClick event

asp.net vb.net casting onclick

I'm trying to find a way in which I can create a collection of various webControl's and then add a "onClick" event handler to these various controls, i've tried creating a reimplementation of

Forms Authentication with sqlite

asp.net-mvc sqlite3 forms-authentication data-migration

I have an ASP MVC-application that uses a SQL server express for and Forms authentication. The server I'm using does not have enough memory to run SQL Server express stably and I was thinking about

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.