How To Conver To Active Record's Inner Join?

Yii 1.1 General Discussion
1

hi, how to conver this statement into yii’s active record format?




SELECT a.USERID, a.ADID, a.PAGEVIEW,a.EXPIRYDATE,a.ADTITLE,a.ADDESC

        from CKAD a INNER JOIN 

        (

            SELECT  b.USERID, max(b.PAGEVIEW) as PAGEVIEW

            FROM CKAD b WHERE b.DISPPUBLISHDATE >= current_date - 3

            AND b.EXPIRYDATE >= current_date and b.STATUS = 1 AND b.USERID IS NOT NULL

            GROUP BY b.USERID

        ) b

        ON (a.USERID = b.USERID and a.PAGEVIEW = b.PAGEVIEW)

        order by a.PAGEVIEW DESC";
2

I would create a DB view and use activerecord to query the view instead.

3

aren’t there any way to convert this in yii active record than making a view ?

4

I don’t know if it’s possible at all, but it seems very complicated and tricky for AR to join the sub-query results at the very best.

I would join waitforit to create a DB view probably for the sub-query ("b" in your case).

5

never mind…i was able convert it to yii active record a few minutes after posting the first thread

6

You should probably share it then how you do it? Other people might be facing the same problem and it would be better if this post has an answer as well.