Welcome Guest, Not a member yet? Register   Sign In
Something new for me with CI and MySQL (A problem...)
#1

[eluser]meridimus[/eluser]
I am writing my own forum using CI with a MySQL database backend.

I have a slight problem.

I'm trying to call all my topics with 1 database call, so I am writing a nice, concise sql query. My problem is that it's not working quite right.

It's very simple to explain, I have a topics table and a posts table. The topics table contains the post title, start date and author id. The posts table contains all the actual text etc along with post author, post date etc.

I want to pull the Topic information ordered by most recent posts order, also I'm joining the posts table to and grouping the posts by topic_id. Before grouping I'm ordering by post date. The problem is it always shows the first post instead of the most recent once it's been grouped...

Here's my SQL in CI format:
Code:
$this->db->select("topic.id, topic.title, topic.date, user.name as author, post.date as recent_date, recent_user.name as recent_author, (SELECT COUNT(id) FROM forum_post WHERE forum_topic_id = topic.id) as post_count");
$this->db->from("forum_topic as topic");
$this->db->join("users as user", "topic.user_id = user.id");
$this->db->join("forum_post as post", "topic.id = post.forum_topic_id");
$this->db->join("users as recent_user", "post.user_id = recent_user.id");
$this->db->order_by("post.date", "desc");
$this->db->group_by("post.forum_topic_id");

How would you suggest going about pulling the most recent post first? Is there a way to reverse the posts part?




Theme © iAndrew 2016 - Forum software by © MyBB