Syntax Error while executing complex query using ModelsManager

Hi all,

I am trying to execute an SQL query of moderate complexity using Model Manager, like I have done in the past. However, although the query works fine when executed using MySQL Workbench (or even phpMyAdmin), it fails while executing it in a controller function. This is the quey I need to execute:

    SELECT Test\Models\HelperMonths.month,
                      COALESCE(Test\Models\Orders.cnt,0),
                      COALESCE(Test\Models\Orders.total,0)
              FROM Test\Models\HelperMonths
              LEFT JOIN (
                SELECT COUNT(Test\Models\Orders.id) AS cnt,
                     SUM(Test\Models\Orders.order_total) AS total,
                     MONTH(Test\Models\Orders.date_created) AS m
                FROM Test\Models\Orders
                WHERE YEAR(Test\Models\Orders.date_created) = YEAR(NOW() - INTERVAL 1 YEAR)
                  AND Test\Models\Orders.order_status_id != 4
                GROUP BY MONTH(Test\Models\Orders.date_created)
              ) ON Test\Models\Orders.m = Test\Models\HelperMonths.month
              ORDER BY Test\Models\HelperMonths.month

and this is the error I am getting:

    Syntax error, unexpected token (, near to ' SELECT COUNT(Test\Models\Orders.id) AS cnt, SUM(Test\Models\Orders.order_total) AS total, MONTH(Test\Models\Orders.date_created) AS m FROM Test\Models\Orders WHERE YEAR(Test\Models\Orders.date_created) = YEAR(NOW() - INTERVAL 1 YEAR) AND Test\Models\Orders.order_status_id != 4 GROUP BY MONTH(Test\Models\Orders.date_created) ) ON Test\Models\Orders.m = Test\Models\HelperMonths.month ORDER BY Test\Models\HelperMonths.month', when parsing: SELECT Test\Models\HelperMonths.month, COALESCE(Test\Models\Orders.cnt,0), COALESCE(Test\Models\Orders.total,0) FROM Test\Models\HelperMonths LEFT JOIN ( SELECT COUNT(Test\Models\Orders.id) AS cnt, SUM(Test\Models\Orders.order_total) AS total, MONTH(Test\Models\Orders.date_created) AS m FROM Test\Models\Orders WHERE YEAR(Test\Models\Orders.date_created) = YEAR(NOW() - INTERVAL 1 YEAR) AND Test\Models\Orders.order_status_id != 4 GROUP BY MONTH(Test\Models\Orders.date_created) ) ON Test\Models\Orders.m = Test\Models\HelperMonths.month ORDER BY Test\Models\HelperMonths.month (886)

I cannot see any syntax errors (else the query would not work on MySQL). Can anyone help me figure out what am I missing?

Thanks a lot!

edited Jan '17

PHQL doesn't support left join from select statement. Just rewrite your query to have something like:

SELECT <COLUMNS>
FROM <MODEL NAME>
LEFT JOIN <MODEL NAME> ON <CONDITION>
WHERE <ALL CONDITIONS>
ORDER BY <ORDERS>

Also for interval i think you need mysqlextended from incubator.