Welcome Guest, Not a member yet? Register   Sign In
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




Theme © iAndrew 2016 - Forum software by © MyBB