How to make the active record many to many relationship easier to develop in codeigniter?

I have the table like this:

[Note]
[id]

[Note_Tag_list]
[Note_id]
[Tag_id]

[Tag]
[id]

Is there any active record to let me ONLY join these three tables? Because every time I based on the note id to loop back the note_tag_list record, and use the note tag list record, make me feel really, really annoying.

The flow I use:

//loop the note the user request
//get the note by id
//use th开发者_开发百科e note id to get the note tag list, and return the array of note tag list
//loop the array of note tag list and use the tag id to quest the tag

How do I do make this easier?

Maybe you could try datamapper :)

I know this is an old question, but anyways i will answer it for anyone who faces the same problem in future.

you need to use a JOIN query to connect multiple tables using primary and foreign keys relation.

Code to get all records

$q = $this->db
             ->select()
             ->from('Note AS n')
             ->join('Note_Tag_list AS n_t_l', 'n_t_l.Note_id = n.id', 'left')
             ->join('Tag AS t', 't.id = n_t_l.Tag_id', 'left')
             ->get();

Code to get only specific records (by using WHERE clause)

$where = array(
    'n.id' => **provide note id here**
);    

$q = $this->db
            ->select()
            ->from('Note AS n')
            ->join('Note_Tag_list AS n_t_l', 'n_t_l.Note_id = n.id', 'left')
            ->join('Tag AS t', 't.id = n_t_l.Tag_id', 'left')
            ->where($where)
            ->get();

For more information read the codeigniter's docs. Codeigniter Query Builder