Codeigniter; Active Records Class

Question

Hello my problems is that I only want to fetch the last topic for the 'last_...'. A simple order_by won't work seeing as it will sort the forums as well, which I don't want. The code I have so far. I am doing this in CodeIgniter's built in Active Records.

return $this->db->select('forums.*,')
        ->select('Count(topics.id) threads, Count(replies.id) replies')
        ->select('topics.url last_post_url, topics.name last_post_name, topics.created last_post_date')
        ->select('users.url user_url, users.name user_name, ranks.name user_rank')
        ->from('forums')
        ->join('topics', 'topics.f_id = forums.id', 'left')
        ->join('replies', 'replies.t_id = topics.id', 'left')
        ->join('users', 'users.id = topics.a_id', 'left')
        ->join('ranks', 'users.status = ranks.id','left')
        ->group_by('forums.id')
        ->get()
        ->result();

In case it isn't clear what I am trying to do; I want it to get the forums, in every row I want it to add the number or topics (working), the number of replies (working), and the who the last post was made by.

Raw query

SELECT `forums`.*, Count(topics.id) threads, Count(replies.id) replies, `users`.`url` user_url, `users`.`name` user_name, `ranks`.`name` user_rank
FROM (`forums`)
LEFT JOIN `topics` ON `topics`.`f_id` = `forums`.`id`
LEFT JOIN `replies` ON `replies`.`t_id` = `topics`.`id`
LEFT JOIN `users` ON `users`.`id` = `topics`.`a_id`
LEFT JOIN `ranks` ON `users`.`status` = `ranks`.`id`
GROUP BY `forums`.`id`

Sadly no, I tried to export the sql into it but I just got error after error with little to no error handling. http://pastebin.com/emA55BdY there you have a paste of it though if you can get it to work. My two problems are that last_post_url, last_post_name and last_post_date must be the latest topic or reply (dependable on which one is the later). — Emz, Jul 24 '12 at 12:32
I've tried to recreate your schema and add some test data to it, [see if it fits](http://sqlfiddle.com/#!2/06f86/1) and edit the question. Also, i think you are thinking about the about the "[The Rows Holding the Group-wise Maximum of a Certain Column](http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html)" problem. — complex857, Jul 24 '12 at 13:32
complex857, you got the same problem as I did, it fetches the first topic, and not the last one. In your case it fetches "topic1" that was created "2012-07-24 14:54:21" while there is topic3 that was created "2012-07-28 00:00:00". Also it should check for replies as well as topic. Topic1 was created at 14:30:30, Topic2 at 13:45:45 and Reply1 at 13:50:50, then it should fetch reply1. Thanks in advance. Also thanks for the subquery, I will try that out, not sure if codeigniter supports it though, will have to do it the "old-fashioned" way! — Emz, Jul 24 '12 at 17:40
Yes, its not a solution, its a replication of your code in a more experiment friendly environment to add to your question, so others might understand it better. But since i can't validate that I've correctly recreate your stuff, I didn't submit it as edit. — complex857, Jul 25 '12 at 05:37

score 0 · Answer 1 · answered Jul 24 '12 at 12:37

0

I think you need to use result_array() instead of result(). I might be wrong but that's how I'd do.

Also, what I'd recommend you to do is to create the SQL request with PHPMyAdmin or whatever you use so you can actually see what you get with the request, and then "translate" it to CodeIgniter syntax.

answered Jul 24 '12 at 12:37

CinetiK

1,748
2
13
19

result_array() should be better yes, but it doesn't change or do anything with my problem. SELECT `forums`.*, Count(topics.id) threads, Count(replies.id) replies, `users`.`url` user_url, `users`.`name` user_name, `ranks`.`name` user_rank FROM (`forums`) LEFT JOIN `topics` ON `topics`.`f_id` = `forums`.`id` LEFT JOIN `replies` ON `replies`.`t_id` = `topics`.`id` LEFT JOIN `users` ON `users`.`id` = `topics`.`a_id` LEFT JOIN `ranks` ON `users`.`status` = `ranks`.`id` GROUP BY `forums`.`id` – Emz Jul 24 '12 at 12:53

score 0 · Answer 2 · answered Jul 25 '12 at 04:22

0

Have you tried $row = $query->last_row() as on the User Guide: http://codeigniter.com/user_guide/database/results.html

answered Jul 25 '12 at 04:22

Ali Gajani

14,762
12
59
100

Codeigniter; Active Records Class

2 Answers2

Linked