I'm building an app on Symfony 1.4/Doctrine 1.2 ORM and am having trouble with implementing a MYSQL query to be used as a doctrine object in my view
Here is the working MYSQL query that I need to hydrate into a doctrine object:
SELECT u.id, u.username, p.picture AS picture, p.fb_user_id AS fb_user_id, p.fb_access_token AS fb_access_token, hot_scores.hot_user_score, SUM(b.incremental_points) as total_points
FROM sf_guard_user u
LEFT JOIN sf_guard_user_profile p ON p.user_id = u.id
LEFT JOIN mb_stats_bosspoints b ON b.user_id = u.id AND b.date >= '2012-03-20' AND b.date <= '2012-03-30' AND b.parent_genre_id = 10
LEFT JOIN (SELECT SUM(g.score) AS hot_user_score, g.user_id AS user_id
FROM mb_score_by_genre g
WHERE g.parent_genre_id = 10
GROUP BY g.user_id
) hot_scores ON hot_scores.user_id = u.id
GROUP BY u.id
HAVING total_points >= 0
ORDER BY total_points DESC
LIMIT 10;
I couldn't figure out how to use DQL to create the query for this because of the complicated SELECT subquery in the final left join. So I decided to use doctrine raw sql. But I found out in another post [Here][1] that others had issues using aggregate values in doctrine raw sql queries. I decided to try it anyway.
Here is my doctrine raw sql query within a function that returns the query or executed query:
$q = new Doctrine_RawSql();
$q->select('{u.id}, {u.username}, {p.picture} AS {picture}, {p.fb_user_id} AS {fb_user_id}, {p.fb_access_token} AS {fb_access_token}, {hot_scores.hot_user_score}, {SUM(b.incremental_points)} AS {total_points}');
$q->from("sf_guard_user u
LEFT JOIN sf_guard_user_profile p ON p.user_id = u.id
LEFT JOIN mb_stats_bosspoints b ON b.user_id = u.id AND b.date >= '".$start_date."' AND b.date <= '".$end_date."' AND b.parent_genre_id = ".$parent_genre_id."
LEFT JOIN (SELECT SUM(g.score) AS hot_user_score, g.user_id AS user_id
FROM mb_score_by_genre g
WHERE g.parent_genre_id = ".$parent_genre_id."
GROUP BY g.user_id
) hot_scores ON hot_scores.user_id = u.id
GROUP BY u.id
HAVING total_points >= 0
ORDER BY total_points DESC
LIMIT ".$max);
$q->addComponent('u', 'sfGuardUser u');
$q->addComponent('p', 'sfGuardUserProfile p');
$q->addComponent('b', 'mbStatsBosspoints b');
$q->addComponent('g', 'mbScoreByGenre g');
if ($execute)
{
return $q->execute();
}
else // just return query for pager to setQuery
{
return $q;
}
However when I ran this I got the following error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'total_points' in 'order clause'. Failing Query: "
SELECT COUNT(*) as num_results FROM (SELECT DISTINCT u.id FROM sf_guard_user u
LEFT JOIN sf_guard_user_profile p ON p.user_id = u.id
LEFT JOIN mb_stats_bosspoints b ON b.user_id = u.id AND b.date >= '2012-04-12' AND b.date <= '2012-04-18' AND b.parent_genre_id = 10
LEFT JOIN (SELECT SUM(g.score) AS hot_user_score, g.user_id AS user_id
FROM mb_score_by_genre g
WHERE g.parent_genre_id = 10
GROUP BY g.user_id
) hot_scores ON hot_scores.user_id = u.id
GROUP BY u.id
HAVING total_points >= 0
ORDER BY total_points DESC
LIMIT 10) as results"`
Is there any way to fix this while still using doctrine raw sql query?
I tried the suggested alternative to execute raw mysql code using:
$doctrine = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
$q = $doctrine->query(".......");
if ($execute)
{
return $q->execute();
}
else // just return query for pager to setQuery
{
return $q;
}
But the I get the following error because I don't know how to use that method just to return a doctrine query object:
Fatal error: Call to undefined method PDOStatement::offset()
I don't know how to create just a query so that I can execute or return the query.
So the two questions are: 1. can I modify my doctrine raw sql call to use aggregate functions? 2. If not, how can I create a doctrine query that uses pure mysql and will still hydrate doctrine objects?
I'm lost and would love a prod in the right direction. Thanks a lot.
