如何在MySQL中使用用户定义的变量？

Question

I have a sql query adapted from Rank function in MySQL and I want to use it with Doctrine.
It seems that the system doesn't like my query and I don't know where to start to fix it.

This query works fine in MySQL itself, when I am using it with Doctrine I get a syntax error.

SELECT    l.*,
          @points:=l.playerPoints + l.organisationPoints,
          @curRank := if(@points <> @lastPoints, @curRank + 1, @curRank) AS rank,
          @lastPoints:= @points as points
FROM      leaderboard l, (SELECT @curRank := 0) r, (SELECT @lastPoints := 0) lp, (SELECT @points := 0) p
ORDER BY  l.playerPoints + l.organisationPoints DESC;

[Syntax Error] line 0, col 10: Error: Expected IdentificationVariable | ScalarExpression | AggregateExpression | FunctionDeclaration | PartialObjectExpression | "(" Subselect ")" | CaseExpression, got '@'

I am using following PHP code:

$EntityManager->createQuery(...);

Is it possible to handle this with Doctrine 2?
It seems to be possible with Doctrine 1 (MySQL User defined variable within Doctrine and Symfony).

Answer 1

EntityManager::createQuery is for DQL queries, I think Native SQL is what you need (allow you to execute an arbitrary query and map result to doctrine entities).