I need to perform this query:
SELECT a, COUNT(a) FROM
(SELECT user_id, a FROM database GROUP BY user_id) as users
WHERE a IS NOT NULL
GROUP BY aCan't find any way to do this kind of subquery in Phalcon. Any help would be much appreciated.
Resorted to raw sql.
$sql = "
SELECT a, COUNT(a) FROM
(SELECT user_id, a FROM table GROUP BY user_id) as users
WHERE a IS NOT NULL
GROUP BY a
";
$table = new Table();
return new \Phalcon\Mvc\Model\Resultset\Simple(
null,
$table,
$table->getReadConnection()->query($sql)
);
Subqueries are not yet supported in QueryBuilder yet. Raw sql is the way to go.
Create a view then you'll be able to use it as a model
There are supported, just not in this form. They are supported like:
SELECT someColumn, (SELECT otherColumn FROM OtherModel) as otherColumn FROM users