Many-to-Many Relationships & Active Record Class

[eluser]Chris Newton[/eluser]
I'm trying to build the results of a many-to-many relationship between 3 tables using the active record class. I maintain a news table containing all of the news item, a category table containing all of the category names & ids, and a join table, containing all of the news_ids & their respective category_ids.

I know how I could do this with standard PHP, but I'm trying to wrap my brain around the best way / best syntax for the active record class. I'm trying to build this many-to-many relationship without a lot of looping and I haven't come up with a working solution yet.

Table 1: ci_news
id,subject,content,date

Table 2: ci_news_to_categories
news_id
category_id

Table 3: ci_news_categories
id,name

Each news item may have multiple categories which is why I need the join table, rather than just adding category_id to the news table.

Does anyone have any working examples of a model that references this type of relationship?

[eluser]gtech[/eluser]
are you trying to select the data? in which case you can do a join

Code:

//untested buy you get the idea
$this->db->select('*');
$this->db->from('ci_news_to_categories');
$this->db->join('ci_news','ci_news_to_categories.category_id = ci_news.id');
$this->db->where('subject','I love cheese');
$res = $this->db->get();

and then you can loop through the results to find all the category ids. Or you could do then join in a different way.

or are you asking how to insert the data?

[eluser]Chris Newton[/eluser]
Sorry... I was wondering how to select the data. Thanks for your suggestion, I'll look at your code here in a bit. I think I might have already tried it... it's not a bad solution, just thought there might be something I hadn't thought of.

[eluser]Chris Newton[/eluser]
This is how I ended up solving it; (view & controller code is super basic for testing purposes)

controller:

Code:

function index()
    {
        $this->load->model('news', '', TRUE);
        $data['news']=$this->news->readNews();        
        $this->load->view('welcome_message',$data);
        
    }

model:

Code:

function readNews(){
        $this->db->select('*');
        $this->db->from('news');
        $this->db->orderby('id','desc');
        $news_info=$this->db->get();
        $my_news=array();
        foreach ($news_info->result() AS $key =>$value){
                        $my_news[$key]['news']=$value;
                        $this->db->select('name, id');
                        $this->db->from('news_to_categories');
                        $this->db->join('news_categories', 'news_to_categories.category_id=news_categories.id');
                        $this->db->where('news_id',$value->id);
                        $query2=$this->db->get();
                        $my_news[$key]['categories']=array();
                        foreach($query2->result() AS $cats){
                            $my_news[$key]['categories'][]=$cats;
                        }
        }
        return $my_news;
    }

Doesn't seem ideal, but It works. I keep trying to come at it from different angles, and nothing better comes to mind. I suppose I could (maybe should?) create 2 functions in the model, one to read the news items, and the other to read the categories, and then join them in the controller. Thoughts?
view:

Code:

foreach ($news AS $key=>$item){
    foreach ($item['categories'] AS $key2=>$cat){
         echo $cat->name;
         echo $cat->id;
         echo br(1);
    }
    echo br(2);
    echo "subject ".$item['news']->subject;
    echo br(2);
    echo "date ".$item['news']->date;
    echo br(2);
}

[eluser]nmweb[/eluser]
Try to find a better solution, if you have 100 news results it will result in a 100 queries for one page alone. Maybe this ORM library can help you out: http://codeigniter.com/wiki/ActiveRecord_Class/

[eluser]tonanbarbarian[/eluser]
I would recommend you just use a single query that joins all of the tables, and then processing the data and splitting it into the structure that you want.
Because for each news item you are doing a seperate query to find the categories, so if you have 200 news items you are doing 201 queries, rather than just 1 if you use a join and process it.

[eluser]Chris Newton[/eluser]
tonanbarbarian;
Right, but how would you write the sql query for that?

Ex;

Table A has 2 rows (news items)
Table B has 25 rows (category names)
Crossover table X has 17 rows, 13 of which relate to table A, 4 of which relate to table B

So.... how do you write a join that does the following; Respects the fact that there are only 2 items I'm interested in from table A (the news items), while adding an array of their corresponding categories stored in table X with names referenced in table B?

Every join that I could come up with ended up displaying multiple copies of the items from table A each with 1 category assigned to them. (rather than only 2 rows)

I could join everything, and then loop through it to pull out only the category information from multiple instances of the same data but that doesn't sound much better. Anyway. I'm gonna go try something... brb.

[eluser]Chris Newton[/eluser]
yep. nope. I tried to join everything and either a; ended up with corrupt data (categories being added where they shouldn't be) or b; didn't pull any data if no categories are assigned to an item.

Any ideas?

A:

Code:

SELECT bn_news.id,bn_news.subject,bn_news_categories.id, bn_news_categories.name
FROM bn_news
CROSS JOIN bn_news_categories
LEFT JOIN bn_news_to_categories ON (bn_news_to_categories.news_id = bn_news.id AND bn_news_to_categories.category_id = bn_news_categories.id)

B:

Code:

SELECT bn_news.id,bn_news.subject,bn_news_categories.id, bn_news_categories.name
FROM bn_news
CROSS JOIN bn_news_categories
LEFT JOIN bn_news_to_categories ON (bn_news_to_categories.news_id = bn_news.id AND bn_news_to_categories.category_id = bn_news_categories.id)
WHERE bn_news_to_categories.news_id=bn_news.id

[eluser]gdgray[/eluser]
The following will join your categories with your news item via the intervening linking table:

SELECT news.*, newscat.*
FROM ci_news news
INNER JOIN ci_news_to_categories news2cat ON news.id = news2cat.news_id
INNER JOIN news_categories newscat ON newscat.id = news2cat.category_id;

Not exactly sure how to apply to the ActiveRecord methods.

greg

[eluser]Chris Newton[/eluser]
Thanks gdgray, but that still leaves me with problem B; If no category has been assigned to a news item, it doesn't show. Without doing 2 queries, I don't see how I can get the info I need. Basically, I have to first know what the news items are, then I have to find their associated categories. I think I need to get the MySQL advanced manual out ;-)