Laravel group by не работает
But groupBy is returning \n
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by\n \n
Try with query builder \n
$logs = DB::table(‘callLog’)\n ->groupBy(‘caller’)\n ->get();\n \n
In MySQL 5.7, the sql mode ONLY_FULL_GROUP_BY is enabled by default, which means your select list must be in your aggregate functions (GROUP_BY, HAVING) assuming they are functionally dependent. See (seriously): https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/group-by-handling.html \n
This will work: \n
CallLogModel::selectRaw(‘count(*) AS cnt, caller’)->groupBy(‘caller’)->orderBy(‘cnt’, ‘DESC’)->limit(5)->get();\n \n
This will not: \n
CallLogModel::selectRaw(‘count(*) AS cnt, caller, callee’)->groupBy(‘caller’)->orderBy(‘cnt’, ‘DESC’)->limit(5)->get();\n \n
If caller isn’t functionally dependent on caller (and it wouldn’t be), you can do this: \n
CallLogModel::selectRaw(‘count(*) AS cnt, caller, ANY_VALUE(callee)’)->groupBy(‘caller’)->orderBy(‘cnt’, ‘DESC’)->limit(5)->get();\n \n
Or, again, disable ONLY_FULL_GROUP_BY. There is some other stuff out there if you want to read further about the pros\/cons of leaving ONLY_FULL_GROUP_BY enabled. For most cases, it’s probably fine to disable. Just keep it in the back of your mind as you write are verify your query results. \n»,»bodyInMarkdown»:»In MySQL 5.7, the sql mode ONLY_FULL_GROUP_BY is enabled by default, which means your select list must be in your aggregate functions (GROUP_BY, HAVING) assuming they are functionally dependent. See (seriously): https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/group-by-handling.html\r\n\r\nThis will work:\r\n«`\r\nCallLogModel::selectRaw(‘count(*) AS cnt, caller’)->groupBy(‘caller’)->orderBy(‘cnt’, ‘DESC’)->limit(5)->get();\r\n«`\r\nThis will not:\r\n«`\r\nCallLogModel::selectRaw(‘count(*) AS cnt, caller, callee’)->groupBy(‘caller’)->orderBy(‘cnt’, ‘DESC’)->limit(5)->get();\r\n«`\r\nIf caller isn’t functionally dependent on caller (and it wouldn’t be), you can do this:\r\n«`\r\nCallLogModel::selectRaw(‘count(*) AS cnt, caller, ANY_VALUE(callee)’)->groupBy(‘caller’)->orderBy(‘cnt’, ‘DESC’)->limit(5)->get();\r\n«`\r\nOr, again, disable ONLY_FULL_GROUP_BY. There is some other stuff out there if you want to read further about the pros\/cons of leaving ONLY_FULL_GROUP_BY enabled. For most cases, it’s probably fine to disable. Just keep it in the back of your mind as you write are verify your query results.»,»replies»:[],»user»:<"id":4696,"username":"lindstrom","avatar":"\/\/unavatar.now.sh\/github\/shawnlindstrom","experience":<"award_count":"45","level":14,"points":"66,235","pointsUntilNextLevel":"3,765">,»achievements»:[],»reported»:null,»profile»:<"github":"shawnlindstrom","twitter":"shawnlindstrom","full_name":null,"website":"https:\/\/tenerant.com","bio":null>,»dateSegments»:<"created_diff":"7 years ago">>,»likes»:[«pranshu»,»pavelpage»,»zeidanbm»,»santiagogg»,»hasnatbabur»],»created_at»:»2016-10-24T13:41:54.000000Z»,»links»:<"delete":"\/discuss\/replies\/289062","like":"\/discuss\/replies\/289062\/likes","best_answer":"\/discuss\/conversations\/47017\/best">,»best_answer»:true,»dateSegments»:<"createdDiff":"4 years ago">>,<"id":289064,"conversation_id":47017,"body":"
So here’s what I did: \n
$calls = Calls::select(‘call_id’, DB::raw(‘COUNT(call_id) as count’))\n ->with(‘called’)\n ->groupBy(‘call_id’)\n ->orderBy(‘count’, ‘desc’)\n ->get();\n \n
And this works, but it only obviously pulls in call_id and I can get data from called relationship, BUT I have another relationship called «info» and I need to pull info_id as well to get it with with(‘info’). So when I try to add that field to select it returns that same error from the original comment. Any thoughts @tomi ? \n
under [mysqld]. I just thought it would be worthwhile to explain what was going on in the event you couldn’t change your config. \n»,»bodyInMarkdown»:»@dlook Glad you sorted it out. Again, I’d just disable the default sql modes. Edit your my.cnf and add \r\n«`\r\nsql_mode=» \r\n«`\r\nunder [mysqld]. I just thought it would be worthwhile to explain what was going on in the event you couldn’t change your config.\r\n\r\n»,»replies»:[<"id":716107,"conversation_id":47017,"body":"
«The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION. \n
The ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES modes were added in MySQL 5.7.5. The NO_AUTO_CREATE_USER mode was added in MySQL 5.7.7. The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes were added in MySQL 5.7.8. For information about all available modes and default MySQL behavior, see Section 6.1.8, \u201cServer SQL Modes\u201d.» \n
At any rate, I’m still happy you got it working. \n»,»bodyInMarkdown»:»@dlook The only way that response from your manager flies is if he’s non-IT (in that case you’re screwed). Otherwise it’s a complete bullshit cop out. If he’s being that dismissive, he sure as hell better be able to either a) enumerate the reasons from either knowledge\/experience or research or b) pay someone to make these calls. He should know, for example, that if you migrated from 5.6 to 5.7, the only sql mode enabled by default was NO_ENGINE_SUBSTITUTION. Prior to 5.6.6, no modes were enabled. And from the fine manual for 5.7: \r\n\r\n\»The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.\r\n\r\nThe ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES modes were added in MySQL 5.7.5. The NO_AUTO_CREATE_USER mode was added in MySQL 5.7.7. The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes were added in MySQL 5.7.8. For information about all available modes and default MySQL behavior, see Section 6.1.8, \u201cServer SQL Modes\u201d.\»\r\n\r\nAt any rate, I’m still happy you got it working.»,»replies»:[],»user»:<"id":4696,"username":"lindstrom","avatar":"\/\/unavatar.now.sh\/github\/shawnlindstrom","experience":<"award_count":"45","level":14,"points":"66,235","pointsUntilNextLevel":"3,765">,»achievements»:[],»reported»:null,»profile»:<"github":"shawnlindstrom","twitter":"shawnlindstrom","full_name":null,"website":"https:\/\/tenerant.com","bio":null>,»dateSegments»:<"created_diff":"7 years ago">>,»likes»:[«dlook»,»zeidanbm»,»dgrzyb»],»created_at»:»2016-10-28T16:51:48.000000Z»,»links»:<"delete":"\/discuss\/replies\/290163","like":"\/discuss\/replies\/290163\/likes","best_answer":"\/discuss\/conversations\/47017\/best">,»best_answer»:false,»dateSegments»:<"createdDiff":"4 years ago">>,<"id":290171,"conversation_id":47017,"body":"
My manager always says «they’re there for a reason». \n \n
old issue i know, but this could help someone. 🙂 \n
i got the same error and i just moved the groupBy to work on the collection returned from the query and not the query itself. \n
if you work with large datasets i would go for a groupBy in the database, but for something small groupBy on the collection should be fine. \n
In MySQL 5.7, the sql mode ONLY_FULL_GROUP_BY is enabled by default, which means your select list must be in your aggregate functions (GROUP_BY, HAVING) assuming they are functionally dependent. See (seriously): https:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/group-by-handling.html \n
This will work: \n
CallLogModel::selectRaw(‘count(*) AS cnt, caller’)->groupBy(‘caller’)->orderBy(‘cnt’, ‘DESC’)->limit(5)->get();\n \n
This will not: \n
CallLogModel::selectRaw(‘count(*) AS cnt, caller, callee’)->groupBy(‘caller’)->orderBy(‘cnt’, ‘DESC’)->limit(5)->get();\n \n
If caller isn’t functionally dependent on caller (and it wouldn’t be), you can do this: \n
CallLogModel::selectRaw(‘count(*) AS cnt, caller, ANY_VALUE(callee)’)->groupBy(‘caller’)->orderBy(‘cnt’, ‘DESC’)->limit(5)->get();\n \n
Источник
Laravel pagination not working with group by clause
It seems Laravel pagination deos not working properly with group by clause. For example:
Produced
note that, there is no limit clause on the query.
Working fine if no group by clause in the query:
produced the following query:
does anyone has any idea how can i fix this?
5 Answers 5
Currently, pagination operations that use a groupBy statement cannot be executed efficiently by Laravel. If you need to use a groupBy with a paginated result set, it is recommended that you query the database and create a paginator manually.
I know it is an old question, by I am sharing my solution for future reference.
I managed to write a function based on this link which does the heavy job of determining the pagination of a complex query. Just pass the ‘QueryBuilder’ and it will return the paginated object/collection.
Additionally, this procedure can track and maintain the other parameters except for page= .
This works for me in laravel 5.2
create a database view named vw_anything . MySql query will be like
create view vw_anything as select subjects.*, count(user_subjects.id) as total_users from subjects inner join user_subjects on user_subjects.subject_id = subjects.id where subjects . deleted_at is null and user_subjects . deleted_at is null group by subjects . id ;
Now create a new model named UserSubModel for this view, protected $table = ‘vw_anything’;
Now your paginate query will be like UserSubModel::orderBy(‘subjects.updated_at’, ‘desc’)->paginate(25);
View query will be :
Let you model is VwModel then your paginate query will be
Источник
laravel 5.5 group by doesn’t work [duplicate]
I’m trying to get some data but I just get this error
SQLSTATE[42000]: Syntax error or access violation: 1055 ‘ms_mascotas.ms_razas.id’ isn’t in GROUP BY (SQL: select ms_razas.id , ms_razas.nombre , ms_mascotas.raza_id , ms_mascotas.id from ms_mascotas inner join ms_razas on ms_razas.id = ms_mascotas.raza_id where ms_razas.tipo_animal_id = 1 group by ms_mascotas.raza_id )»
my query is this
I’ve been reading about this error and it is related with the strict mode in the database file, strict is set to false by default, what should I do?
2 Answers 2
The problem isn’t caused by laravel but this is because MySQL doesn’t permit the illegal query, because in standard SQL-92, the nonaggregated name column in the select list does not appear in the GROUP BY. MySQL Handling of GROUP BY
Actually, you better solve the solution with changing the query, but if you still want to use the same query, just go to your config\database.php and change the setting the database that you use
Everything you select you must add in the group by, here is an example
You need to have a full group by:
SQL92 requires that all columns (except aggregates) in the select clause is part of the group by clause. SQL99 loosens this restriction a bit and states that all columns in the select clause must be functionally dependent of the group by clause. MySQL by default allows for partial group by and this may produce non-deterministic answers, example:
Источник
Using ‘groupBy’ method in Laravel
I often learn from my mistakes. And I prefer to learn by doing things. Today, I tried to group the rows of the result by a value of one of the columns. Then it threw an exception.
Illuminate\Database\QueryException SQLSTATE[42000]: Syntax error or access violation: 1055 ‘***.resources.id’ isn’t in GROUP BY (SQL: select * from `resources` where `lesson_number` ONLY_FULL_GROUP_BY mode) does not allow you to select columns which are not in the GROUP BY . Most answers to a question on this on StackOverflow suggest to disable strict mode or ONLY_FULL_GROUP_BY mode. I understood that I have done something wrong, but disabling MySQL strict mode is not the solution. I tried to enter the columns I need in the select query. Still, I got a similar error for another column. I turned off MySQL strict mode for some time to see the result and then I understood my mistake.
This restriction makes sense as when you use GROUP BY in MySQL, it returns one row for each value in the columns used in GROUP BY . So, the values of other columns in the selected rows do not make sense to use anywhere. So, it’s always recommended to use the best practice and I would recommend not to disable MySQL strict mode.
Often developers, like me, may need rows of a query grouped by the value of a column. Here they don’t need only one row per the unique values of the columns. But they need multiple rows grouped by the unique values of a particular column. For some reason, they use groupBy Query Builder method of Laravel which generates a MySQL GROUP BY query and the developers encounter the above error.
The solution to their problem is to use groupBy Collection method instead.
Did you see the difference? groupBy method before get method acts as a Query Builder method while after get method acts as a Collection method. This will give them the desired result.
Источник
Laravel 5 — Elequent GROUP BY не работает
Я пытаюсь сделать следующее:
У меня две таблицы:
В каждом Контенте должен быть раздел, который определяется внешним ключом, содержимое может иметь подраздел, где, если контент имеет тот же родительский_ид, — тогда это классифицируется как подраздел. Так, например:
Я использую Eloquent и использовал следующее:
Если я выведу их в цикле foreach, то он будет показывать только одну из записей, где есть несколько, имеющих один и тот же родительский элемент, если я удалю groupBy тогда он отобразит все записи, но не в группах
Я настроил отношения так, что: есть belongsTo отношение.. Так
Где я здесь не так?
Если я правильно понял, вы хотели бы получить список объектов Content вместе со своими объектами Content content, правильно?
Самый простой способ сделать это — создать соотношение родитель-ребенок в модели «Элементарный контент», а затем использовать его для загрузки родителей с детьми:
Затем, если вы хотите перечислить объекты содержимого своего Секции вместе со своими детьми и их разделами, вы можете получить такие данные:
$ contents будет содержать коллекцию всех объектов Content, у которых нет родителя. Каждый из объектов будет иметь атрибут $content-> children, который содержит коллекцию всех дочерних объектов Content. Все дочерние объекты также будут содержать ссылку на родителя в родительском элементе $childContent->. У обоих родителей и детей будет соответствующий атрибут раздела -> section.
Если вы хотите отобразить некоторую иерархию контента в шаблоне Blade, вы можете передать переменную $ contents в представление и сделать следующее:
Я заметил, что у вас есть поле последовательности в вашей модели. Я полагаю, что вы хотите, чтобы контент был отсортирован по этому полю. В этом случае вам потребуется изменить способ получения данных:
Источник
