[eluser]Unknown[/eluser]
I have an issue with group concat using active record. Here is my query:
Code:
function get_covers_by_category($slug) {
$this->db->select('cover.id AS cID, cover.title AS cTitle, cover.slug AS cSlug, cover.image AS cImage, cover.timestamp AS cTimestamp, category.name AS catName, category.slug AS catSlug, GROUP_CONCAT( tag.tag SEPARATOR "," ) AS tags')
->from('cover')
->join('category', 'cover.category_id = category.id')
->join('cover_tag', 'cover_tag.cover_id = cover.id', 'left outer')
->join('tag', 'cover_tag.tag_id = tag.id', 'left outer')
->group_by('cover.title')
->order_by('cover.timestamp', 'ASC')
->where('category.slug', $slug);
$q = $this->db->get();
return $q->result_array();
}
And here is the error:
Code:
Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '` ) AS tags FROM (`cover`) JOIN `category` ON `cover`.`category_id` = `category`' at line 1
SELECT `cover`.`id` AS cID, `cover`.`title` AS cTitle, `cover`.`slug` AS cSlug, `cover`.`image` AS cImage, `cover`.`timestamp` AS cTimestamp, `category`.`name` AS catName, `category`.`slug` AS catSlug, GROUP_CONCAT( tag.tag SEPARATOR ", `"` ) AS tags FROM (`cover`) JOIN `category` ON `cover`.`category_id` = `category`.`id` LEFT OUTER JOIN `cover_tag` ON `cover_tag`.`cover_id` = `cover`.`id` LEFT OUTER JOIN `tag` ON `cover_tag`.`tag_id` = `tag`.`id` WHERE `category`.`slug` = 'cars' GROUP BY `cover`.`title` ORDER BY `cover`.`timestamp` ASC
Filename: /Applications/MAMP/htdocs/codeigniter/models/cover.php
Line Number: 19
The issue is with the delimiter/separator on group concat. Not sure how to fix it. The query works fine when I run it through MySQL.