How to optimize a query in the calc element

[sasha199]

Hello everyone, I have several calc elements, a request to the database occurs in them

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

$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 = '.$id.' GROUP BY date) AND DATE = '.$month.' AND familia = '.$id.'';
$dbb->setQuery($query);
$datavalue = $dbb->loadResult();
$datavalues = $datavalue;

return (int)$datavalues;

The queries are almost similar, only the condition in if is changed, how to make one query in the calc element and access it in subsequent calcs?

I want to optimize the page for faster loading, and it takes a very long time to make a request in each element

 

[henk]

Don't use calcs, but create a view in the database. Then you can use the view in a normal list.
Views also run a lot faster then calc's.

 

[lousyfool]

how to make one query in the calc element and access it in subsequent calcs?

As said in the Wiki:

You cannot use the values of other calc elements in your code. This creates a chicken and egg situation, as there is no guarantee that another calc element's code has been run yet. The order of execution of calc elements is "arbitrary" - we make no guarantees about what order they run in.

I want to optimize the page for faster loading, and it takes a very long time to make a request in each element

Perhaps consider to use a MySQL view instead.

 

[sasha199]

Don't use calcs, but create a view in the database. Then you can use the view in a normal list.
Views also run a lot faster then calc's.

I would love to use views, but then filtering by month will not work.

 

[juuser]

With a decent web server the above code in calc element shouldn't cause any real performance issues.