Codeigniter & PostgreSQL: Display posts by entry date or latest comment date, depending on which is newer

I'm using CI and have a PostgeSQL set up with two tables: Posts and Comments, which both have 2 columns: Id and Date. Comments also has a column Parentid to match it with the Post it is related to.

Now I'd like to display the Posts ordered by latest activity, i.e. show them according to the Post Date or if there is a Comment related to the Post, by the latest Comment Date.

The following set-up...

Posts:

Id | Date
=========
1  | 04
2  | 07
3  | 08

Comments:

Id | Parentid | Date
====================
1  | 1        | 04
2  | 2        | 07
3  | 1        | 09

... should return the Posts in order 1-3-2: 1 has the latest activity (comment), then 3 (post), then 2 (comment).

Is there any way I can achieve this with CodeIgniter开发者_开发百科's active record or do I need to build a custom query or even further, sort them by hand?

Active Record has order by. You can use it like so...

$this->db->order_by('date', 'asc');

Check it out here...

http://codeigniter.com/user_guide/database/active_record.html

As for the latest activity via comments or posts, your gonna have to create a new column alias that takes the latest value of either comments or posts.