Welcome Guest, Not a member yet? Register   Sign In
database error when using DISTINCT and ORDER BY
#1

hello everyone 

i am new to code igniter and got stuck in one query i want to get distinct id from database order it by id

my code is 

PHP Code:
$this->db->select('message_user_id, message_user_name');
$this->db->distinct("message_user_id");
$this->db->order_by("message_id");
$this->db->get('cms_inbox'); 


i get this error when i run it  i am using wamp with php 7 and mysql 5.7

Code:
Error Number: 3065
[color=#4f5155][size=small]Expression #1 of ORDER BY clause is not in SELECT list, references column 'test.cms_inbox.message_id' which is not in SELECT list; this is incompatible with DISTINCT[/size][/color]
[color=#4f5155][size=small]SELECT DISTINCT `message_user_id`, `message_user_name` FROM `cms_inbox` ORDER BY `message_id`[/size][/color]
[color=#4f5155][size=small]Filename: G:/wamp/www/system/database/DB_driver.php[/size][/color]
Line Number: 691


how can i fix it please help me
Reply
#2

Try this:

Code:
$this->db->select('message_user_id, message_user_name, message_id');

Reply
#3

This select thing is working if i dont use order by but it shows error when order by is used with distinct or group by this error only shows when mysql is 5.7 they changed some stuff in it and i think codeigniter $this->distinct is not fixed for this type query in latest mysql version
Reply
#4

ok i need to repeat my error in details

i want to get only one user_id from my inbox db by using group by or distinct  but both not working

here is my sql code for group by

Code:
SELECT message_user_name FROM cms_inbox GROUP BY message_user_id ORDER BY message_time DESC

and error i get from it in only latest version on mysql 5.7 is

Code:
Error Number: 1055

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.cms_inbox.message_user_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

demo on w3schools website HERE

it works on that website but dont know why my code is showing error
Reply
#5

(This post was last modified: 05-05-2017, 10:32 PM by corey.)

my problem solved by using

Code:
SELECT ANY_VALUE(message_user_name), ANY_VALUE(message_user_id) FROM cms_inbox GROUP BY message_user_id ORDER BY ANY_VALUE(message_time)

adding ANY_VALUE()  to SELECT and ORDER BY solved my problem but causing problem in view file 

this is showing when print_r uses 

Code:
[color=#333333][size=medium][font=Ubuntu, sans-serif]Array ( [0] => Array ( [ANY_VALUE(message_user_name)] => user_1 [ANY_VALUE(message_user_id)] => 2 ) [1] => Array ( [ANY_VALUE(message_user_name)] => user_2 [ANY_VALUE(message_user_id)] => ) )

and when i use foreach is view file 

Code:
foreach ( $inbox as $var) { echo $var->message_user_name; }


it shows error 

Code:
[color=#333333][size=medium][font=Ubuntu, sans-serif]Message: Trying to get property of non-object[/font][/size][/color]
Reply
#6

Does this not work?

Code:
$this->db->select('distinct message_user_id, message_user_name, message_id');

Reply
#7

(This post was last modified: 05-09-2017, 08:37 PM by corey.)

guyz seems like you dont understand problem is with mysql version 5.7 needs change in code or change mysql 5.7 mode
by default in 5.7 one mode is set to ONLY_FULL_GROUP_BY either disable it or go back to old 5.6 mysql
i fixed this problem by disabling only full group by

now here is what is happening and how it can be fixed

Code:
SELECT message_user_name FROM cms_inbox GROUP BY message_user_id ORDER BY message_time DESC

this code shows error in mysql 5.7

Code:
Error Number: 1055

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.cms_inbox.message_user_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by


how to fix it simply go back to mysql 5.6 or disable ONLY_FULL_GROUP_BY mode

seems like no one use group by regularly in codeigniter thats why no fixed querying in codeigniter which cause error in latest version of mysql 5.7 while works with every db before it

thank you guys my problem solved by disable ONLY_FULL_GROUP_BY but hoping for future proof codeigniter team fixes this query by making it compatible with mysql 5.7 or giving lil info how to setup query to use it with newer mysql

here is more info about this mode mysql 5.7
Reply
#8

Try this:

Code:
SELECT ANY_VALUE(message_user_name) message_user_name, ANY_VALUE(message_user_id) AS message_user_id
FROM cms_inbox GROUP BY message_user_id ORDER BY ANY_VALUE(message_time)
A good decision is based on knowledge and not on numbers. - Plato

Reply




Theme © iAndrew 2016 - Forum software by © MyBB