Subqueries, Subqueries...Wow


May '15

Jun '15

3

1146

2


Thien
8.6k

HI Phalcon fan

One of the feature I'm expecting this is Subueries and now available in Phalcon 2.0.2. Now, you can take advantage of subqueries as shown below:

$phql = "SELECT c.* FROM Shop\Cars c
WHERE c.brandId IN (SELECT id FROM Shop\Brands)
ORDER BY c.name";
$cars = $this->modelsManager->executeQuery($phql);

Models must belong to the same database in order to be used as source in a subquery. Also you can check all information feature here

Cheer :)

I was happy when i read that subqueries are now supported. So i updated to 2.0.2 - but im my case they still not working... :-(

$sql = 'SELECT * 
            FROM ( 
                    SELECT * 
                    FROM ZipGeodb 
                    Group by zc_location_name 
                    Order By zc_location_name) as alleStaedte 
            WHERE alleStaedte.zc_id NOT IN(' . $used . ')';
$orte = $this->modelsManager->executeQuery($sql);

Phalcon throw the following error:

Phalcon\Mvc\Model\Exception: Syntax error, unexpected token (, near to ' SELECT * FROM ZipGeodb Group by zc_location_name Order By zc_location_name) as alleStaedte WHERE alleStaedte.zc_id NOT IN('8390','8860','301','342','404','455','731','822','2261','3267','3720','3826','4656','5622','6304','6341','7047','8022','9651','10179','10585','11321','11924','11960','13707')', 
when parsing: SELECT * FROM ( SELECT * FROM ZipGeodb Group by zc_location_name Order By zc_location_name) as alleStaedte WHERE alleStaedte.zc_id NOT IN('8390','8860','301','342','404','455','731','822','2261','3267','3720','3826','4656','5622','6304','6341','7047','8022','9651','10179','10585','11321','11924','11960','13707') (317)

The SQL-statement is correct.

So, is such a nested query not yet supported or am i doing something wrong?

Does anybody has a solution to solve my problem ?


Thien
13.1k

Could you upload issue for Phalcon on Github

edited Jun '15
Jun '15

Having same issue with query:

"SELECT p.*, (SELECT n.creationdate FROM Uns\Models\Notifications AS n WHERE n.postid = p.id AND n.userid = ".$userid ." ORDER BY n.creationdate DESC LIMIT 1 ) AS cdate FROM Uns\Models\Posts AS p WHERE p.userid = ".$userid." AND p.groupid = ".$group_id." ORDER BY cdate DESC";

              Fatal error</b>:  Uncaught exception 'Phalcon\Mvc\Model\Exception' with message 'Syntax error, unexpected token SELECT, near to ' n.creation_date  FROM Uns\Models\Notifications AS n
                              WHERE n.post_id = p.id
                              AND n.user_id = 3
                              ORDER BY n.creation_date DESC
                              LIMIT 1
                            ) AS cdate
              FROM Uns\Models\Posts AS p
              WHERE p.user_id = 3
                  AND p.group_id = 2
              ORDER BY cdate DESC', when parsing: SELECT p.*, (SELECT n.creation_date  FROM Uns\Models\Notifications AS n
                              WHERE n.post_id = p.id
                              AND n.user_id = 3
                              ORDER BY n.creation_date DESC
                              LIMIT 1
                            ) AS cdate
              FROM Uns\Models\Posts AS p
              WHERE p.user_id = 3

              Query works well in phpmyadmin.