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.

51 views and no responses? Is there something I can do to make the question more clear? Any help would be much appreciated. — frankp221, Apr 04 '12 at 00:07

score 2 · Accepted Answer · answered Apr 04 '12 at 11:57

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.

Thanks a lot starl1ng, the issue was that WHERE doesn't support aggregate functions. I also had to put the groupBy before the where clause. The resulting table gives me a list of all the records that are ahead of the current user_id. To get the ranking, I just count the rows and add 1. Thanks again. — frankp221, Apr 18 '12 at 18:35

philipphoffmann · Answer 2 · 2012-10-21T08:00:48.423

You should not nest subqueries into where conditions (or having in your case) using raw sql. Instead use createSubquery() to explicitly tell doctrine about the subquery. This will also help you in more complex scenarios where doctrine just cant handle deeply nested raw sql queries any more. So your query should look something like this:

$q = Doctrine_Query::create()
    ->select('count(*)')
    ->from('mbScoreByGenre s')
    ->where('s.parent_genre_id = ?', $genre['parent_id'])
    ->groupBy('s.user_id')
;

$subquery = $q->createSubquery()
     ->select("SUM(p.score)")
     ->from("FROM mbScoreByGenre p")
     ->where("p.parent_genre_id = ?", $genre['parent_id'])
     ->andWhere("p.user_id = ?", $user_id)
;

$q->having("SUM(s.score) > (".$subquery->getDql().")");

Another example can be found here:

http://www.philipphoffmann.de/2012/08/taming-doctrine-subqueries/

Symfony Doctrine Query to calculate rank after a group by subquery

2 Answers2