Log message #4217321

# At Username Text
# Dec 4th 2019, 15:12 val F.ex. if you take a set [1, 'apples'], [2, 'oranges'], [3, 'oranges'], [4, 'apples'] and then group by name you will get ['apples', 'oranges'] and it makes no sense to order by id because 'apples' have ids 1 and 4, 'oranges' have ids 2 and 3. The result of ordering by id can be 'apples', 'oranges' or 'oranges', 'apples' - both correct
# Dec 4th 2019, 15:09 val "but how do you explain it working then for mysql here?" - MySQL chooses any value according to the docs
# Dec 4th 2019, 15:08 slackebot2 free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.
# Dec 4th 2019, 15:08 val > MySQL extends the standard SQL use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is
# Dec 4th 2019, 15:07 info315 Is it possible to pass custom SQL to where() like so: ``` $query->andWhere([ 'IF(Services.name IS NULL, Servicetemplates.name, Services.name) LIKE' => 'asd' ]);``` I'm on CakePHP 4.x. I can't add the field to the field list, because I need to select count
# Dec 4th 2019, 15:04 neon1024 As least, that’s my understanding
# Dec 4th 2019, 15:04 neon1024 They then fixed it to be inline with other database engines, even MariaDB has it fixed too
# Dec 4th 2019, 15:03 neon1024 MySQL, the old versions, just allowed it even though it was invalid
# Dec 4th 2019, 14:59 dereuromark and then: why not just allowing this useful shim to actually be present/available for usability? apparently everyone seems to strive for an alternative that does with great costs maybe cover it eventually, maybe...^^
# Dec 4th 2019, 14:58 dereuromark "logically not correct" => but how do you explain it working then for mysql here? They must be shimming it inside then.
# Dec 4th 2019, 14:43 neon1024 No matter how often I read about it, I can’t seem to get the query correct
# Dec 4th 2019, 14:42 neon1024 I never got around this either and tend to turn it off also :man-shrugging:
# Dec 4th 2019, 14:36 val I meant "must be present in GROUP BY"
# Dec 4th 2019, 14:27 val @dereuromark when `GROUP BY` is used all non-aggregated columns must be present in `SELECT` . Explained in the docs - https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html. `SELECT summary FROM database_logs GROUP BY summary ORDER BY id;` is logically not correct as it is not possible to determine the order because the rows that have the same `summary` value have different `id` values.
# Dec 4th 2019, 14:05 ndm It shouldn't, as it's an aggregate function
# Dec 4th 2019, 14:02 dereuromark ndm: max() etc doesnt count into the isssue here? interesting
# Dec 4th 2019, 13:44 noel Is it possible to set layout based on route, rather than action?
# Dec 4th 2019, 13:43 ndm `->orderDesc('MAX(id)')`
# Dec 4th 2019, 13:32 graziel ie in my db its set to `STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION`
# Dec 4th 2019, 13:31 graziel when you do `sql_mode=''` you can change other things also - i dont know which never bothered to check
# Dec 4th 2019, 13:28 dereuromark Too bad this intuitive way of grouping is not allowed (by DB types in general) anymore
# Dec 4th 2019, 13:28 dereuromark @info315 Jep, thats my default usually. But not all users have that.
# Dec 4th 2019, 13:27 dereuromark haha, if you delete the code, the error also goes away. Does not solve it though keeping the functionality (which should be the aim)
# Dec 4th 2019, 13:27 graziel but when you do `->group(['summary', 'id'])` error goes away
# Dec 4th 2019, 13:26 graziel dont ask me im not mastermind behind this change
# Dec 4th 2019, 13:26 info315 @dereuromark you can set `sql_mode=''` :)
# Dec 4th 2019, 13:25 dereuromark wouldnt that kill the idea of grouping?
# Dec 4th 2019, 13:21 graziel what about adding `id` to `->group`
# Dec 4th 2019, 13:13 dereuromark Does anyone have a good idea how you can solve the (annoying) SQL problem of "nonaggreated columns" without losing the order? https://github.com/dereuromark/CakePHP-DatabaseLog/pull/36 I usually silence those as I am too lazy to fix it, but maybe there is a simple and good solution to this.
# Dec 4th 2019, 13:09 noel Probably because I'm using CRUD and I need to inject that into an event listener somewhere rather than just in the action.. lemme see...
# Dec 4th 2019, 13:08 graziel would need to see whole code at this point
# Dec 4th 2019, 13:07 noel hmm... no matter what I put in there it gives the same error; ```Confirm you have created the file: "Customer/Enquiries/index.ctp" in one of the following paths: /Users/noeldacosta/repo/mnr-be/src/Template/Customer/Enquiries/index.ctp```
# Dec 4th 2019, 13:06 graziel so try `setTemplatePath('`Enquiries')``
# Dec 4th 2019, 13:05 graziel then 'setTemplatePath' is relative to it
# Dec 4th 2019, 13:05 graziel iirc in app.php you have setting to templates in general
# Dec 4th 2019, 13:04 noel Cool.. what would go in the path though? I tried all the combinations I could think of. E.g. `'\App\Templates\Enquiries\index.ctp'`, `'\Templates\Enquiries\index.ctp'` , `'\App\Templates\Enquiries'` , , `'\Templates\Enquiries'` , `'\App\Templates\Enquiries\index'`, `'\Templates\Enquiries\index'` .... none of those works.
# Dec 4th 2019, 12:58 graziel maybe ```$this->viewBuilder() ->setTemplatePath('path');```
# Dec 4th 2019, 12:58 info315 You can pass a path like so: `$this->render('/Errors/error403');` maybe this helps
# Dec 4th 2019, 12:57 noel I tried using `$this->render()` but it seems always to default to the subfolder.
# Dec 4th 2019, 12:56 noel Let's say I have a controller in a subfolder: `\App\Controller\Customer\MyController.php` which is an extension of a controller in the main folder `\App\Controller\MyController.php`. Is there a way I can point the extension to the Template of it's parent controller, rather than having to create folders and duplicate Template files?
# Dec 4th 2019, 12:24 neon1024 Looks like some people posted solutions in the comments to get it without a namespace though