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

[eluser]little brittle[/eluser]
I am having trouble coming up with a query to retrieve blog posts based on abstracted tags. Here is what I want to do:

- Return posts.* tagged with "motorcycles", LIMIT 20
- Return users.username for each post by joining 'users.id' with 'posts.userid'
- Return number of total possible unique posts that match "motorcycles", either with CI query caching, or with SQL_CALC_FOUND_ROWS.

Here is some sample data from my tables. Could someone help guide me in creating a query to pull the right results?

Code:

TABLE "posts"

id  title             content          time         userid
--  --------          -----------      ----         ------
12  my blog title 1   bla bla bla...   1218826136   5
13  my blog title 2   bla bla bla...   1218826136   28
14  my blog title 3   bla bla bla...   1218826136   3


TABLE "tags"

id  name
--  -----
45  cars  
46  trucks  
47  motorcycles


TABLE "tagmap"

id   tag_id   post_id
--   ------   -------
156  46       12
157  46       14
158  45       12
159  47       13

TABLE "users"

id   username
--   --------
3  Frank
4  Jimbo5000
5  wayne

[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.

[eluser]fesweb[/eluser]
one way to limit the output

Code:

$i = 0;
foreach($all_results as $row)
{
$i++;
echo $row->title.' - by '.$row->username.'<br />';
if($i == 20) {
    break;
}
}

[eluser]little brittle[/eluser]
I keep getting this error no matter what I try:
#1054 - Unknown column 'posts.userid' in 'on clause'

If I take out the JOIN, it returns results without the user.username field. What can I do to fix this?

[eluser]Armchair Samurai[/eluser]
This will retrieve the first 20 posts with motorcycle tag as well as give the total number of 'motorcycle' posts contained in the database.

Code:

$data = new stdClass;

$this->db->start_cache();
    $this->db->from('posts p');
    $this->db->join('users u', 'p.userid = u.id');
    $this->db->join('tagmap tm', 'p.id = tm.post_id');
    $this->db->join('tags t', 'tm.tag_id = t.id');
    $this->db->where('t.name', 'motorcycles');
$this->db->stop_cache();

$data->count = $this->db->count_all_results();

$this->db->select('p.*, u.username');
$this->db->limit(20);

$query = $this->db->get();
$data->posts = $query->result();

$this->db->flush_cache();
return $data;

[eluser]little brittle[/eluser]
That was exactly what I was looking for. Thanks a lot for the help.