How to convert this Query to Active Record?

[eluser]eldrinofsoalim[/eluser]
Hi guys,

I have this SQL query using a standard PHP query but I seem to have a problem with it (the latest inserted entries in the first table are not showing up). Everything is correct in the SQL but for some reason, it doesn't translate to PHP correctly.

My only option is to convert it into an Active Record query. Can you guys help how I'll do this?

Here's the the first query:

Code:

$this->db->query('
            SELECT post.id,
                post.post_title,
                post.post_url,
                post.post_date,
                post.post_desc,
                COUNT(post_id) AS NumberOfLikes
            FROM post
            LEFT JOIN like_post
            ON post.id=like_post.post_id
            GROUP BY post_id
            ORDER BY id');

Hope to hear from you dudes!

eldrinofsoalim

[eluser]toopay[/eluser]

Code:

$this->db->select('post.id,post.post_title,post.post_url,post.post_date,post.post_desc,COUNT(post_id) AS NumberOfLikes')
    ->join('like_post','post.id = like_post.post_id','left')
    ->group_by('post_id')
    ->order_by('id')
    ->get('post');

[eluser]eldrinofsoalim[/eluser]
Thank you toopay for the quick reply! It seems that it's the exact equivalent in Active Record query. Thanks.

HOWEVER, I'm still having problem with the query since it didn't fix my problem. For some reason, when I use the query (either my original or the one given by toopay), the returned query result is incomplete.

For example,

In my "post" table, there are 3 entries AND my "like_post" table is empty. When I use the query, it should show all 3 rows with 0 NumberOfLikes.

BUT it only shows only the first entry when I use the query.

Can someone explain why my query is wrong?

Thanks!

[eluser]toopay[/eluser]
try this.

Code:

$this->db->select('p.id,p.post_title,p.post_url,p.post_date,p.post_desc,IFNULL( l.post_id, 0) AS NumberOfLikes')
    ->from('post AS p')
    ->join('(SELECT post_id, COUNT( * ) AS NumberOfLikes FROM like_post GROUP BY post_id) AS l','p.id = l.post_id','left')
    ->order_by('p.id')
    ->get();

[eluser]toopay[/eluser]
You're going to have to get a count from the 'like_post' table before you join to the 'post' table, and then you can use the 'ifnull' function to make the count 0 where it may be null.

[eluser]eldrinofsoalim[/eluser]
By Hawking's Chair! You're totally correct toopay! You totally understood what I was trying to do even when I couldn't understand what I was trying to do. Haha.

I did what you did, after some tweaking. Basically, I had to COUNT the rows first in a new table and then join that table to the main table.

I'm very grateful. I'm starting out and it's help from forums like these that help me keep on going whenever I hit a wall.

Hope to get more advice from you soon and maybe I can message you directly? I need a mentor or a go-to troubleshoot advisor, haha.

[eluser]toopay[/eluser]
Send me message directly ONLY to ask my address, if you want to send me a bottle of beer!
Nice to hear it help. Come and drop by in CI forum, there are huge numbers of members here, to help you out!