| # |
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 |
| # |
Jul 17th 2018, 11:31 |
edgaras.jan |
`->order(['duration' => $this->getRequest()->getQuery('direction')])` is insecure |
| # |
Jul 17th 2018, 11:23 |
josbeir |
:P |
| # |
Jul 17th 2018, 11:23 |
neon1024 |
Yeah, but currently I only need it in that one place ;) #lazy |
| # |
Jul 17th 2018, 11:23 |
johnwayne |
Is there a way to integrate in cakephp query '->where([CAST(id / POW(3, CAST(LOG10(id) AS SIGNED)) AS SIGNED) = $myId])' |
| # |
Jul 17th 2018, 11:22 |
josbeir |
you will get that value everywhere you need it |
| # |
Jul 17th 2018, 11:22 |
josbeir |
i'm still conviced that, in your case @neon1024 it would be cleaner to also store the duration in db |
| # |
Jul 17th 2018, 11:21 |
neon1024 |
Depending on what it is and what data it involves of course |
| # |
Jul 17th 2018, 11:20 |
neon1024 |
Yeah, if you can’t do the calc in SQL, it’s probably worth storing it |
| # |
Jul 17th 2018, 11:19 |
josbeir |
(or store the calculated field in db too) |
| # |
Jul 17th 2018, 11:19 |
neon1024 |
If you want to use PHP, you’d probably need to look into paginating a collection |
| # |
Jul 17th 2018, 11:18 |
neon1024 |
I’m not convinced you can’t calculate in SQL, it’s pretty well featured |
| # |
Jul 17th 2018, 11:18 |
josbeir |
@dereuromark did you find it? |
| # |
Jul 17th 2018, 11:13 |
matt |
I was thinking I need to get all the results from the query, map in my calculated value and then paginate the results myself using collection take etc |
| # |
Jul 17th 2018, 11:12 |
matt |
thanks though my problem is I can't calculate the field using sql |
| # |
Jul 17th 2018, 11:11 |
neon1024 |
You probably only really need lines 3 and 6-8 |
| # |
Jul 17th 2018, 11:11 |
neon1024 |
File uploaded https://cakesf.slack.com/files/U1BT622HW/FBT5SFH6J/sorting_on_calculated_field.php / https://slack-files.com/T053DPNCM-FBT5SFH6J-9f79beece9 - This is for the Crud plugin, but the query instance is the same either way. |