-
New Commercial Services Section
We have now opened a commercial services section here on the forum for registered users. If you have a Fabrik project that you wish to have someone work on for you, post it under Help Wanted. If you are an application developer and wish to earn some money helping others, post your details under Fabrik Application Developers.
Both of these are unmoderated. It will be up to both parties to work out the details and come to an agreement.
-
Joomla 5.1
For running J!5.1 you must https://fabrikar.com/forums/index.php?wiki/update-from-github/ or include the new file manually https://fabrikar.com/forums/index.php?threads/joomla-5-1-and-fabrik-cannot-find-files-error.54473/post-285151 See also Announcements
Dynamic sorting on calc element
- Thread starter dimoss
- Start date
cheesegrits
Support Gopher
That depends entirely on whether your calc'ed data has been saved or not. Regardless of the "Only calc on save" setting, we always save the calc'ed value when submitting a form. That setting controls whether we then re-calc on the fly when displaying, or just use the saved value.
If you are using rows where you added a calc element AFTER the rows were created, and some or all of your rows have not been submitted through a form since adding or changing a calc, then the value will not exist in the table, or might be wrong.
If the values are not in the table, then we can't order on them, as that's done in the query with an ORDER BY statement. And we can't do the ordering in PHP after selecting, because in order to handle pagination, that would mean selecting every row in the table (potentially millions), performing your calc on every row, ordering in PHP, then grabbing the 20 (or whatever) we need form the ordered data. Which would be insanely slow, versus just applying an ORDER BY with a LIMIT and selecting the 20 rows we need.
What you will probably have to do, is work out the SQL query to "prime" your calc'ed values in the database by hand. So if your calc was (say) "return (int)'{mytable___foo}' + (int)'{mytable___bar}';", then you'd run a query like "UPDATE mytable SET mycalc = foo + bar".
You would then have a table where the mycalc field is up to date. And as long as the data you are using to calculate mycalc only ever changes through submission of that form, then the data should stay in sync from then on. Adding new rows and editing existing ones through the form will update the values of the calc in the table, so it'll be usable for ordering in a query.
-- hugh
If you are using rows where you added a calc element AFTER the rows were created, and some or all of your rows have not been submitted through a form since adding or changing a calc, then the value will not exist in the table, or might be wrong.
If the values are not in the table, then we can't order on them, as that's done in the query with an ORDER BY statement. And we can't do the ordering in PHP after selecting, because in order to handle pagination, that would mean selecting every row in the table (potentially millions), performing your calc on every row, ordering in PHP, then grabbing the 20 (or whatever) we need form the ordered data. Which would be insanely slow, versus just applying an ORDER BY with a LIMIT and selecting the 20 rows we need.
What you will probably have to do, is work out the SQL query to "prime" your calc'ed values in the database by hand. So if your calc was (say) "return (int)'{mytable___foo}' + (int)'{mytable___bar}';", then you'd run a query like "UPDATE mytable SET mycalc = foo + bar".
You would then have a table where the mycalc field is up to date. And as long as the data you are using to calculate mycalc only ever changes through submission of that form, then the data should stay in sync from then on. Adding new rows and editing existing ones through the form will update the values of the calc in the table, so it'll be usable for ordering in a query.
-- hugh
dimoss
Well-Known Member
Hi Hugh
Thanks for the fast reply.
In fact I created the calc filed after all the rows of this table had been created the data was empty grabbing the values on the fly from the other table.
What you propose is to run an UPDATE query on db level to "fill" this calc field with the values and then put the "Only calc on save" setting to yes in order to have everything synchronized even if I edit or add new rows in the future and also be able to have the ordering normally. Am I right?
Thanks again
Thanks for the fast reply.
In fact I created the calc filed after all the rows of this table had been created the data was empty grabbing the values on the fly from the other table.
What you propose is to run an UPDATE query on db level to "fill" this calc field with the values and then put the "Only calc on save" setting to yes in order to have everything synchronized even if I edit or add new rows in the future and also be able to have the ordering normally. Am I right?
Thanks again
cheesegrits
Support Gopher
Correct. Although the "Only calc on save" doesn't affect the ordering. That just controls whether we re-calc the value or not after we've already done the query with the ordering, and do the rendering of the elements for display. It's obviously more efficient not to do that, but the option is there for a variety of reasons - like whether the calc code you have uses values which might get changed independently of the form being saved.
But yeah, if your calc is just taking values from the same row of the same table the calc is on, and you've "filled" the table with the query by hand, then you can turn "Calc on save only" to Yes, to save the overhead of re-calcing on display.
-- hugh
But yeah, if your calc is just taking values from the same row of the same table the calc is on, and you've "filled" the table with the query by hand, then you can turn "Calc on save only" to Yes, to save the overhead of re-calcing on display.
-- hugh
