Recalculation of all scores (calc element) on update or insert

[chofra02]

I have created a list and with this a table to manage the results of a competition.

The competition is during one month and each participants can enter it's own results. The score is a percentage of the best score in its age and weight category.
what I would like is to recalc the scores for each row every time a competitor insert or update a result.
Is this possible with Fabrik or do I have to go back to program some triggers / routines in the db?

structure is: competitor id - age categorie - weight categorie - distance - score

thanks for your support.

 

[troester]

A calculation element set to "calc on save only"=no (which is the default) is calculating every time the list or form is displayed, so just use a calc element.

 

[chofra02]

I was thinking so too but I'm not that good with your calc expression. what I need to do is as follow:

I have a result table where participants are entering their distance.
I have one field score that need to get updated before displayed. The score need to be calculated as follow:
a) finding the best distance based on the age and weight categorie the participant belongs to
b) Calculating the percentage of this distance reached , that's the score.

And this for each participant, meaning for each row in the table.

Any help is welcome

 

[troester]

Do you really need the score stored in the database? (this is the complicated way)

Or only calculated "on the fly" every time the list or form is displayed? (this is just the calculation element)
For calculating see
http://fabrikar.com/forums/index.php?wiki/common-php-tasks/
so something like (pseudo-code)

select max(distance) from your-table where cat=participant-cat and age=participant-age
return participant-distance*100/max-distance

 

[cheesegrits]

As Troester says, the approach you need to take depends on whether you actually need the score stored in the table, or whether you can calculate it "on the fly" when display the results.

The problem here is obviously that if you actually want the score in the table to always be up to date, EVERY row for that age/weight category could have to be recalculated every time someone in that category submits a result, because the new submission may become the "target" (best) upon which everyone elses score needs to be based.

So how you do this dpeends on whether you ever need to display results outside of Fabrik. if you are only ever displaying the results within Fabrik, then using a calc element would work, as Fabrik could then re-calculate the value every time it is displayed.

But if you need to access / display thes resuls elsewhere, outside of Fabrik, then you'd be better of doing this as a form submisison plugin, which updates all the rows as necessary whenever a row is added / edited.

It may also be possible to create a MySQL view which shows the results, doing the scroe calculation as part of a view's SELECT. O)r, as you say, you could do this with a MySQL trigger.

-- hugh

 

[chofra02]

Hi Cheesegrit and Troester,

thank you first for your answers. It definitely helped me think about the whole design.
Troester is right, I do not need this to be stored permanently in the db.
The reason why I thought about a db update is that when you add an element to the list, then a new field is added in the db table you indicate.

I don't need to show the score outside of Fabrik and my site. So it is good enough to implement a calculation plugin element.

I have done that based on Troester's recommendation and here is the php code I used for the calc:

$dbscorecon = JFactory::getDbo();
$agecat= $data['irwl_results___agecat'];
$weightcat= $data['irwl_results___weightcat'];

$queryscore = "SELECT MAX(raw09) FROM irwl_results WHERE agecat =". $dbscorecon->quote($agecat)." AND weightcat =". $dbscorecon->quote($weightcat);
$dbscorecon->setQuery($queryscore);
$09max = $dbcon->loadResult();
$dist09 = $data['irwl_results___raw09'];
$score09 = $dist09*100/$09max
return $score09;

I hope this does the math and will test it now. Please feel free to comment on this or tell me if I'm on the wrong path.

 

[cheesegrits]

You are using an undefined variable in that $dbcon->loadResult(), needs to be $dbscorecon.

Also I don't think you can start variable names in PHP with a number. So $09max needs to change to something else.

You should probably also add a little defensive coding, to make sure $09max (or whatever you call it starting with a letter) is not 0, so avoid a fatal "divide by 0" if for whatever reason the query doesn't come up with a valid result. Something like ..

$score09 = empty($zero9max) ? 0 : $dist09*100/$zero9max;

NOTE - the only real disadvantage of doing this with a calc is that it's going to generate an extra DB query for every row being displayed in a list view. So if you are showing 50 rows in that list, that's 50 extra queries, to do the MAX select. Probably not an issue, but depends on how many rows are in the table. If you have a lot of rows (like many thousands), it could get expensive.

So I would definitely suggest that you make sure that the agecat and weightcat fields both have indexes on them. One way to do this is to make them filterable in Fabrik (in the element's List settings), which will cause Fabrik to automagically add an index for them. Or you can use phpMyAdmin, and add indexes for them by hand.

-- hugh

 

[cheesegrits]

Oh, and while not essential, it's good to get in the habit of using J!'s query builder. So rather than specify a raw query, do ...

$query = $dbscorecon->getQuery(true);
$query
   ->select('MAX(raw09) AS max_raw09')
   ->from('irwl_results')
   ->where("agecat =". $dbscorecon->quote($agecat)." AND weightcat =". $dbscorecon->quote($weightcat));
$dbscorecon->setquery($query);

 

[chofra02]

hi,

I tried to implement based on your advices but I think I will hire a professional coder.
This is the last piece of code I tried with no success as the value returned is empty:

$dbscorecon = JFactory::getDbo();
$agecat= $data['irwl_results___agecat'];
$weightcat= $data['irwl_results___weightcat'];
$dist09 = $data['irwl_results___raw09'];

$query = $dbscorecon->getQuery(true);
$query
->select('MAX(raw09) AS max_raw09')
->from('irwl_results')
->where("agecat =". $dbscorecon->quote($agecat)." AND weightcat =". $dbscorecon->quote($weightcat));
$dbscorecon->setquery($query);
//$max_raw09 = $dbscorecon->loadResult();
$score09 = empty($max_raw09) ? 0 : $dist09*100/$max_raw09;
$score09 = $dist09*100/$09max
return $score09;

I certainly have misunderstood something about how to put the different code pieces that thankfully posted above but I'm too tired as of now to give it a new run. May be you see it faster than I do. Let's hope so.

 

[cheesegrits]

Well, you've commented out the loadResult() line, so you'll never have the result of the query in $max_raw09.

Also, you've left ...

$score09 = $dist09*100/$09max

... in there, with no ; on the end, which is going to throw a fatal error. That line just needs to be removed, as it's been replaced with the previous line (using $max_raw09 instead of $09max).

If you'd care to take out a Pro sub, I'd be more than happy to simply do this for you, hands-on on your site. Probably cheaper than hiring a non-Fabrik coder, and you'd have the balance of a month with higher priority support responses. #justsayin

(And of course it'd help me and Rob put food on thte table, LOL!)

-- hugh

 

[chofra02]

Hi Cheesegrits,

I made it on my own after finally taking the time to seriously think about it. The code bellow does the math for me (even if it's maybe not pretty):

$dbcon = JFactory::getDbo();
$agecat= $data['irwl_results___agecat'];
$weightcat= $data['irwl_results___weightcat'];
$dist09 = $data['irwl_results___raw09'];

$query = $dbcon->getQuery(true);
$query
->select('MAX(raw09) AS max_raw09')
->from('irwl_results')
->where('agecat = '. $dbcon->quote($agecat) .' AND weightcat = '. $dbcon->quote($weightcat));
$dbcon->setquery($query);
$max_raw09 = $dbcon->loadResult();
$score09 = empty($max_raw09) ? 0 : $dist09*100/$max_raw09;
$formscore09 = number_format($score09, 2, '.', ',');
return $formscore09;

If you would like to do some work for me , let's discuss offline, I'm always open for such a discussion.
But first of all , thank you for your very kind support and patience ...
Cheers

 

[cheesegrits]

Glad you got it going.

-- hugh

 

[chofra02]

Hi, I'm still not there where I would like to be, there is still an issue with the calculation:

I have one table called irwl_results and one list called irl_results as well as form and group with the same name.
with the following calculation I calculate the best score and all the other scores as well in a calculation field called score09:

$dbcon = JFactory::getDbo();
$dist09 = $data['irwl_results___raw09'];
$query = $dbcon->getQuery(true);
$query
  ->select('MAX(raw09) AS max_raw09')
  ->from('irwl_results');
$dbcon->setquery($query);
$max_raw09 = $dbcon->loadResult();
$score09 = empty($max_raw09) ? 0 : $dist09*100/$max_raw09;
$formscore09 = number_format($score09, 2, '.', ',');
return $formscore09;

With this the top scorer get 100.00 points and the rest proportional points based on their distance.
This works so far so good in an non pre-filtered list (see allcat.pdf attached to this post) apart from the fact that the sort on Total does not work

Now I would like to pre-filter this list based on the categories the rower belongs to and get a specific ranking based on some of those.
So for example in the basedonindoor the rower belongs to a team, and he calls the team ranking list. This list is a copy of the all categories list with a pre-filter and pre-filter query.
I would like the top scorer to be shown on top of the list with 100 points and then the rest proportional to his score and this should be done by the calc element. Instead of that, the calc element is not recalculating but showing what ever value has been stored in the db (apparently some values calculated on save) and the ordering does not work also.
I understood from your previous reply that the calc element calculates "on the fly" when the list is called and this based on the list input.
Am I wrong or just missing something?

Thanks for your help.

 

[chofra02]

OK, let me summarize, the error with the calc is found. I was to stupid and calculated the max of the whole column instead of applying the same search criteria as for the pre-filter.
I did also some progress regarding how to get this table sorted. I think that I put the finger on the issue but don't really know what to do.
As written above I would like to sort based on an element named Total. The issue here is that this element is a calc element that add value of calc element that I'm recalculating on the fly (meaning by loading the list).
I saw that the calc element I'm summing in Total like calc09 are somehow calculated and saved in the db when a value is submitted in the form. Even if the value does not correspond to the calculated element value later on in the list, it has a certain influence on the order.
I also noticed that some of the calc value has not been correctly calculated as I had some calculation mistakes on the time the value has been submitted in the form.
So, what do I need to do in order to resync the whole table in order to have the sorting on Total working? Do I need for example to run an update on the db recalculating some values ?

the calculation in the total field looks like this:
return (float)'{irwl_results___score09}' + (float)'{irwl_results___score10}'+ (float)'{irwl_results___score11}'+(float)'{irwl_results___score12}'+ (float)'{irwl_results___score01}'+ (float)'{irwl_results___score02}'+ (float)'{irwl_results___score03}';

all of them are calc fields.

Thank you for your help.

Cheers

 

[cheesegrits]

Well, this goes back to what I was saying in post #5 in this thread:

The problem here is obviously that if you actually want the score in the table to always be up to date, EVERY row for that age/weight category could have to be recalculated every time someone in that category submits a result, because the new submission may become the "target" (best) upon which everyone elses score needs to be based.

As you just discovered, that applies to sorting, which is done in the database query, using "ORDER BY ...". So if you want to order by your calc, or some other field that uses that calc, then you'll have problems, unless you update every row (that needs it) in the table, on every submission.

So, the answer to your question:

So, what do I need to do in order to resync the whole table in order to have the sorting on Total working? Do I need for example to run an update on the db recalculating some values ?

Is yes. But its not just a one-off deal to fix the incorrect values. As explained above, you'll have to re-calculate all those totals, on every form submission, for every row in the table.

The only way I can see this working is with a PHP form submission plugin, instead of (or as well as) a calc element. Whether you need both depends on whether you are using "AJAX calc" to show the value live on the form, as they enter the data, or you just need it enetered as the form submits.

Another way might be to use a MySQL view, but that introduces another level of complexity to your overall application, as youd have to use different copies of the list for different purposes - we treat MySQL views as "read only tables", and trying to write data to them may have unpredicatble issues, as MysQL is very touchy about when a view can and can't be updated.

So, the next step would be to work out the query you would need to do that whole table update. The actual PHP to use in the submission plugin would be simple enough, I can provide you with that. But the query to do it, I don't have off the top of my head. It would be somewhat more complex than doing it for a single row. If you want to go googling, see if you can find an example we can work from, that'd be good. If not ... I'll do my best, but there's a limit on how long I can spend coming up with custom queries like this, on a Standard support subscription. That's more of a Pro level thing.

-- hugh

 

[chofra02]

Hi, I have recalculated the whole table and the score in the db as I'm better in the db as via PHP and so on. After I've done that recalculation everything worked fine, except from one and that's the leader score.
The issue with sorting comes from the calculation above. I calculate a float that I then store in a text field as all the calc field are somehow text fields in the db (don't know actually why I can't choose the type of my calc element and it's format). So the result of dividing the top scorer by itself is 100 without and 100 is then stored in the field from the left side (text field). Now I calculate the other and I get for example a score like 99.07 and this is also stored from the left side in the text field.
Now I'm sorting this text field an guess what happens , string comparison first character found for top scorer is 1 , first character found for 2nd scorer is 9 and here we go ....
So now based on the fact that my score calc element is a text field I will need to add to all the score lower than 100 a blank in front of the number to do the math or I will need to generally fill the field with blanks from the left side to avoid this thing.
Isn't there a way to set calc elements to something else than text ?
Would be happy to know the solution and will experiment in the meanwhile the blank filling thing.

 

[cheesegrits]

You can manually set the field type in phpMyAdmin to INT (of whatever size you need). Just make sure you then set the "Alter field types" to "No" (or "Only add new", or whatever) in the List's advanced settings, so we don't set it back to TEXT or VARCHAR (or whatever it defaults to atm) next time you save that element.

But as far as I can tell from your description (I'm getting a little confused, so I may be wrong), you are still going to need to re-calculate the entire table after any row is added or edited, as that action could change the scores of other rows, as they are relative to the highest score in each category?

The reason we never added a "Field type" option to the calc element is that people rarely sort on the calc element, due to it's nature. And if they do, there is a workaround, as described above. We typically try to only provide a built in option for something if 80% or more of users would find it useful - the old Microsoft 80% Rule. Fabrik already has WAAAAAY too many options, and suffers from Creeping Featurism (or Feeping Creaturism), as more options get added over time.

-- hugh

 

[chofra02]

Hi Guys,
here I'm back again. first of all, I would like to thank you for all your patience and good tips as I now almost got where I wanted to! The lists calc are working well, sorting also.
The thing that I did in order to get my main result list recalculating correctly was to put the calc elements of the main list to recalculate on save. This has so far done the math.
I'm now only missing one thing and I would need a tip from you how I could implement it.
I would like to have a ranking position in my list that is updated every time I load this list based on the sort I'm doing.
so for example:
If I would have the following scores: Tom = 100, Jim = 95, Niklas = 89.
The actual list will look like after sorting :
Tom 100
Jim 95
Niklas 89
What i would like to have is a column that shows the position like this:
1 Tom 100
2 Jim 95
3 Niklas 89

is there an easy way to generate this position column numbering?

Another thing that I would also like to have is a kind of "How good am I" button that would immediatly show the list page where the competitor is listed in and highlight his row? Is this possible with Fabrik?

 

[cheesegrits]

Hmmm, I know I did one of those "ranking" displays for someone a while back, just can't remember how I did it. I think it was with a custom list template, using a loop counter on the main row display, inserting the ranking number in to the table structure "by hand". It also had to take account of pagination, so adding "this page number" * "number of rows per page" to the rank.

On your second question, again, hmmm ... that might be a bit tough. Thinking out loud ... it would require a couple of custom queries, one to lookup the user's row and get the score, and another to count the number of rows with a greater score ... then work out what page that is based on the current pagination display size, then redirect the browser to that page ... and that code would probably have to either use the php_list plugin, or be embedded in to a custom template, which looked for a custom query string argument on page load ... which would have to come from a button embedded in the custom template which loaded the list page but with the added query string arg (like &show_me=1).

And to be honest, both of those things are definitely Pro kind of stuff, they are outside the scope of a Standard sub. At the Standard level we can help out with a few lines of code, and lots of advice, but for us to write custom code of more than a handful of lines, it has to be a Pro sub.

-- hugh