[solved]how to prevent several run of calc element?

[samani]

hi..
i have this code in my calc element:

$myDb = FabrikWorker::getDbo(false, 2);
// now it's just standard J! database API stuff, and probably some Fabrik placeholders
//$something = '{yourtable___something}';
$myQuery = $myDb->getQuery(true);
$myQuery->select('COUNT(id)')->from('mdl_question_attempt_steps')->where('userid = "9311" ');
$myDb->setQuery($myQuery);
dump ($myDb->loadResult(),"loadResult");
return $myDb->loadResult();

but dump show me it run 3 time and also my result is *3
foe example my original result of my query is 60 but this Calc element is return 180.
how to solve that?
thank you...

 

[Sophist]

The calc php does indeed run multiple times - by design. However this does not explain why it gives a result 3* the value it should. I think that this is coincidence.

I am not sure that this explains either, it but you are running the query twice each time the calc is run because you run loadResult twice. You should really do:

$result = $myDb->loadResult();
dump($result,"loadResult");
return $result;

Also you should really escape the table column and names with "`" for safety (though it is difficult to see how an SQL injection attack could be carried out here).

 

[samani]

the problem is in my query...excuse me sophist and thank you for your reply but i have a question is it good for performance when calc element 3 time run?
thank you...

 

[troester]

• The calc element should only be used to return a value. It should not be used to "do things", like update database tables. If you need to do "stuff" other than just return a value for the element, you should use a PHP form submission plugin.

http://fabrikar.com/forums/index.php?wiki/calculation-element/#important-notes

 

[Sophist]

No - when the calc runs SQL or makes a web service call it is not good for performance - but at some point in the past Hugh did reassure me that it was necessary.

I guess Fabrik could do with some generic helper functions to cache SQL results - at present some element types already do this (e.g. database join), but it seems to me it would be useful to have Fabrik override the Joomla execute SQL methods (like loadResults) in order to avoid repeating the SQL call.

That said, MySQL is supposed to have some caching of SQL results of its own, so the performance hit might not be that bad.

 

[Sophist]

The calc element should only be used to return a value. It should not be used to "do things", like update database tables. If you need to do "stuff" other than just return a value for the element, you should use a PHP form submission plugin.

Yes - but @samani is only doing a read-only SELECT statement, so he is not doing an update.

 

[cheesegrits]

The calc element simply wasn't designed for doing heavy lifting. If running more than once is an issue, use a PHP submission plugin instead. End of story.

-- hugh

 

[samani]

i use import csv for import my data and is there PHP submission plugin work?
(calc element work correctly)

 

[Sophist]

The calc element simply wasn't designed for doing heavy lifting. If running more than once is an issue, use a PHP submission plugin instead. End of story.

You also need to remember that unless you set "calc only on save" the calc element is run once per row of a list displayed.

So if you are displaying 100 items in a list it will run 100 times, once for each row. Which means it will do 100 consecutive SQL queries.

So another thing you need to think about is to do a List PHP plugin PHP to do the calculations in a single REPLACE query if the calc is based on data which might change and you want to avoid this performance issue. I can provide an example if needed.

 

[samani]

So another thing you need to think about is to do a List PHP plugin PHP to do the calculations in a single REPLACE query if the calc is based on data which might change and you want to avoid this performance issue. I can provide an example if needed.

thank's alot Sophist and cheesegrits.
@Sophist if is it possible,please let me know your example...

 

[cheesegrits]

i use import csv for import my data and is there PHP submission plugin work?
(calc element work correctly)

CSV import doesn't run form submission plugins, no. But there is a relatively well documented 'listcsv' List plugin that lets you modify imported data.

-- hugh

 

[Sophist]

I have added my example to the performance section of the Calc element wiki page: http://fabrikar.com/forums/index.php?wiki/calculation-element/

 

[samani]

well documented 'listcsv' List plugin

i study document and work according to that exactly...but
$formModel->formData['us_streets___street_desc']="testing";
that is in document, not work for me (also i use fabrik v 3.8 not 3.8.1)
i use this syntax and it's work correctly:
$formModel->updateFormData('table___myelement', $foo, true);
can i edit wiki or this need to change by you?