[eluser]elverion[/eluser]
I'm working on a query that will take search results and provide all tags associated with each result. In example, a user can search for the tag 'cool' and all articles tagged as 'cool' will be displayed as well as all the other tags associated with each article returned by the search. I'm sorry that came out so wordy; I hope you understood what I meant.
I'm using GROUP_CONCAT() to return the tags associated with each item. The problem is, having it in the select statement causes only one result to ever be displayed. As soon as I remove GROUP_CONCAT (and nothing else), all results are shown again. And this isn't just a display glitch; only one result is returned. Unless I'm mistaken, this isn't the intended behavior of GROUP_CONCAT().
I've got three tables: items, item_tag (item_id, tag_id), and tags (id, name).
'items' obviously refers to the actual items that the user is searching for. 'tags' is obviously just a table containing all of the tags, and item_tag binds the other two tables together. This all works just fine.
My query looks something like this:
Code:
$this->db->select('items.*, accounts.username AS username, accounts.id AS userid, GROUP_CONCAT(tags.name ORDER BY tags.name ASC) AS tagss', FALSE);
$this->db->from('items');
$this->db->limit($count);
$this->db->order_by('added', 'desc');
$this->db->join('accounts', 'items.owner = accounts.id');
$this->db->join('tags', 'tags.id = items.id', 'left');
$query = $this->db->get();
return $query->result();
Alternatively, I can take the normal results and run a separate query to grab the tags for each result, but as this could result in an additional 50+ queries per page...I figure it is probably not a very good option.
So, am I doing something wrong, is this a bug, or intended behavior?
