[eluser]Nameless One[/eluser]
Hi. I'm experienced with Zend Framework but switching to CodeIgniter at the moment. I have a strange problem using ActiveRecord class to fetch the data from database.
I have a controller that needs to fetch and format a helluva lot of data from the database and send it to a view. It instantiates several models that connect to the database in order to achieve this. The problem is that the last query that I run on the database with ActiveRecord class seems to remember the JOIN and WHERE parts from the first query that was executed by the controller.
The database class is autoloaded. The first query is called from the controller like this:
Code:
$items = $this->items->fetch_for_list($from, $until, $categories);
The function in the model looks like this:
Code:
class Items_model extends Model {
protected $table = 'items_definitions';
function Items_model() {
parent::Model();
}
function fetch_for_list($from = 0, $until = 0, $categories = array()) {
if ($from)
$this->db->where('items_instances.item_start >='.$from);
if ($until)
$this->db->where('items_instances.item_start <='.$until);
$this->db->join('items_instances','items_instances.item=item_id');
$this->db->join('items_ratings','items_ratings.item=item_id');
$this->db->join('items_categories','items_categories.item=item_id');
if (count($categories))
$this->db->where_in('items_categories.category',$categories);
else
return NULL;
$this->db->group_by('items_instances.item,items_instances.item_start,items_instances.location');
return $this->db->get($this->table);
}
}
This query works fine. The next two queries also work fine. The final query is called from the controller like this:
Code:
$categories_menu = $this->categories->fetch_for_menu();
It looks like this in the model:
Code:
class Categories_model extends Model {
protected $table = 'categories';
function Categories_model() {
parent::Model();
}
function fetch_for_items_list($categories) {
$this->db->where_in('category_id',$categories);
return $this->db->get($this->table);
}
function fetch_for_menu() {
$categories = $this->db->get($this->table);
$categories_array = array();
foreach ($categories->result() as $category)
if ($category->parent)
$categories_array[$category->parent][] = $category;
return $categories_array;
}
}
I see no apparent errors, but fetch_for_menu() function generates a database error with the following query:
SELECT * FROM (`categories`) JOIN `items_instances` ON `items_instances`.`item`=`item_id` JOIN `items_ratings` ON `items_ratings`.`item`=`item_id` JOIN `items_categories` ON `items_categories`.`item`=`item_id`, which is exactly the query from Items_model::fetch_for_list() function with a different FROM part.
What am I doing wrong? It seems quite strange that the middle two queries are working fine but the last one repeats the JOIN and WHERE from the first one. Do I need to reload the database class before each query or something like that? I tried to put $this->db->flush_cache() at the beginning of Categories_model::fetch_for_menu() but it didn't help.
