• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
query builder HAVING clause

#1
Hello!
I'm trying to write SQL with query builder(QB). I want to use HAVING clause with MIN() function in it. In Mysql CLI everything works fine, but QB produce string with "IS NULL" in the end.

$this->db->HAVING('MIN(sp.s_price)', null, false);
$this->db->HAVING('MIN(sp.s_price)', '', false);
It's didn't help.
Code:
SELECT `s`.`s_code`, `sp`.`s_price`
FROM `services` `s`
LEFT JOIN `service_prices` `sp` ON `s`.`s_id` = `sp`.`s_id`
WHERE `s`.`s_name` LIKE '%q%' ESCAPE '!'
AND `s`.`s_activity` = '1'
GROUP BY `s`.`s_id`
HAVING MIN(sp.s_price) IS NULL

PHP Code:
$srvcCode 'q';
$this->db->select('s.s_code, sp.s_price');
$this->db->from('services s');
$this->db->join('service_prices sp''s.s_id = sp.s_id''left');
$this->db->like('s.s_name'$srvcCode);
$this->db->where('s.s_activity''1');
$this->db->group_by('s.s_id');
$this->db->having('MIN(sp.s_price)');

echo 
'<pre>';
print_r($this->db->get_compiled_select());
echo 
'</pre>';
die; 
 
I found workaround $this->db->having("MIN(sp.s_price)", "MIN(sp.s_price)", false);
But It's not a good practice.

Is it a bug or I didn't understand something?

English isn’t my first language, so please excuse any mistakes  Blush
Reply

#2
(11-10-2015, 04:20 PM)mIden Wrote: Hello!
I'm trying to write SQL with query builder(QB). I want to use HAVING clause with MIN() function in it. In Mysql CLI everything works fine, but QB produce string with "IS NULL" in the end.

$this->db->HAVING('MIN(sp.s_price)', null, false);
$this->db->HAVING('MIN(sp.s_price)', '', false);
It's didn't help.
Code:
SELECT `s`.`s_code`, `sp`.`s_price`
FROM `services` `s`
LEFT JOIN `service_prices` `sp` ON `s`.`s_id` = `sp`.`s_id`
WHERE `s`.`s_name` LIKE '%q%' ESCAPE '!'
AND `s`.`s_activity` = '1'
GROUP BY `s`.`s_id`
HAVING MIN(sp.s_price) IS NULL

PHP Code:
$srvcCode 'q';
$this->db->select('s.s_code, sp.s_price');
$this->db->from('services s');
$this->db->join('service_prices sp''s.s_id = sp.s_id''left');
$this->db->like('s.s_name'$srvcCode);
$this->db->where('s.s_activity''1');
$this->db->group_by('s.s_id');
$this->db->having('MIN(sp.s_price)');

echo 
'<pre>';
print_r($this->db->get_compiled_select());
echo 
'</pre>';
die; 
 
I found workaround $this->db->having("MIN(sp.s_price)", "MIN(sp.s_price)", false);
But It's not a good practice.

Is it a bug or I didn't understand something?

English isn’t my first language, so please excuse any mistakes  Blush

Your HAVING clause missed an operator. That's why Query Builder issues an IS NULL for it.
You can read about SQL HAVING here and QB having() here
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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