Convert the mysql subquery to join because the phalcon php framework query builder does not support the subquery


I am stacking to write a query for couple of days, appreciate if someone can advice me. I have written query using SUB QUERY but limitation is that I am using PHALCON PHP FRAMEWORK which query builder does not support sub query yet. So need some advice to get it convert with JOINS.


I need to fetch posts in order to latest entry in post_notification table. Well, I am building an application where I have to rearrange the posts list based on latest notification user received.


SELECT p.* , ( SELECT creation_date  FROM uo_notifications AS n
                            WHERE n.post_id =
                            AND n.user_id = ".$user_id ."
                            ORDER BY DESC
                            LIMIT 1
                          ) AS cdate
            FROM uo_posts AS p
            WHERE p.user_id = ".$user_id."
                AND p.group_id = ".$group_id."
            ORDER BY cdate DESC

Posts table:

  1. id PK
  2. message
  3. group_id
  4. creation_date


  1. id PK
  2. user_id
  3. notify_msg
  4. post_id
  5. creation_date

Thank you in advance for everyone who try to help me.

Instead of converting in join I have executed this query and get resultset with simple resultset type that work very well with Paginator class of Phalcon.

              // Base model
              $bm = new BaseModel();

              // Execute the query
             $rs = new Phalcon\Mvc\Model\Resultset\Simple(null, $bm, $bm->getReadConnection()->query($sql));