| # |
Jan 24th 2020, 06:24 |
 javier.villanueva |
morning all |
| # |
Jan 23rd 2020, 22:09 |
ndm |
you're welcome |
| # |
Jan 23rd 2020, 21:57 |
k4t |
Thank you guys for your time! Really appreciate |
| # |
Jan 23rd 2020, 21:57 |
k4t |
I cant wait :P |
| # |
Jan 23rd 2020, 21:43 |
ndm |
Well, we'll see again when you need to paginate such a query, or when you want to apply certain behaviors on the associated table :grimacing: |
| # |
Jan 23rd 2020, 21:37 |
k4t |
I understand that this is maybe wrong ->matching usage but finally it is generating one and correct query |
| # |
Jan 23rd 2020, 21:36 |
ndm |
What has that query ever done to you? ;) |
| # |
Jan 23rd 2020, 21:36 |
k4t |
I still dont super like it but it is the only way to avoid additional queries |
| # |
Jan 23rd 2020, 21:35 |
k4t |
I think it is just easier to use ->matching which will generate one correct SQL and use results formatter to reformat returned array with data |
| # |
Jan 23rd 2020, 21:31 |
slackebot |
->innerJoinWith('Posts') ->where([...]) })``` |
| # |
Jan 23rd 2020, 21:31 |
ndm |
For one you could try the `subquery` strategy, but I'm not sure that it will actually select all the required fields, or that you can affect that. ```->contain([ 'Comments' => [ 'strategy' => 'subquery', 'queryBuilder' => function($q) { return $q->where([...]) } ] )``` Another way might be to join in the `Posts` association. ```->contain('Comments', function($q) { return $q |
| # |
Jan 23rd 2020, 21:25 |
k4t |
not in comments tabl |
| # |
Jan 23rd 2020, 21:25 |
k4t |
yes it is a field in posts table |
| # |
Jan 23rd 2020, 21:24 |
ndm |
What is `Posts.the_oldest_available_post`? Is that an actual field in the schema? |
| # |
Jan 23rd 2020, 21:16 |
slackebot |
'Comments.release_date >= Posts.the_oldest_available_post' ]); } ) ->group('Posts.id')``` |
| # |
Jan 23rd 2020, 21:16 |
k4t |
``` return $query ->innerJoinWith( 'Comments', function ($q) { return $q ->where([ 'Comments.release_date >= Posts.the_oldest_available_post' ]); } ) ->contain( 'Comments', function ($q) { return $q ->where([ |
| # |
Jan 23rd 2020, 21:15 |
k4t |
that one above is the 2nd select |
| # |
Jan 23rd 2020, 21:14 |
k4t |
it is because ->contain for hasMany generates 2 separated selects |
| # |
Jan 23rd 2020, 21:14 |
k4t |
```SELECT Comment.id AS `Comment__id`, Comment.post_id AS `Comment__post_id`, Comment.created_at AS `Comment__created_at`, FROM Comments Comment WHERE (Comment.post_id in (:c0) AND Comment.created_at >= Posts.the_oldest_available_post AND Comment.published = :c1``` |
| # |
Jan 23rd 2020, 21:04 |
ndm |
Let's have a look at it, maybe there's a fix for that. |
| # |
Jan 23rd 2020, 21:03 |
k4t |
I have that case right now in front of my eyes :P |
| # |
Jan 23rd 2020, 21:02 |
ndm |
Maybe, but I'd have to see it to believe it ;) |
| # |
Jan 23rd 2020, 21:00 |
k4t |
sometimes it is not possible to make exactly the same conditions for matching and contain as it will generate broken SQL |
| # |
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 |