| # |
Dec 4th 2019, 16:59 |
 gianmarxgagliardi |
wroooooooooooooooooooooooooo |
| # |
Dec 4th 2019, 16:59 |
ndm |
And when you use distinct/group to remove possible duplicates, you might come back to join @dereuromark’s "group by handling" trauma support group :) |
| # |
Dec 4th 2019, 16:54 |
ndm |
:tongue: |
| # |
Dec 4th 2019, 16:54 |
neon1024 |
Beaten to it! ;) |
| # |
Dec 4th 2019, 16:54 |
neon1024 |
I’d use the `notMatching()`.. |
| # |
Dec 4th 2019, 16:54 |
ndm |
`$this->DogsCats->Dogs->find('list')->notMatching('DogsCats')` or `$this->DogsCats->Dogs->find('list')->notMatching('Cats')` https://book.cakephp.org/3/en/orm/retrieving-data-and-resultsets.html#using-notmatching |
| # |
Dec 4th 2019, 16:39 |
gianmarxgagliardi |
a question: with this method `$this->set('Dogs', $this->DogsCats->Dogs->find('list'));` I take the list of dogs, but if I wanted to take all the dogs except those that were NOT associated with cats in the dogs_cats table, how could I do? here you find the DB ->https://stackoverflow.com/questions/59141547/cakephp-displays-the-id-display-table-name-from-two-different-tables-with-relati |
| # |
Dec 4th 2019, 16:27 |
dereuromark |
thats a cool trick @ndm |
| # |
Dec 4th 2019, 16:26 |
ndm |
@dereuromark If what you want is a list of distinct summaries ordered by their latest occurrence, then ordering by `MAX(id)` will give you exactly that. |
| # |
Dec 4th 2019, 16:16 |
k4t |
is it known problem? |
| # |
Dec 4th 2019, 16:16 |
k4t |
Record not found in table "requests" |
| # |
Dec 4th 2019, 16:16 |
k4t |
I have problem with debuggit which throws follwoign error: |
| # |
Dec 4th 2019, 16:16 |
k4t |
Hello |
| # |
Dec 4th 2019, 16:12 |
dereuromark |
true. it is still somewhat fuzzy. But most cases it seemed to work this easy way. Well, maybe just dropping the order then - and done with it. thx |
| # |
Dec 4th 2019, 15:24 |
val |
some queries may return unexpected results when only_full_group_by is disabled, f.ex. `SELECT email, DATE(created) FROM users GROUP BY DATE(created);` - it is not really clear what `email` value is expected - the first user created at the given date? the last user? Often MySQL will pick one user email on Linux but a different email on Windows even when the data in users table is the same. |
| # |
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=''` :) |