Wrong list calculation

[lcollong]

Hi,

I've a list setup to display a view from the DB. The view is making computation. I've uses "sum" for 3 of the column but the results are inconsistent (see "capture"). You'll find also the view's structure as well as the phpmyadmin result.

Any idea where it could come from ?

 

[lcollong]

Friendly Bump

 

[lcollong]

Friendly Bump

 

[lcollong]

Still not working after the very last github update. Whatever is the number of rows displayed the sum is the same (not summing the displayed rows)...
Is the column calc feature supposed to work on a SQL view ?

 

[lcollong]

Back with this problem. It seems to not add some of the last lines if more than 2 lines..... Very recent github Master Zip update. The element belong to a list which is mapped to an sql view. It contains integer values.

 

[myfatebiz]

I want to help but the sum calculation on list nothing wrong with me.. All are correct. You might try reinstall fresh joomla, template and fabrik component with different route.

 

[lcollong]

It's already a fresh install of a recent master.zip from github. But thanks. I suspect that calculation behavior is different on SQL-VIEW rather than SQL-TABLE ?...

 

[cheesegrits]

Have you specifically enabled "Reload Calculations" in the menu item that display your list?

I don't think there's anything specific to using a view which would alter how we do calculations.

Are you doing any list joins, at the Fabrik level? Having repeat joins can affect calculations.

-- hugh

 

[lcollong]

Hi Hugh,

I've tried with or without reloading the calculations but it still produces an error (see screenshots). I've no dbjoin elt nor joined table. It's purely a statistical view. I'm just using filters. I can't grant access as it is an extranet under production cycle but I'll try to setup a test drive.

 

[rob]

Hi Jerome, which site is this on?

 

[lcollong]

Hi Rob,

As I said it's an "extranet" where theses list are only reachable after login. I can't grant connection to this one even through "mysites" (although it's "rdvpro". I'll setup a subset on my own server and see if I can reproduce it. I'll keep you inform. Thanks.

 

[lcollong]

Hi Rob,

I have setup a testdrive. It's the only one in "mysites". To see the problem, you'll have to connect to the front, select "Tableaux de bord" and "Stats par structure". To make it easier, you'd better filter the list : "Structure" : "AFEPT". It should display only 9 lines. The calculation shows 25 whereas the manual sum says 31. If you reduce the list using "Ann?e", you'll notice some strange behavior. For 2012 it is wrong but for 2014 and 2013 they are right....

This list is pointing on a mysql view made this way :

CREATE VIEW `RDVPRO_Stats_Structures2` AS
    SELECT
        YEAR( r.`date_demande` ) AS `annee` ,
        MONTH( r.`date_demande` ) AS `mois` ,
        o.`nom` AS `structure` ,
        COUNT(DISTINCT r.referent) as nb_referent,
        o.type_structure AS `type` ,
        COUNT( r.`id` ) AS `nb_rendez_vous` ,
        IF(SUM( if( r.type_rdv = 'individuels', 1, NULL ) )=0,NULL,SUM( if( r.type_rdv = 'individuels', 1, NULL ) )) AS nb_individuels,
        IF(SUM( if( r.type_rdv = 'collectifs', 1,NULL ) )=0,NULL,SUM( if( r.type_rdv = 'collectifs', 1,NULL ) )) AS nb_collectifs,
        IF(SUM( if( r.type_rdv = 'stages', 1,NULL ) )=0, NULL,SUM( if( r.type_rdv = 'stages', 1,NULL ) )) AS nb_stages,
        ROUND(COUNT( r.`id` ) *100 / (
            SELECT COUNT( id ) AS nb_total_rdv FROM `RDVPRO_Rendezvous` WHERE (`RDVPRO_Rendezvous`.`statut` >1)),2) AS `pourcentage_rendez_vous` ,
        IF(SUM( b.`rsa` )=0,NULL,SUM( b.`rsa` )) AS `nb_brsa` ,
        IF(SUM( b.`prf` )=0,NULL,SUM( b.`prf` )) AS `nb_prf` ,
        IF(SUM( b.`prescription` )=0,NULL,SUM( b.`prescription` )) AS `prescription`
    FROM `RDVPRO_Rendezvous` r
    LEFT JOIN `RDVPRO_Organismes` o ON r.`organisme` = o.`id`
    LEFT JOIN `RDVPRO_Beneficiaires` b ON r.`beneficiaire` = b.`userid`
    WHERE (r.`statut` >1)
    GROUP BY `type` , `structure` ASC , annee DESC , mois ASC;

 

[rob]

ah ok think I know what this one is.
Your view should select a field which contains a primary key, currently you have the key set to the year, which is not unique and as our query was selecting distinct rows it was missing out one row where the year and date were the same.
ideally the view should contain a field which can be used in fabrik as a unique key, but I've also updated the code so that if your list is based on a view and it doesn't contain any joins, then a non-distinct select is used.
https://github.com/Fabrik/fabrik/commit/4bfed83c2d1513141728e5fd74a4b1b0244809de

 

[lcollong]

Sounds to work ideally. Thanks. However I don't understand the part of your explanation relating to joins. Are you speaking about the build of the mysql view itself or of any joins inside the fabrik'list and/or dbjoin element ? As my view is built using two left join and your work made it working, I'm a bit confused.

 

[rob]

I was referring to any joins you had might have added to the view from within Fabrik, not any join that was used to initially create the view.
Once the view is created Fabrik views that as a single database table.

 

[lcollong]

Clear ! Thanks. It works perfectly.