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

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:


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

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 :
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,

Hi Skrol29,

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


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)


The sub1 query returns an array of 2 assoc arrays

but I got this error (if I don't put "noerr")

I've debugged and the array checked by the piece of code that generates this error has only (actual values are correct):

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
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

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.