I would like to know if possible to convert this to the codeigniter active record way using this->db->join etc.
What's the best. It works fine with the code I got but would like to make it work with codeigniter db active records.
http://www.codeigniter.com/userguide2/database/active_record.html
public function get_category($category_id) {
$language_id = $this->check_language();
$query = $this->db->query("SELECT DISTINCT *,
(SELECT GROUP_CONCAT(cd1.name ORDER BY level SEPARATOR ' > ')
FROM " . $this->db->dbprefix . "category_path cp LEFT JOIN " . $this->db->dbprefix . "
category_description cd1 ON (cp.path_id = cd1.category_id AND cp.category_id != cp.path_id)
WHERE cp.category_id = c.category_id
AND cd1.language_id = '" . (int)$language_id . "'
GROUP BY cp.category_id)
AS path
FROM " . $this->db->dbprefix . "category c
LEFT JOIN " . $this->db->dbprefix . "category_description cd2
ON (c.category_id = cd2.category_id)
WHERE c.category_id = '" . (int)$category_id . "'
AND cd2.language_id = '" . (int)$language_id . "'
");
return $query->row_array();
}