| # |
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]); });``` |
| # |
Dec 17th 2019, 14:29 |
luke |
I think you need to pass conditions to contain differently https://book.cakephp.org/3/en/orm/query-builder.html#passing-conditions-to-contain |
| # |
Dec 17th 2019, 14:29 |
davalb |
can you post the sql error? |
| # |
Dec 17th 2019, 14:26 |
jotpe |
Anyone has a hint? |
| # |
Dec 17th 2019, 14:26 |
jotpe |
This contain breaks the query: `->contain(['Users' => *['Names']*, 'Terminations'])` |
| # |
Dec 17th 2019, 14:25 |
slackebot |
<jotpe> |
| # |
Dec 17th 2019, 14:25 |
jotpe |
Hey, I try this again. I have a query with some left joins. When I add a contain with nested Models, the Model breaks: |
| # |
Dec 17th 2019, 14:23 |
gianmarxgagliardi |
I have three tables with this structure: https://pastebin.com/e2PwW5bs when I go to work on the dogscats table by adding an ennuple using the special "add" function developed in DogsCatsController it shows me only ID of "cat" or "dog", but I would like to show the name .... |
| # |
Dec 17th 2019, 14:22 |
dereuromark |
see https://documentation.spryker.com/tutorials/howtos/ht-case-sensitive-file-system-mac.htm |
| # |
Dec 17th 2019, 14:21 |
davalb |
I see, thanks |