How to optimize a query in the calc element

[sasha199]

Hello everyone, there is such a problem I have a page with about 10 cacl elements, there I make a query to the database and output the results, everything works fine, but when I turn on the final calculation, the page gives an error (0) and refuses to work, how to change the query to make the built-in calculator work?

$element = $_POST['month'];
$firstday2 = '{sbrh2_datebase___id}';
if ($element > 0) {
$test = "LAST_DAY('$element-01') + INTERVAL 1 DAY - INTERVAL 1 MONTH";
$test2 = "LAST_DAY('$element-01') + INTERVAL 1 DAY";
}
else
{
$test = "LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH";
$test2 = "LAST_DAY(CURDATE()) + INTERVAL 1 DAY";
}

$dbb = JFactory::getDbo();
$query='SELECT SUM(zp_day) FROM sbrh2_tabel_pers_log WHERE id = ANY(SELECT max(id) FROM sbrh2_tabel_pers_log WHERE familia = '.$firstday2.' GROUP BY date) AND DATE >= '.$test.' AND DATE <= '.$test2.' AND familia = '.$firstday2.' AND id_spec = 7';
$dbb->setQuery($query);
$datavalue = $dbb->loadResult();

return $datavalue;

 

[troester]

What do you mean with "final calculation"?
Which error? Turn Joomla's error reporting and system debug on to get more information.

 

[sasha199]

Thank you for the answer, I figured out the error, I didn't choose the section element, but the problem remained, the total amount of the column still doesn't count. I attach a screen of the column, the total amount of the entire column should be calculated at the bottom, but this does not happen, I turn on the calculation in the element settings, calculations, the amount of the column.

 

[troester]

The calculation in element's settings (SUM, AVG etc) are done on the stored values in the database.

If you don't set the calc elements to "only calc on save"=yes the calc elements are calculating "on the fly" in list view (and details view).
You can't use those calc elements for "element list calculation" or filtering (which is also working with stored values).

If you must calculate "on the fly" (because of e.g. working with current dates or values of other database tables which may have changed meanwhile) you must do the calculation (also) on list load (e.g. php_events plugin) and store the values in the database table.
There are several threads about this.

 

[sasha199]

The calculation in element's settings (SUM, AVG etc) are done on the stored values in the database.

If you don't set the calc elements to "only calc on save"=yes the calc elements are calculating "on the fly" in list view (and details view).
You can't use those calc elements for "element list calculation" or filtering (which is also working with stored values).

If you must calculate "on the fly" (because of e.g. working with current dates or values of other database tables which may have changed meanwhile) you must do the calculation (also) on list load (e.g. php_events plugin) and store the values in the database table.
There are several threads about this.

Thank you very much for the answer, you are the best as always, I will try to solve the question.

 

[deant]

If you don't set the calc elements to "only calc on save"=yes the calc elements are calculating "on the fly" in list view (and details view).

So be carefull how you set calc element. You have to use it with appropriate settings.

Calc element is great element and easy to use. In my first app I use it quite a lot because its very powerfull and handy but...there is allways but...I did not set my calc apropriate. And now a few yeas letter the master teble have +600 rows and two repeat tebles each +1000 row...it takes realy veeeeery loooong looooong time to load a table...BTW 600 rows isnt a lot data at all

Nowdays I rather use JS and user_ajax....I still use calc but with consider.

 

[henk]

For an application like this and for tables with a lot of rows, you better create a database view(s) for it.
A view can be handled by fabrik like a normal table. A database is optimized to run views much more efficient then PHP can do.
My tables contain even more then 1000+ rows and the views run in fabrik in just a few seconds, making all calculations and complicated queries. If I would use calc elements, my application will not be able to run, or times out.

 

[sasha199]

For an application like this and for tables with a lot of rows, you better create a database view(s) for it.
A view can be handled by fabrik like a normal table. A database is optimized to run views much more efficient then PHP can do.
My tables contain even more then 1000+ rows and the views run in fabrik in just a few seconds, making all calculations and complicated queries. If I would use calc elements, my application will not be able to run, or times out.

Please tell me, do you use triggers in the database to calculate?

 

[troester]

Google is your friend, e.g. https://www.mysqltutorial.org/mysql-views-tutorial.aspx

 

[henk]

Calculations in the database are best done by view(s) and functions. I also use triggers, but not for calculations.