Getting results form a query to insert in other using active records
I'm needeing to get all the child categories from the given to use in a where_in with active records of codeigniter.
The problem is that the second query get mixed with the main one breaking it completely.
Main Query
$this->db->select('artworks.*, users.id as owner, users.name as user_name');
$this->db->from('artworks');
$this->db->join('users', 'users.id = artworks.user_id');
$category = $this->get_child_categories($this->get_categories(), $matches[1]);
$this->db->where_in('artworks.category', $this->category['child']);
$this->db->group_by('artworks.id');
$query = $this->db->get();
return $query->result_array();
Second Query "get_categories()"
$this->db->select('*');
$this->db->order_by('parent', 'asc');
$this->db->order_by('name', 'asc');
$query = $this->db->get('categories');
return $query->result_array();
get_child_categories
function get_child_categories($categories, $parent){
foreach($categories as $category){
if($category['parent'] == $parent){
array_push($this->category['childs'], $category['id']);
$this->get_child_categories($categories, $category['id']);
}
}
}
But i'm getting this error where clearly displays that the second query is quetting inside the main one.
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 '* FROM (`artworks`, `categories`) JOIN `users` ON `users`.`id` = `artworks`.`use' at line 1
SELECT `artworks`.*, `users`.`id` as user_id, `users`.`name` as user_name, * FROM (`artworks`, `categories`) JOIN `users` ON `users`.`id` = `artworks`.`user_id` WHERE `artworks`.`rating` I开发者_运维百科N ('g', 'm', 'a') ORDER BY `artworks`.`id` desc, `parent` asc, `name` asc
Filename: D:\Server\htdocs\gallery\system\database\DB_driver.php
Line Number: 330
I personally think this is an error in CodeIgniter's Active Record approach, if it's supposed to follow the Active Record pattern at all. It should totally enforce either one of these:
- queries contained in a single data context
- queries specified in a atomic instruction
As neither of these is happening, at the moment you're unwillingly mixing two queries with a structure that CodeIgniter does not support, thus creating that invalid query.
For a simple solution, I would suggest for you to invert the order of the instructions so that the queries are executed separately.
$category = $this->get_child_categories($this->get_categories(), $matches[1]);
# the first query gets executed here, your data context is cleaned up
$this->db->select('artworks.*, users.id as owner, users.name as user_name');
$this->db->from('artworks');
$this->db->join('users', 'users.id = artworks.user_id');
$this->db->where_in('artworks.category', $this->category['child']);
$this->db->group_by('artworks.id');
$query = $this->db->get();
# your second query gets executed here
return $query->result_array();
精彩评论