+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: query problem (/showthread.php?tid=73978)
query problem - midav - 07-02-2019
here is my model
PHP Code:
function get($where = [],$like = [],$order = '',$group_by = [],$type = 'array'){
$this->db->select('*');
$this->db->from($this->table);
if(!empty($where))
$this->db->where($where);
if(!empty($like))
$this->db->like($like);
if(!empty($order))
$this->db->order_by($order);
if(!empty($group_by))
$this->db->group_by($group_by);
$this->db->join('fb_cities', 'fb_cities.id_ci = fb_usr.id_ci', 'left');
$this->db->join('fb_classes', 'fb_classes.id_cl = fb_usr.id_cl', 'left');
$this->db->join('fb_person', 'fb_person.id_pe = fb_usr.id_pe', 'left');
$this->db->join('fb_posts', 'fb_posts.id_po = fb_usr.id_po', 'left');
$this->db->join('fb_schools', 'fb_schools.id_sc = fb_usr.id_sc', 'left');
$this->db->join('fb_key', 'fb_key.id_fb_us = fb_usr.id_fb_us', 'left');
$query = $this->db->get();
if($type == 'array')
return $query->result_array();
else
return $query->row();
}
here is my challenge in the controller
PHP Code:
$this->M_usr->get(['fb_usr.id_sc' => $this->input->post('school_id'),'fb_usr.id_cl !=' => 0,'fb_usr.id_sc !=' => ''], [], 'fb_classes.name_cl ASC', ['fb_usr.id_cl']);
here's the error
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'admin_codeignite.fb_usr.id_fb_us' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
SELECT `fb_usr`.`id_fb_us` as `id_fb_us`, `fb_usr`.`fname` as `fname`, `fb_usr`.`lname` as `lname`, `fb_usr`.`sname` as `sname`, `fb_usr`.`fio` as `fio`, `fb_usr`.`email` as `email`, `fb_usr`.`phone` as `phone` FROM `fb_usr` LEFT JOIN `fb_cities` ON `fb_cities`.`id_ci` = `fb_usr`.`id_ci` LEFT JOIN `fb_classes` ON `fb_classes`.`id_cl` = `fb_usr`.`id_cl` LEFT JOIN `fb_person` ON `fb_person`.`id_pe` = `fb_usr`.`id_pe` LEFT JOIN `fb_posts` ON `fb_posts`.`id_po` = `fb_usr`.`id_po` LEFT JOIN `fb_schools` ON `fb_schools`.`id_sc` = `fb_usr`.`id_sc` LEFT JOIN `fb_key` ON `fb_key`.`id_fb_us` = `fb_usr`.`id_fb_us` WHERE `fb_usr`.`id_sc` = '18' AND `fb_usr`.`id_cl` != 0 AND `fb_usr`.`id_sc` != '' GROUP BY `fb_usr`.`id_cl` ORDER BY `fb_classes`.`name_cl` ASC
RE: query problem - hc-innov - 07-02-2019
Hi,
see the mysql documentation: https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html
RE: query problem - midav - 07-02-2019
maybe someone can give a mini example of how to recycle my request
RE: query problem - php_rocs - 07-02-2019
@midav,
Do you have a version of the query that works directly in MySQL?
Have you tried the query directly in MySQL?
You are going to have to break down each join to see where the issue is. Did you check the field in the table as the error suggested?
RE: query problem - midav - 07-02-2019
(07-02-2019, 08:01 AM)php_rocs Wrote: @midav,
Do you have a version of the query that works directly in MySQL?
Have you tried the query directly in MySQL?
You are going to have to break down each join to see where the issue is. Did you check the field in the table as the error suggested?
on my MySql 5.7 if it is disabled ONLY_FULL_GROUP_BY
RE: query problem - php_rocs - 07-02-2019
@ midav,
Do you have access to phpMyAdmin or some other type of MySQL tool?
RE: query problem - midav - 07-04-2019
(07-02-2019, 10:04 AM)php_rocs Wrote: @ midav,
Do you have access to phpMyAdmin or some other type of MySQL tool?
I have access, the problem is that I do everything on my hosting and the parameter only_full_group_by is disabled there and it is enabled on the customer's server, I googled a lot but didn’t find an example of how to do it on the SQL92 specification on SQL99. A lot of text and there is no example of standing.
RE: query problem - midav - 07-04-2019
here is another example of my SQL92 specification request
PHP Code:
$this->db->select('fb_usr.id_ci AS id_ci, fb_usr.id_sc AS id_sc, fb_usr.id_cl AS id_cl, COUNT(fb_usr.id_us) AS count');
$this->db->where(['fb_usr.id_ci !=' => '', 'fb_usr.id_sc !=' => '', 'fb_usr.id_cl !=' => 0]);
$this->db->from('fb_usr');
$this->db->group_by(["fb_usr.id_sc", "fb_usr.id_cl" ]); // Производит: GROUP BY title, date
$query = $this->db->get();
$rez = $query->result_array();
with only_full_group_by disabled, everything works fine, but when only it turns on all the error is the same again.
Can someone show how to fix my query on the SQL 99 specification.
RE: query problem - neuron - 07-04-2019
[quote pid='365734' dateline='1562061900']
here's the error
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'admin_codeignite.fb_usr.id_fb_us' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
[/quote]
as error says you need to disable only_full_group_by
https://stackoverflow.com/questions/23921117/disable-only-full-group-by
I faced the same error, fixed it by disabling only_full_group_by mode.
Disabling should not break your existing queries, in my case I did not face any problems.
RE: query problem - hc-innov - 07-04-2019
(07-04-2019, 05:52 AM), midav Wrote: here is another example of my SQL92 specification request
PHP Code:$this->db->select('fb_usr.id_ci AS id_ci, fb_usr.id_sc AS id_sc, fb_usr.id_cl AS id_cl, COUNT(fb_usr.id_us) AS count');
$this->db->where(['fb_usr.id_ci !=' => '', 'fb_usr.id_sc !=' => '', 'fb_usr.id_cl !=' => 0]);
$this->db->from('fb_usr');
$this->db->group_by(["fb_usr.id_sc", "fb_usr.id_cl" ]); // Производит: GROUP BY title, date
$query = $this->db->get();
$rez = $query->result_array();
with only_full_group_by disabled, everything works fine, but when only it turns on all the error is the same again.
Can someone show how to fix my query on the SQL 99 specification.
@midav,
you can disable the mode only_full_group_by in you customer server.
in your CI model :
$this->db->query("SET session sql_mode=''");
OR
if you don't use the primary key in your group by clause, you can use any_value in your query:
select any_value(fb_usr.id_ci), any_value(your_column), etc....
if you use a primary_key in your group by clause, there is no problem.