On the fly record count on criteria based

[dimoss]

Hi

I have the following calc element which shows a text or not if some criteria meet:

$a = (int)'{fab_cgs___tournament_raw}';
$b = '{fab_cgs___assoc_raw}';

$query1 = "SELECT count(*) FROM fab_cgs WHERE tournament = $a AND assoc = '$b'";
$db = & JFactory::getDBO();
$db->setQuery( $query1 );
$y = $db->loadResult();

$query2 = "SELECT organizer FROM fab_tournaments WHERE id = $a";
$db = & JFactory::getDBO();
$db->setQuery( $query2 );
$xx = $db->loadResult();

if (($y > 2) && ($xx != $b))
{return 'WL';}
else if (($y < 2) && ($xx != $b))
{return '';}
else if ((($y > 2) && ($y <= 11)) && ($xx == $b))
{return '';}
else if (($y > 11) && ($xx == $b))
{return 'WL';}
else
{return '';}

The above works fine and if I set the Calc On Save parameter to 'Yes' then puts the WL text on the record. However as it saves the text this cannot be changed when a record deleted.

So I set the Calc On Save parameter to 'No' the text is not saved and the value is calculated on the fly for table rows. However this brings another problem. When the calculation meet the WL criteria then ALL the records on the table have this WL text.

Is it possible this text be shown only on all records after the 'WL' criteria are met?

Thanks!

Dimos

 

[felixkat]

However as it saves the text this cannot be changed when a record deleted

I'm not sure what you mean by the above. I assume you mean when saving a record.. What is the actual message? It's not clear if this message happens when you delete a record or that's a part of the message.

 

[dimoss]

Hi,

I will give you an example:

Each user can enter three players. From the 4th and so on all the rest will have the WL which according to the calc settings will be stored in the db.

Let's say that the user has entered 4 players and delete the 1st in the row. In this case we have 3 players in total BUT the last player keeps the WL sign which is wrong..

I hope you understand what I am trying to say.

The only thing is the condition to be calculated on the fly with a table php plugin somehow.

Thanks.

Dimos

 

[felixkat]

So theoretically the routine should only initially run if there's 4 or more players.

However on an edit, (in the case of a deletion), it should note if players are removed and reset any that are 3 or under to not be WL?

 

[dimoss]

Yes...and put the WL sign as needed.

 

[felixkat]

I assume the players are a repeat element or in a repeat group?

 

[dimoss]

No repeat group..it's a single group.

 

[felixkat]

So a repeat element then?

If a user can enter an unlimited number of players

From the 4th and so on

I assume a player element is repeatable?

Or do you have x number of player elements?

 

[dimoss]

Nope...each record represent one player..which means that I have to count on the fly and put the WL sign on all the players after the 4th one.

 

[felixkat]

Riiiiight, now the code makes sense. Sorry to get to the 10th post with only questions but my brain cannot compute unless it knows the full setup.

So I have one last question

If a user has 6 players the 4th, 5th and 6th are at WL status.

If player 2 is deleted then what decides what becomes the new third player out of 4,5 or 6? Is it done in the sequence those players are entered, so in this case it can only be player 4 therefore it's sorted by 'id'?

 

[dimoss]

Yes..the id decides.
If you delete the 2nd then the WL status goes to 5th, 6th which are the last two.

 

[felixkat]

Okay I mocked up some code that I tested and it seems to do what you want to do. You'll have to splice it into your existing code.

$a = '{morethan3___person}';
$id = '{morethan3___id}';

$query = "SELECT id FROM morethan3 WHERE person = '$a'";
$db = & JFactory::getDBO();
$db->setQuery( $query );
$user = $db->loadResultArray();

$keyz = array_search($id, $user);

if  ($keyz > 2)
{
return 'WL';
}
else
{
return '';
}

What this is doing is identifying the user and returning all the record id's belonging to that user into an array, ($user).

It then searches the array and identifies the position of the current record. Arrays start at 0 so position 3 is array position 2.

Then it returns 'WL' if the id is greater than 3 which in your case would be player 4 onwards.

 

[dimoss]

Hi

I implemented your code into mine like this:

$a = (int)'{fab_jbs___tournament_raw}';
$b = '{fab_jbs___assoc_raw}';
$id = (int)'{fab_jbs___id_raw}';

$query1 = "SELECT id FROM fab_jbs WHERE tournament = $a AND assoc = '$b'";
$db = & JFactory::getDBO();
$db->setQuery( $query1 );
$user = $db->loadResultArray();

$query2 = "SELECT organizer FROM fab_tournaments WHERE id = $a";
$db = & JFactory::getDBO();
$db->setQuery( $query2 );
$xx = $db->loadResult();

$keyz = array_search($id, $user);

if (($keyz > 2) && ($xx != $b))
{return 'WL';}
else if (($keyz < 2) && ($xx != $b))
{return '';}
else if ((($keyz > 2) && ($y <= 11)) && ($xx == $b))
{return '';}
else if (($keyz > 11) && ($xx == $b))
{return 'WL';}
else
{return '';}

However it seems that there is something wrong because when I added the players for first time everything was ok. However when I deleted a player and then re-submit it again the code mess it up. See the following pic:

According to the id's the WL sign should be on 50, 51.

When I continued to add one more player I got this:

The WL sign should go to 51, 52

I suspect the problem is on the array because as it seems it can't identify the position of the records on the fly (after a change has been made, delete or add).

Thanks

Dimos

 

[felixkat]

Can you try adding the line

[B][COLOR=Red]sort($user, SORT_NUMERIC); [/COLOR][/B]
$keyz = array_search($id, $user);

 

[dimoss]

Yes!! This did the trick!
Thanks a lot!

Dimos

 

[felixkat]

Great! All that is doing is sorting the id's into order before we process them. I imagine you have some sorting on your list, (orderby), which I didn't originally think about.

Whether you do this real time or not all depends on the site's performance.

 

[dimoss]

Yes, actually I have id sorting ASC in the table properties.

Does the site's performance affects the id ordering and the correct position of the WL sign?
Id it possible to run the process before the is sorting?

Thanks

Dimos

 

[felixkat]

Well any form of calculation, or more to the point additional code, will increase load times.

We don't have any control as to what loads first as that's all a part of Fabrik's core code.

You could probably re-work the code with 'Only Calc on Save' which would create less processing on the load of your list. The only overhead would be when saving or editing a record, but as it will only be processing one user the overhead would be minimal.

The downside to this route is that it wouldn't process any deletions. You could possible use the PHP list plugin to handle your deletions though.....

Before you start 'tweaking' you may just want to see how much impact the calc element is having. If you enable debug you could load the list a few times and work out the average load time, you could then unpublish the calc element and do the same again.

This should give you an idea on how much overhead the calc plugin is creating, although figures will only be approxmiate. If you are on a shared server then the timings will all depend on server load during the point you display your list.

 

[dimoss]

Ok I see ...
Thanks again!