view_post method for a forum

[eluser]TheFuzzy0ne[/eluser]
I'd like to be able to find a posts position within it's parent topic by ID. I'd like to do this using the Active Record class, with no sub-selects id possible, and without adding an extra field to the database representing the position of that post.

So far, the best I could come up with, is simply to select the IDs of all of the posts in the topic, and iterate through those IDs to find the posts position. Does anyone else have any better ideas?

Many thanks.

The Fuzz.

[eluser]gtech[/eluser]
you could:

select count(*) from table where id <= $current_post_id and parentid = $parentid

somthing like that.

[eluser]TheFuzzy0ne[/eluser]
So it trims off all following posts? That's a great idea, I love it. Thanks!

I'd like to think I would have thought of that when I was coding it, but I'm not too sure.

[eluser]gtech[/eluser]
no probs... its one of those, 'ahhh of course' moments.

ahh forgot to add the 'and parentid = $parentid' bit.... modified post above.

[eluser]TheFuzzy0ne[/eluser]
Ah, nuts! I just realised I missed your point. It wasn't to trim the IDs from the end, it was to actually yield the position of the post. The solution works beautifully with the posts as a whole, but I can't make that work within a specific topic ID without a sub-select. Or at least I don't think it's possible. I can't wait for the day when MySQL is built with this in mind, and allows you to grab the position of a row within a result set.

[eluser]TheFuzzy0ne[/eluser]
Wow, I take that back. It really does work! No idea why I couldn't get it to work originally. I think I got sidetracked by pages such as this one - http://arjen-lentz.livejournal.com/55083.html

Many thanks again!

[eluser]gtech[/eluser]
coolarama

[eluser]gtech[/eluser]
I guess that site 'LOL'ed you into a false sense of sercurity!

[eluser]TheFuzzy0ne[/eluser]
Haha!

[eluser]TheFuzzy0ne[/eluser]
For anyone interested, here's my final solution:

Code:

class Post_model extends Model
{
    ...

    function get_position($post_id=0, $topic_id=0)
    {
        $post_id = (int)$post_id;
        $topic_id = (int)$topic_id;
        
        if ( ! $post_id || ! $topic_id)
        {
            return FALSE;
        }
        
        $this->db->select('COUNT(*) as count', FALSE);
        $this->db->where('topic_id', $topic_id);
        $this->db->where('id <=', $post_id);
        $this->db->order_by('id');
        $res = $this->db->get($this->table);
        $res = $res->row_array();

        return $res['count'];
    }

Thanks again, gtech.