Element sum calculation on views

[dimoss]

Hi

It seems that there is a small bug on the element sum calculation (simple and split) on db views as it doesn't do the sum correctly. The same function is ok on normal db tables...I have latest github 3.0.9 J! 2.5.x.
Thanks.

 

[dimoss]

Friendly bump

 

[cheesegrits]

Works for me:



Do you have the list / menu link set to recalc on display? We cache calculations, and only recalc them when something happens in Fabrik that would change the list or it's data (like saving a form, changing list settings on the backend, etc).

It's possible that in a view, the data is changing "outside" of Fabrik, or at least outside of that list, because the view is pulling in data form another table which is getting modified elsewhere ... in which case we'd show the cached sum, and not recalc, as we don't know that the data might have changed.

-- hugh

 

[dimoss]

Hi Hugh

Yes I have set to recalc on display but the problem is that the view contains fields like SUM(field). So when I select to calculate the total sum of these fields it make the mistake both with split or not.

See the screenshot:

With split


Without split


The second calculation is the correct one and I did it using the Custom Calc putting SUM(%s) as custom query.

Thanks

 

[cheesegrits]

That image I posted is of a view that uses a SUM() to produce the element value I'm then doing a sum calculation on.

What's that entry at the top that doesn't have a value?

-- hugh

 

[dimoss]

"?????????? ?????????? ??????" is one word.
All the entris have values. The wrong one is "?????????" which points 1 but it should be 2.

As you decribe in your post I did the same to produce the element values but I get errors.

The interesting is that it's actually a different result with split which return 148 or wto split which returns 130. Both values are wrong because the correct is 149!!!

 

[dimoss]

Works for me:

It's possible that in a view, the data is changing "outside" of Fabrik, or at least outside of that list,.....

-- hugh

This is exactly my case. The created view pulls data from another table. So it should work like this.

 

[cheesegrits]

I don't even know where to start on this one. If we're re-calc'ing on list load, I can't think of any reason that being a view rather than a table would affect the calculations. We just do really simple "SELECT SUM(foo) AS value" queries on it. We get a bit funky with split / grouping, but you said it's doing this even with splitting.

Is it just that one view, and is it always that one value which is off by 1?

-- hugh

 

[dimoss]

It's not this value only..There are more mistakes in the other columns also. I have checked it on this view as this one has the calcs. In other views i don't have SUM(fields) inside.
I know it's crazy but it is.

 

[cheesegrits]

Just to be certain that the calculations are being re-run, can you edit your ./components/com_fabrik/models/element.php, around line 4740 should be the end of the sum() function:

        $res = $this->formatCalcs($results, $calcLabel, $split);
var_dump($res, $results);exit;
        return array($res, $results);
    }

Can you insert that var_dump() line, and make sure when you load the list page, you get a debug output, and not the list?

Hmmm ... can you also try turning off caching for the list itself (in the advanced settings for the list in Fabrik). I can't remember whether list query caching trumps re-calc'ing or not.

-- hugh

 

[dimoss]

The cache is disabled for this list.
The view is:

select, til_sima.etos, til_sima.minas, til_sima.nomos, til_sima.perifereia, til_sima.eidos, sum(fab_til_sima.katagelies) AS katagelies,sum(fab_til_sima.oikiotheleis) AS oikiotheleis,sum(fab_til_sima.anagelies) AS anagelies,sum(fab_til_sima.roes) AS roes from fab_til_sima group by fab_til_sima.etos,fab_til_sima.minas,fab_til_sima.nomos,fab_til_sima.perifereia,fab_til_sima.eidos order by fab_til_sima.perifereia,fab_til_sima.nomos

 

[dimoss]

I checked in another list in which I have a simlar view from another table and it works ok.
The only difference between the two views is that in the problematic one i have SUMs. In the other I haven't.

 

[cheesegrits]

Are you sure that is the SELECT? That comma after the 'select, ' doesn't look right. And ... do you have something selected as the PK in your list? Doesn't really matter what it is, as long as it's a unique integer.

And have you tried that var_dump()?

-- hugh

 

[dimoss]

The comma after SELECT is typo error here. I put the id field as PK but again I got the same wrong result.
No, I haven't done the var_dump() yet. But I will let you know immediately when I will .

 

[dimoss]

And ... do you have something selected as the PK in your list? Doesn't really matter what it is, as long as it's a unique integer.

-- hugh

Hi Hugh

The problem was on the unique integer and the mistake was mine. When I created a unique integer (id) and put it on the list as PK worked ok.
Thanks for your advice! .

 

[cheesegrits]

Glad you got it working.

-- hugh