• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
problem with method "from" of db_active_record. Apparently add a ` when using one select query as argument.

#1
[eluser]Unknown[/eluser]
Code:
$this->db->select(array('user_id','MAX(date) as date'));
$this->db->from('routine');
$where = array (
   'grade is not null' => null,
   'datediff(curdate(), date ) >= ' => 10
);
$this->db->where($where);
$this->db->where_in('user_id', $ids);
$this->db->group_by('user_id');
$where_clause = '('.$this->db->get_compiled_select().') AS sq' ;

with a var_dump($where_clause), I get:

Code:
(SELECT `user_id`, MAX(date) as date FROM (`routine`) WHERE `grade` is not null AND datediff(curdate(), date ) >= 0 AND `user_id` IN (1, 2, 3) GROUP BY `user_id`) AS sq

which is good. But when I try to nest this query:

Code:
$this->db->select('avg(r.grade) as average');
$this->db->from($where_clause);
$t
$this->db->join('routine r','r.user_id = sq.user_id AND r.date = sq.date');

I get this:

Code:
SELECT avg(r.grade) as average FROM ((SELECT `user_id`, MAX(date) as date FROM (`routine`) WHERE `grade` is not null AND datediff(curdate(), `date` ) >= 0 AND `user_id` IN (1, `2`, `3)` GROUP BY `user_id`) as sq) JOIN `routine` r ON `r`.`user_id` = `sq`.`user_id` AND r.date = sq.date
Apparently, the "from" method put a ` after IN operator. I did something wrong?



Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.