Help with query: Join, Limit and Count total rows?

[eluser]fesweb[/eluser]
No guarantees, but this should be close:

Code:

// bring back all results
$this->db->select('posts.*, users.username, tags.name');
$this->db->from('posts');
$this->db->join('tagmap', 'tagmap.tag_id = tags.id' );
$this->db->join('tags', 'tags.id = 47' );
$this->db->join('users', 'users.id = posts.userid');
$query = $this->db->get();

$total_rows = $query->num_rows();

$all_results = $query->result();

// then output only the first 20 rows...

Make sure you have the profiler on to see what the generated query looks like.

I usually find it much easier to paste that query into the sql box in phpmyadmin to adjust the query until it brings back what I'm expecting, then parse it back out into active record speak.