| # |
Jan 23rd 2020, 21:00 |
 k4t |
;P |
| # |
Jan 23rd 2020, 21:00 |
k4t |
my standard "workaround for that problem" |
| # |
Jan 23rd 2020, 20:59 |
k4t |
I generally think that I will end with ->matching and results formatter |
| # |
Jan 23rd 2020, 20:59 |
ndm |
But that's hardly CakePHP's fault |
| # |
Jan 23rd 2020, 20:59 |
slackebot |
rethinking about how to query things. |
| # |
Jan 23rd 2020, 20:59 |
ndm |
```$results = $posts ->find() ->innerJoinWith('Comments', function($q) { return $q->where(['Comments.user_id' => 2]) }) ->contain('Comments', function($q) { return $q->where(['Comments.user_id' => 2]) }) ->group('Posts.id') ->toArray();``` And be prepared for more disappointment when your DBMS doesn't allow to select nonaggregated columns that aren't in the group by list, that will required some |
| # |
Jan 23rd 2020, 20:57 |
ndm |
If you don't really use `_matching` (it's more useful for hasOne/belongsTo), then use `innerJoinWith()` instead, which won't put any data in your results. |
| # |
Jan 23rd 2020, 20:54 |
ndm |
Using joins for retrieving hasMany/belongsToMany would come with all sorts of problems on its own. How would you for example filter just the contained association if things are joined in? And how would you handle pagination with all those duplicates? I mean there's solutions for all of this, but I have very, very serious doubts that they'd be simpler than the current implementation. |
| # |
Jan 23rd 2020, 20:52 |
ndm |
Again matching is just for filtering, not for retrievel, the data in `_matching` is just sugar. You need the additional query, there's no way around, that's how the ORM is designed. |
| # |
Jan 23rd 2020, 20:52 |
k4t |
thats the whole problem |
| # |
Jan 23rd 2020, 20:52 |
k4t |
contain will start generating additional queries which I want to avoid |
| # |
Jan 23rd 2020, 20:51 |
ndm |
like @challgren showed |
| # |
Jan 23rd 2020, 20:51 |
ndm |
And you need to filter that containment the same way as the main query |
| # |
Jan 23rd 2020, 20:51 |
ndm |
Correct and expected, you also need contain |
| # |
Jan 23rd 2020, 20:51 |
k4t |
please read: https://cakesf.slack.com/archives/C053DPNGT/p1579812316131900 (I edited it) |
| # |
Jan 23rd 2020, 20:50 |
k4t |
I will receive only one comment |
| # |
Jan 23rd 2020, 20:50 |
k4t |
ok I understand your idea, I think that we have missunderstanding somewhere. I can not group by bacause if I group by Posts.id I wont receive comments which belong to that post |
| # |
Jan 23rd 2020, 20:47 |
ndm |
Most likely by the table's primary key |
| # |
Jan 23rd 2020, 20:46 |
k4t |
and I should group by what? |
| # |
Jan 23rd 2020, 20:46 |
ndm |
the database |
| # |
Jan 23rd 2020, 20:45 |
k4t |
on results set or database? |
| # |
Jan 23rd 2020, 20:45 |
ndm |
That is expected, you need to use grouping. |
| # |
Jan 23rd 2020, 20:45 |
k4t |
that array should contain only 1 Post not 397 |
| # |
Jan 23rd 2020, 20:44 |
k4t |
each Post in that array is exactly the same post but with different Comment under _matchingData key |
| # |
Jan 23rd 2020, 20:44 |
k4t |
imagine that PdfPlace is Post on that screen |
| # |
Jan 23rd 2020, 20:43 |
slackebot |
<k4t> |
| # |
Jan 23rd 2020, 20:42 |
k4t |
I agree, but I think that matching still should return results like ->contain instead of creating weird _matchingData key with associated data |
| # |
Jan 23rd 2020, 20:38 |
ndm |
When using matching on hasMany or belongsToMany you need to use grouping to avoid duplicates, that's the way to go |
| # |
Jan 23rd 2020, 20:38 |
ndm |
Matching is really just for filtering based on associated data, not for retrieving associated data. And using separate queries is just how the ORM works. |
| # |
Jan 23rd 2020, 20:38 |
k4t |
my hair is getting gray becuase of that |
| # |
Jan 23rd 2020, 20:37 |
ndm |
On a serious note |
| # |
Jan 23rd 2020, 20:37 |
ndm |
You need to be more open for new experiences :P |
| # |
Jan 23rd 2020, 20:36 |
k4t |
@ndm do you know what my problem is? |
| # |
Jan 23rd 2020, 20:35 |
k4t |
group by on DB is not needed |
| # |
Jan 23rd 2020, 20:34 |
challgren |
Im not sure though try both |
| # |
Jan 23rd 2020, 20:33 |
challgren |
database, |
| # |
Jan 23rd 2020, 20:33 |
k4t |
groupBy on database or returned ResultsSet? |
| # |
Jan 23rd 2020, 20:32 |
challgren |
groupBy()? |
| # |
Jan 23rd 2020, 20:31 |
k4t |
$results will contain a lot of duplicated items (Posts), and each Post in that array will contain Comment under _matchingData |
| # |
Jan 23rd 2020, 20:30 |
k4t |
which is a big pain for me |
| # |
Jan 23rd 2020, 20:30 |
k4t |
so ->matching is fine, it will give me SQL query which I want but result of association collection is not nested in $results array |