Codeigniter DB Active Record Join

[riwakawd]

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();
    }

[cereal]

Hi,

I haven't tested this but try to set the subquery in the select() method:

$this->db->distinct();

# select
$this->db->select("*, (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");

# join
$this->db->join($this->db->dbprefix . "category_description AS cd2", "c.category_id = cd2.category_id", "left");

# where
$this->db->where("c.category_id", (int)$category_id);
$this->db->where("cd2.language_id", (int)$language_id);

$query = $this->db->get($this->db->dbprefix . "category c");

With the $this->db->get_compiled_select() method you can see the query generated by active query.