phpmysqlcodeigniterjoinactiverecord

Query with subqueries in the SELECT clause incorrectly converted to CodeIgniter active record

When I use the $this->db->where() & $this->db->join() it is not retrieving any data. When var dump says NULL.

On my old code it works fine, using JOIN in query. Cannot work out what I am missing.

OLD Code Works Fine

public function get_admin_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, (SELECT DISTINCT keyword FROM " . $this->db->dbprefix . "url_alias WHERE query = 'category_id=" . (int)$category_id . "') AS keyword 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();
}

New Code What I Have Tried.

public function get_admin_category($category_id) {
    $language_id = $this->check_language();
    $this->db->select('GROUP_CONCAT(cd1.name ORDER BY level SEPARATOR "  >  ")');
    $this->db->distinct();
    $this->db->from($this->db->dbprefix . 'category_path cp', 'LEFT');
    $this->db->join($this->db->dbprefix . 'category_description cd1', 'cp.path_id = cd1.category_id AND cp.category_id != cp.path_id', 'LEFT');
    $this->db->where('cp.category_id', 'c.category_id');
    $this->db->where('cd1.language_id', $language_id);
    $query = $this->db->get();
    return $query->row_array();
}

How can I convert my old $this->db->query to codeigniter work with the join. http://www.codeigniter.com/userguide2/database/active_record.html

Solution

  • Try this it'll work for you.

    $this->db->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, (SELECT DISTINCT keyword FROM " . $this->db->dbprefix . "url_alias WHERE query = 'category_id=" . (int)$category_id . "') AS keyword", false);
$this->db->from($this->db->dbprefix . "category c");
$this->db->join($this->db->dbprefix . "category_description cd2", "c.category_id = cd2.category_id", "left");
$this->db->where("c.category_id", intval($category_id));
$this->db->where("cd2.language_id", intval($language_id));
$result = $this->db->get()->row_array();
return $result;