CODEWITHSUNDEEP
joinsubqueryphalcon
Rakesh Shekhawat
Rakesh Shekhawat

Reputation: 358

Convert mysql subquery to join because phalcon php framework query builder does not support 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.

MORE DETAILS:

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

Posts table:

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

post_notifications

  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.

Upvotes: 0

Views: 1446

Answers (2)

Rakesh Shekhawat
Rakesh Shekhawat

Reputation: 358

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));

Upvotes: 1

user2877596
user2877596

Reputation: 31

Now. Phalcon 2.x have support subquery, you can take look here http://phalcontip.com/discussion/44/subqueries-subquerieswow

Upvotes: 0

Related Questions