| # |
Dec 17th 2019, 15:05 |
 jotpe |
Yeah, that's what I'm trying now ;) |
| # |
Dec 17th 2019, 15:05 |
luke |
I sometimes find with more complex queries, writing it in sql first, and getting it to work, then pushing it into query builder |
| # |
Dec 17th 2019, 15:03 |
luke |
or change to an outer join |
| # |
Dec 17th 2019, 15:02 |
luke |
try select distinct maybe? |
| # |
Dec 17th 2019, 15:02 |
jotpe |
Yes, and if I remove the group by I have duplicate membersips. I want them just once. |
| # |
Dec 17th 2019, 15:01 |
gianmarxgagliardi |
sorry someone could help me with my problem |
| # |
Dec 17th 2019, 15:00 |
luke |
if there are more than one, the response object will have all associated records attached which match the conditions |
| # |
Dec 17th 2019, 15:00 |
luke |
I dont think group by is what you are looking for then |
| # |
Dec 17th 2019, 14:57 |
jotpe |
so no aggergation |
| # |
Dec 17th 2019, 14:56 |
jotpe |
• have a student flag • are active (have no Terminations or Termination.effective is in future) • have not a valid Matriculation (no Matriculation at all or a Matriculation.revocation_date in past) |
| # |
Dec 17th 2019, 14:51 |
jotpe |
In fact all Memberships that: |
| # |
Dec 17th 2019, 14:51 |
jotpe |
I need the group by to select only the Memberships. |
| # |
Dec 17th 2019, 14:50 |
jotpe |
@luke https://gist.github.com/julianpollmann/c128d4f03211b7f423d4cae2c9f4cee1 |
| # |
Dec 17th 2019, 14:46 |
luke |
you are returning too many columns in your select. But I am not sure which you are aggregating, so do you need to even group at all? |
| # |
Dec 17th 2019, 14:46 |
luke |
it should be something like this: ```mysql> SELECT a, SUM(b) FROM mytable WHERE a = 'abc'; +------+--------+ | a | SUM(b) | +------+--------+ | abc | 3000 | +------+--------+``` you can have a group by column (a) and an aggregate column (sum(b)) |
| # |
Dec 17th 2019, 14:44 |
jotpe |
mom |
| # |
Dec 17th 2019, 14:44 |
luke |
@jotpe list the columns you expect to get back, and the aggregated column |
| # |
Dec 17th 2019, 14:43 |
jotpe |
I left out the Users => Names to test, the query for the Matriculations doesn't work standalone |
| # |
Dec 17th 2019, 14:43 |
neon1024 |
Or do I need to implement the TimezoneAwareDateTime data type again? |
| # |
Dec 17th 2019, 14:43 |
scuadra |
Hello. In Cake3 I am trying to make a method where the user can update his password. I made a new validation set and there are 3 fields in the form: `old_password`, `password` and `confirm_password`. For password and new_password I use sameAs as a validation rule but my question is how to compare the old_password user input with his current password? |
| # |
Dec 17th 2019, 14:42 |
neon1024 |
Apologies, but I’ve confused myself. https://book.cakephp.org/3/en/core-libraries/internationalization-and-localization.html#parsing-localized-dates This refers to accepting request data right? So when the instance is created in PHP, when it’s marshalled, the instance will be in the users timezone right? ..and when it hits the ORM it’ll be converted to `defaultTimezone` |
| # |
Dec 17th 2019, 14:42 |
challgren |
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html |
| # |
Dec 17th 2019, 14:42 |
luke |
what are you expecting to get back? |
| # |
Dec 17th 2019, 14:42 |
luke |
yeah, you need to include all the columns in the group by statement. |
| # |
Dec 17th 2019, 14:41 |
jotpe |
:confounded: |
| # |
Dec 17th 2019, 14:41 |
jotpe |
`*Error:* SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx.Matriculations.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` |
| # |
Dec 17th 2019, 14:41 |
jotpe |
This gives me |
| # |
Dec 17th 2019, 14:40 |
luke |
? |
| # |
Dec 17th 2019, 14:40 |
luke |
should it be membership_id |
| # |
Dec 17th 2019, 14:40 |
jotpe |
*Error:* SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Memberships.id' in 'group statement' |
| # |
Dec 17th 2019, 14:40 |
luke |
I guessed at the select columns you needed. |
| # |
Dec 17th 2019, 14:40 |
slackebot |
<jotpe> |
| # |
Dec 17th 2019, 14:40 |
jotpe |
The query looks like: |
| # |
Dec 17th 2019, 14:39 |
jotpe |
Mhm doesn't work @luke |
| # |
Dec 17th 2019, 14:36 |
luke |
```->contain('Matriculations', function (Query $q) { return $q ->select(['id', 'date_revocation', 'student_id]) ->where(['OR' => ['Matriculations.id IS' => null, 'Matriculations.date_revocation <' => new DateTime()]]); });``` |
| # |
Dec 17th 2019, 14:35 |
davalb |
Had to google it, for a detailed explanations and some options see for instance: https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql |
| # |
Dec 17th 2019, 14:33 |
luke |
@jotpe Try returning the contain from a function, as in the documentation. Are your groups from the contain table? |
| # |
Dec 17th 2019, 14:32 |
jotpe |
I added Users.id to group, but didn't solved it. |
| # |
Dec 17th 2019, 14:32 |
dereuromark |
my favorite :) |
| # |
Dec 17th 2019, 14:31 |
jotpe |
*Error:* SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #13 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'XXX.Names.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by |
| # |
Dec 17th 2019, 14:30 |
luke |
```// In a controller or table method. // Prior to 3.5.0 you would use contain(['Comments' => function () { ... }]) $query = $articles->find()->contain('Comments', function (Query $q) { return $q ->select(['body', 'author_id']) ->where(['Comments.approved' => true]); });``` |