query problem

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

Attached Files

Thumbnail(s)
   

Hi,

see the mysql documentation: https://dev.mysql.com/doc/refman/8.0/en/...dling.html

maybe someone can give a mini example of how to recycle my request

@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?

@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

@ midav,

Do you have access to phpMyAdmin or some other type of MySQL tool?

@ 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.

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.

Attached Files

Thumbnail(s)
   

[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/2392...l-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.

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.