| # |
Jul 17th 2018, 11:47 |
 neon1024 |
Hah, hate those things. |
| # |
Jul 17th 2018, 11:47 |
birdy247 |
FYI, we are using a endless scroll on the frontend |
| # |
Jul 17th 2018, 11:47 |
birdy247 |
just wondered if there was a less expensive way of quering all books every time |
| # |
Jul 17th 2018, 11:47 |
birdy247 |
so the plan is to query the DB for ALL books, pop into a collection and then take the necessary |
| # |
Jul 17th 2018, 11:47 |
neon1024 |
Yeah, so you’ll need to get all teh books, calculate the score, setup the collection and then paginate that new collection I guess right? |
| # |
Jul 17th 2018, 11:46 |
neon1024 |
Books right? So you’re getting all the books anyway |
| # |
Jul 17th 2018, 11:46 |
birdy247 |
1 User would want to see books with a score > 8 |
| # |
Jul 17th 2018, 11:46 |
neon1024 |
Well you either paginate Books, or you paginate Users. |
| # |
Jul 17th 2018, 11:45 |
neon1024 |
Okay a trait, not an interface, but I was almost right! |
| # |
Jul 17th 2018, 11:45 |
neon1024 |
https://github.com/cakephp/cakephp/blob/master/src/ORM/ResultSet.php#L34 |
| # |
Jul 17th 2018, 11:45 |
birdy247 |
@neon1024 If we paginate a collection, thats all good. but we still need to retrieve all books from the sql. |
| # |
Jul 17th 2018, 11:44 |
neon1024 |
Shouldn’t be too hard, as I think ResultSet implements CollectionInterface iirc |
| # |
Jul 17th 2018, 11:43 |
matt |
thanks, yes think we will go with paginating the collection manually but with caching |
| # |
Jul 17th 2018, 11:42 |
neon1024 |
At least then you alleviate some of the pressure, in exchange for slightly stale data |
| # |
Jul 17th 2018, 11:42 |
neon1024 |
Keyed by user id or something |
| # |
Jul 17th 2018, 11:42 |
neon1024 |
I guess you could cache the calculated user results |
| # |
Jul 17th 2018, 11:41 |
neon1024 |
It works. Is it perfect? Not by half ;) |
| # |
Jul 17th 2018, 11:41 |
neon1024 |
Meaning my request / response time was still snappy |
| # |
Jul 17th 2018, 11:40 |
neon1024 |
Which is why I ended up with a shell, and I could “front-load” the data |
| # |
Jul 17th 2018, 11:40 |
birdy247 |
I think we will see how big the problem is |
| # |
Jul 17th 2018, 11:40 |
neon1024 |
Away from the user request is #1 |
| # |
Jul 17th 2018, 11:40 |
neon1024 |
It’s expensive, it just a matter of choosing where you want to put the expense |
| # |
Jul 17th 2018, 11:39 |
neon1024 |
Yep, and the aggregate table will be huge |
| # |
Jul 17th 2018, 11:39 |
birdy247 |
If we have 10,000 books and 20,000 users, could be quite a cron! |
| # |
Jul 17th 2018, 11:39 |
neon1024 |
Worked for me when I had to calculate bespoke pricing for every API consumer and every package |
| # |
Jul 17th 2018, 11:38 |
neon1024 |
Or on a cron, or both |
| # |
Jul 17th 2018, 11:38 |
neon1024 |
Perhaps either in a worker queue kicked off when the user logs in |
| # |
Jul 17th 2018, 11:38 |
neon1024 |
Then I can update that aggregates table on a per-user basis |
| # |
Jul 17th 2018, 11:38 |
neon1024 |
Then I’d have a shell which processes all the books for every user |
| # |
Jul 17th 2018, 11:38 |
neon1024 |
If it’s really expensive I’d probably do an aggregate table |
| # |
Jul 17th 2018, 11:37 |
neon1024 |
I think I’ve drunk too much tea and eaten too much pick and mix today :shocked_face_with_exploding_head: |
| # |
Jul 17th 2018, 11:37 |
neon1024 |
(╯°□°）╯︵ ┻━┻ |
| # |
Jul 17th 2018, 11:35 |
birdy247 |
The calc is non changeable |
| # |
Jul 17th 2018, 11:35 |
neon1024 |
Simple as that ;) |
| # |
Jul 17th 2018, 11:34 |
neon1024 |
Simplify the calculation and the problem goes away! |
| # |
Jul 17th 2018, 11:34 |
neon1024 |
Ah, so the problem is in the calculation. |
| # |
Jul 17th 2018, 11:33 |
birdy247 |
@neon1024 the use case the @matt is working on is as follows:.. We are bringing back a list of "books" against a set of criteria. Each book is then given a score depending on the logged in user. Its quite an involved calculation to generate the score |
| # |
Jul 17th 2018, 11:33 |
neon1024 |
Thanks @edgaras.jan good job is this in my admin area! |
| # |
Jul 17th 2018, 11:32 |
lorenzo |
https://book.cakephp.org/3.0/en/orm/query-builder.html#using-sql-functions |
| # |
Jul 17th 2018, 11:31 |
edgaras.jan |
> Order fields/directions are not sanitized by the query builder. > You should use a whitelist of fields/directions when passing > in user-supplied data to `order()`. |
| # |
Jul 17th 2018, 11:31 |
lorenzo |
@johnwayne use the functions builder |