Complex SQL Query on Doctrine

Question

I'm trying to implement a complex SQL Query using Doctrine 2. However, my SQL Query has subselects. I think this' the problem, but I can't realize a way to solve this.

SELECT 
    x.year, x.month, y.total 
FROM
(SELECT 
     YEAR(a.date) year, MONTH(a.date) month, count(*) number
 FROM
     Acessos a
 GROUP BY
     YEAR(a.date), MONTH(a.date)) x 

INNER JOIN

(SELECT
 MAX(Z.number) total
 FROM
 (SELECT
      COUNT(a.id) number
  FROM
      Acessos a
  GROUP BY
      YEAR(a.date), MONTH(a.date)) z) y ON x.number = y.total LIMIT 1

I'm getting a QueryException when I put this query to run trhu code.

<b>Fatal error</b>:  Uncaught exception 'Doctrine\ORM\Query\QueryException' 
with message 'SELECT x.messelecionado, x.anoselecionado, y.total from (select
month(a.data) messelecionado, year(a.data) anoselecionado, count(*) numero from 
Acesso a group by month(a.data), year(a.data)) x inner join (select max(z.numero)
total from (select count(a.id) numero from Acesso a group by month(a.data),
year(a.data)) z) y on x.numero = y.total limit 1' in     
C:\xampp\htdocs\patclin\app\Doctrine\ORM\Query\QueryException.php:39
Stack trace:
#0 C:\xampp\htdocs\patclin\app\Doctrine\ORM\Query\Parser.php(429):   
Doctrine\ORM\Query\QueryException::dqlError('SELECT x.messel...')
#1 C:\xampp\htdocs\patclin\app\Doctrine\ORM\Query\Parser.php(854):     
Doctrine\ORM\Query\Parser-&gt;semanticalError('Class '(' is no...', Array)
#2 C:\xampp\htdocs\patclin\app\Doctrine\ORM\Query\Parser.php(1529): 
Doctrine\ORM\Query\Parser-&gt;AbstractSchemaName()
#3 C:\xampp\htdocs\patclin\app\Doctrine\ORM\Query\Parser.php(1426): 
Doctrine\ORM\Query\Parser-&gt;RangeVariableDeclaration()
#4 C:\x in <b>C:\xampp\htdocs\patclin\app\Doctrine\ORM\Query\QueryException.php</b> 
on line <b>49</b><br />

Anyone knows what can I do?

Thanks!!!

how did you manage to succeeded this query with a select in your inner join ? i m having the exact problem with this question :http://stackoverflow.com/questions/22815483/convert-native-sql-query-in-symfony2-query-builder-with-select-in-join — Charles-Antoine Fournel, Apr 03 '14 at 09:24

score 3 · Accepted Answer · edited May 23 '17 at 12:17

YEAR function that you have written with group by clause is not understood by doctrine. u have to register custom dql function for that . here is the list of dql functions that doctrine provides: http://docs.doctrine-project.org/en/latest/reference/dql-doctrine-query-language.html#dql-functions

here is the link to create your own dql function http://docs.doctrine-project.org/en/latest/reference/dql-doctrine-query-language.html#adding-your-own-functions-to-the-dql-language

Also groupby works with aggregate functions only (AVG, COUNT, MIN, MAX, SUM)

This link might help you Group by using month, year in doctrine2

Complex SQL Query on Doctrine

1 Answers1