Welcome Guest, Not a member yet? Register   Sign In
Many-to-Many Relationships & Active Record Class
#11

[eluser]gdgray[/eluser]
This should take care of missing links ...

SELECT news.*, newscat.*
FROM ci_news news
LEFT OUTER JOIN ci_news_to_categories news2cat ON news.id = news2cat.news_id
LEFT OUTER JOIN ci_news_categories newscat ON newscat.id = news2cat.category_id

greg
#12

[eluser]Chris Newton[/eluser]
Yeah, that works. Now I just need to work on taking the results and dumping the duplicate information. As you'll see in the attached png, that join does return the data as many times as there are categories assigned to it.

Thanks for the help guys. I'll post more on my progress if I come up with anything additional.
#13

[eluser]Chris Newton[/eluser]
OK. After much MySQL query string testing, this query will do what I need with a few HUGE gotchas.

Code:
SELECT news_table.id AS id, news_table.subject AS subject, news_table.content AS content, news_table.date AS date,
GROUP_CONCAT(news_table.cat_name ORDER BY news_table.cat_name SEPARATOR ',') as catlist
FROM
(SELECT news.id AS id, newscat.name AS cat_name, newscat.id AS cat_id, news.subject AS subject, news.content AS content, news.date AS date
FROM bn_news news
LEFT OUTER JOIN bn_news_to_categories news2cat ON news.id = news2cat.news_id
LEFT OUTER JOIN bn_news_categories newscat ON newscat.id = news2cat.category_id)as news_table
GROUP BY news_table.id
ORDER BY news_table.id

So, what's going on here is that I'm creating a table called news_table in a subquery, and then pulling the data I need from that subquery, and using GROUP_CONCAT to aggregate the many-to-many category items into a text string. This leaves me with the data seen in the attached PNG.

So, with 1 query, I get all of my data, but the categories are now a string, devoid of their accompanying category IDs. I could ALSO pull out the category_IDs into a string list if I chose. So, if I decide to display the category names in the view and make them clickable I have to explode them into an array, and then I'd have to explode the cat_id string into an array too, and hope that they match up. Who knows, maybe there's some mysql command to pull together multiple items into an array or something, but I don't know what it is.

So. Yes, everything in a 3-way many-to-many relationship used for connecting multiple categories to items can be done with 1 query, but I don't (currently) know of a straigthforward method to make those categories very useful. If / when I come up with something else on this, I'll post.
#14

[eluser]vokic[/eluser]
Well i do it like this, simply by using the active record class, and i must separate two different situations:

1. You are pulling out categories just for representation (to put them on display to the user):
Code:
// Categories
$this->db->select('GROUP_CONCAT(DISTINCT ci_news_categories.name SEPARATOR 'whatever') AS categories');
$this->db->join('ci_news_to_categories', 'ci_news_to_categories.news_id = ci_news.id', 'LEFT');
$this->db->join('ci_news_categories', 'ci_news_categories.id = ci_news_to_categories.category_id', 'LEFT');
// News
$this->db->select('ci_news.*');
$this->db->orderby('ci_news.date', 'desc');
$this->db->groupby('ci_news.id');
$news = $this->db->get('ci_news');
$news = $news->result_array();

2. You also need data from categories (these categories migh be clickable and you want their id's and rest of the data)... So we select our news items just like in the previous case and categories must go through the loop...
Code:
// News
$this->db->select('ci_news.*');
$this->db->orderby('ci_news.date', 'desc');
$news = $this->db->get('ci_news');
$news = $news->result_array();
//Categories
$this->db->select('ci_news_to_categories.news_id, ci_news_categories.*');
$this->db->join('ci_news_to_categories', 'ci_news_to_categories.category_id = ci_news_categories.id');
$this->db->orderby('ci_news_categories.name');
$query = $this->db->get('ci_news_categories');
foreach ($query->result() as $row) {
  $categories[$row->news_id][] = array(
    'id' => $row->id,
    'name' => $row->name
  );
}
// News loop to assign categories
foreach ($news as $key => $item)
  $news[$key]['categories'] = isset($categories[$item['id']]) ? $categories[$item['id']] : array(); // or just ''

There you go... I think.. Please don't mind the spelling errors and if this solution is not exactly correct (syntax etc.) because I'm writing it at 5am (local time) and porting it from another, similar, situation... Also note that group_concat was added from mySQL 4.1... Hope this helps...




Theme © iAndrew 2016 - Forum software by © MyBB