Welcome Guest, Not a member yet? Register   Sign In
query problem
#1

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)
   
Reply
#2

Hi,

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

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

@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?
Reply
#5

(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
Reply
#6

@ midav,

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

(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.
Reply
#8

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)
   
Reply
#9

(This post was last modified: 07-04-2019, 08:21 AM by neuron.)

[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.
Reply
#10

(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.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB