Symfony Doctrine Query to calculate rank after a group by subquery

Question

I'm building an app in Symfony 1.4 and Doctrine 1.2 ORM. I'm pretty new to the doctrine ORM and am getting the hang of it but I can't quite solve this problem.

I have a table of user scores (mbScoreByGenre) where one user id can have multiple records of user scores for one parent_genre. ie - many to many

My goal is to find the rank of a particular user based on his cumulative scores for a given parent_genre_id and user_id. My ranking algorithm uses a subquery and I've been having a lot of trouble building a doctrine query that works.

Here is my doctrine schema for mbScoreByGenre

mbScoreByGenre:
  actAs:
    Timestampable: ~    
  columns:

    id:                 { type: integer, primary: true, autoincrement: true }
    user_id:            { type: integer, notnull: true }
    genre_id:           { type: integer, notnull: true } 
    parent_genre_id:    { type: integer, notnull: true } 
    score:              { type: float, notnull: true, default: 0  } 

A. First I tried to do something like this:

$q = Doctrine_Query::create()
    ->select('((SELECT COUNT(1) AS num 
        FROM 
        (SELECT SUM(mbScoreByGenre.score) 
        WHERE SUM(mbScoreByGenre.score) > SUM(s.score)
        AND mbScoreByGenre.parent_genre_id = '.$genre['parent_id'].'
        AND s.parent_genre_id = '.$genre['parent_id'].'
        GROUP BY mbScoreByGenre.user_id
        ) + 1)  AS rank')
    ->from('mbScoreByGenre s')
    ->where('s.user_id = ?', array($user_id))
    ->groupBy('s.user_id')
    ->orderBy('rank');

but I got the following error Fatal error: Maximum function nesting level of '100' reached, aborting! in \lib\vendor\symfony-1.4.14\lib\plugins\sfDoctrinePlugin\lib\vendor\doctrine\Doctrine\Query\Tokenizer.php on line 303. I don't understand how to build the subquery so that it works.

B. So then I changed and tried a different approach

$q = new Doctrine_RawSql();
$q  ->addComponent('s', 'mbScoreByGenre')
    ->select('COUNT({*}) AS {rank}')
    ->from('(SELECT SUM(s.score) AS total_score
        FROM mb_score_by_genre s
        WHERE s.parent_genre_id = '.$genre['parent_id'].'
        GROUP BY s.user_id)
            ')
    ->where('total_score >= (
        SELECT SUM(s.score) 
        FROM mb_score_by_genre s
        WHERE s.parent_genre_id = '.$genre['parent_id'].'
        AND s.user_id = '.$user_id.'
        GROUP BY s.user_id
    )');

But I got this error: All selected fields in Sql query must be in format tableAlias.fieldName. The reason I used a Doctrine_RawSql is I read that doctrine 1.2 doesn't support subqueries in the From. For this approach I couldn't figure out how to reference the "total_score" column in the tableAlias.fieldName format. Do I have to add a blank component that refers the to subquery table returned for "total_score"?

C. Finally I tried just to run the subquery as a doctrine query and calculate the rank by counting the doctrine object rows returned by the query.

$q = Doctrine_Query::create()
    ->select('SUM(s.score)')
    ->from('mbScoreByGenre s')
    ->where('s.parent_genre_id = ?', $genre['parent_id'])
    ->andWhere('SUM(s.score) > (
        SELECT SUM(p.score) 
        FROM mbScoreByGenre p
        WHERE p.parent_genre_id = '.$genre['parent_id'].'
        AND p.user_id = '.$user_id.'
        GROUP BY p.user_id
    )')
    ->groupBy('s.user_id'); 

    $result = $q->execute();

But it gives me the error:

SQLSTATE[HY000]: General error: 1111 Invalid use of group function. Is it because groupBy('s.user_id') and GROUP BY p.user_id, both p and s refer to the same model?

I've done a ton of scouring the web for answers but I can't seem to find answers for any of the 3 approaches.

Any help would be great. Appreciate it.

Answer 1

Maybe, i did't fully understood what you really need, but did you try HAVING clause? WHERE clause does not support aggregate functions like SUM(). I tried this code and it worked and returned some values, but i can't say for sure if this is what you need:

$q = Doctrine_Query::create()
  ->select('count(*)')
  ->from('mbScoreByGenre s')
  ->where('s.parent_genre_id = ?', $genre['parent_id'])
  ->having("SUM(s.score) > (
    SELECT SUM(p.score) 
    FROM mbScoreByGenre p 
    WHERE p.parent_genre_id = {$genre['parent_id']}
      AND p.user_id = {$user_id})")
->groupBy('s.user_id');

$result = $q->execute(array(), Doctrine::HYDRATE_SCALAR);
var_dump($result);

If this is not what you need - try to explain more precise.