TinyButStrong - the PHP Template Engine
Categories > TinyButStrong general >

strange behaviour (error?) with Aggregate plugin inside sub blocks

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs

By: Walter
Date: 2014-07-23
Time: 19:31

strange behaviour (error?) with Aggregate plugin inside sub blocks

Hi,

I'm building a report of some votations, and I use an XLSX template. I have an outer block iterating over "votations", and an inner block iterating over "votes" (that are, the possible votes castable in a single votations).
Let us say that we have 2 votations, and in each one voters can choose among a bunch of possible votes, or can choose not to vote.

So I repeat the votations block for each votations, and inside each votation I present the number of votes cast bu the voters on the single votations. So far, it works..
this is the pseudo code
[votations;block=begin]                       
                       
[votations.title]                       

[votes;block=begin;p1= [votations.id];aggregate=num_not_null:sum,num_null:sum,num:sum]                       
[votes.title;if [val]='';then 'Non votanti';else [val]]    $    [votes.num_not_null;if [val]=0; then [votes.num_null]; else [val]]                [votes;block=end]
                       
Totale votanti    $[votes.num_not_null:sum]                   
Totale non votanti    $[votes.num_null:sum]                   
Totale generale    $[votes.num:sum]                   
                       
[votations;block=end]                       

Please imagine this in some Excel cells.. rows correspond to actual rows. The "$" sign, I've put it to clarify cell boundaries but it's not present in the template
For each possibile vote, my SQL query returns three numbers: num_null, num_not_null, and num.
At the end of the block I'd like to display the sum of all three counters for all the votes cast for a given votation.

This sort of works, that is, this is my outcome:


Votazione di Lista x CDA                   
Voto    $    Voti Raccolti    
                   
Lista 3            $ 76           
Lista 2            $ 250           
Lista 1            $ 1826           
Non votanti        $ 434           
                   
Totale votanti    $ 961               
Totale non votanti    $ 1625               
Totale generale    $ 2586               
                   
                   
                   
Votazione di lista x Collegio Sindaci                   
Voto    $    Voti Raccolti    
                   
lista "Dei Buoni"        $ 74           
lista "I Belli"        $ 887           
Non votanti        $ 1625           
                   
Totale votanti    $ 961               
Totale non votanti$$    1625               
Totale generale    $ 2586               

As you can see, the second totals (961,1625,2586) is correct with the above numbers, while the first is the same as the second, and is wrong.

I've debugged the Aggregate Plugin (easy) and in the AfterMerge function, the sums are stored correctly. This method is called twice (I've got 2 votations in my DB) and each time it stores the correct sums..
I suspect that OpenTBS itself will just ask for the sums at the end of the whole outer loop, and thus will get just the latest Aggregate calculations.

But, as per Aggregate manual, the sums MUST be placed outside of the relevant block, so I don't know what to do. I'll look at the TBS code but it's a bit convoluted for me.
Any suggestions, maybe I'm doing something wrong? I hope so..

Thanks
W
By: Skrol29
Date: 2014-08-02
Time: 17:59

Re: strange behaviour (error?) with Aggregate plugin inside sub blocks

Hi Walter,

Thanks to had detailed your problem with this precisions. I could reproduce the behavior.

The problem comes from using the Aggregate plug-in withing a Sub-block with dynamic-query.
It has nothing to do with OpenTBS.

Because Sub-blocks with dynamic-query work with a separated MergeBlock() call, external fields are merged at the end of all sub-blocks merging.
That's why all the sum fields have the same values in your result: they are all merged with the last sum.

The solution is to replace the "Sub-blocks with dynamic-query" with an "Automatic sub-block".
This can be done like this:

At the template side :
- replace [votations;block=begin;sub1=votes;ondata=f_ondata] with [votations;block=begin;sub1=votes;ondata=f_ondata]  
- delete the parameter "p1=[votations.id]"
- rename all [votes] fields by [votations_sub1], don't forget the embedded field.

At the PHP side:
- delete the line that merges block "votes"
- add a new function f_ondata()
   It can be a method if you prefer, see parameter ondata in the manual.

The "ondata" function has the purpose to add the new colmun "votes" that should contain the sub-records .
Example :
function f_ondata($BlockName,&$CurrRec,$RecNum) {
    $id = $CurrRec['id'];
    $records = $sql->fetchAll("SELECT ... WHERE id = $id"); // here your code retrieving the sub-records.
    $CurrRec['votes'] =$records;
}

Since automatic sub-blocks are merged on the fly, external aggregate fields are actually merged after the local sub-block is merged.
So the result will be correct regarding to your goal.

I have to add a note about the behavior of the Aggregate plug-in withing a Sub-block with dynamic-query.
Regards,
By: Walter
Date: 2014-08-04
Time: 16:36

Re: strange behaviour (error?) with Aggregate plugin inside sub blocks

Hi Skrol29,

Thanks a lot.. seems to work better.. almost, but not quite.

[votations;block=begin;sub1=votes;ondata=~f_ondata]           
           
[votations.title]           
Voto     $    num of votes 
[votations_sub1;block=begin;when=num_not_null != '';aggregate=num_not_null:sum,num_null:sum,num:sum]           
[votations_sub1.title;if [val]='';then 'Non voting';else [val]]     $    [votations_sub1.num_not_null;ope=tbs:num;if [val]==0;then [votations_sub1.num;noerr]]    
Total voters $    [votations_sub1.num:sum]    
        
[votations;block=end]           

This is my code. f_ondata does the same thing as before, as you suggested.... BUT I have an error.. in the second if (this displays sub1.num if sub1.num_not_null ==0)

[votations_sub1.num_not_null;ope=tbs:num;if [val]==0;then [votations_sub1.num;noerr]]    

The sub1 query returns an array of 2 assoc arrays
Array
(
    [0] => Array
        (
            [num_not_null] => 74
            [num_null] => 0
            [num] => 74
            [vote_value] => 7
            [short_name] => LCS2
            [title] => lista "Dei Buoni"
            [shares] => 415295
        ),...

but I got this error (if I don't put "noerr")
TinyButStrong Error in field [votations_sub1.num...]: item 'num' is not an existing key in the array. This message can be cancelled using parameter 'noerr'.

I've debugged and the array checked by the piece of code that generates this error has only (actual values are correct):
   [num_not_null:sum] => 2174
            [num_null:sum] => 430
            [num:sum] => 2546

So of course it does not find "num".. but I wonder WHY is TBS looking for "num" in the aggregate array?
Please help me.. what am I doing wrong this time?

Thanks
Walter

PS is there a way to pass a parameter to the "ondata" function, different for each iteration on "votations"? basically I'd like to execute two queries as in
[votations..;block=begin;sub1=votes;sub2=votes]  <== this executes THE same query with a different param (see below)

[votations_sub1;block=begin;param1=A is not null;.....] ... [votaitons1_sub1;block=end]
[votations_sub2;block=begin;param1=A is   null;.....] ... [votaitons1_sub1;block=end]


[votations..;block=end]
I know I can duplicate the queries, and differentiate them.. but since they are 20 lines long.. I'd rather keep the source code in the same place, just once
By: Skrol29
Date: 2014-08-05
Time: 00:30

Re: strange behaviour (error?) with Aggregate plugin inside sub blocks

Hi Walter,

> BUT I have an error.. in the second if (this displays sub1.num if sub1.num_not_null ==0)

You have to check that the field [votations_sub1.num_not_null;...] is actually placed inside the [votations_sub1] block. And not outside.

> PS is there a way to pass a parameter to the "ondata" function

Yes but not the way you're expecting.
The OnData function has an argument which is the parameters of its TBS tag. Tha's the way to pass arguments.
But The OnData function is not called for each field, is is called each time TBS has to fetch a new record for the block ; and the OnData function can complete the current record.

> I know I can duplicate the queries, and differentiate them.. but since they
> are 20 lines long.. I'd rather keep the source code in the same place, just once

You can however capitalizes the SQL call in the OnData function.