CODEWITHSUNDEEP
phpdoctrine-orm
CSchulz
CSchulz

Reputation: 11030

How to use user defined variables with MySQL?

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).

Upvotes: 3

Views: 583

Answers (1)

MathB.Fr
MathB.Fr

Reputation: 292

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).

Upvotes: 1

Related Questions