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.
QUERY I WRITTEN:
SELECT p.* , ( SELECT creation_date FROM uo_notifications AS n WHERE n.post_id = p.id AND n.user_id = ".$user_id ." ORDER BY n.id 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
- id PK
- id PK
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));