Row Counter

Is there a plugin (or programming trick) to display the current row count in a filtered list?

 

I should have explained better. I didn't mean a total count - I meant an incremental counter.

I have a table that conatains a list of "Steps" taken when filling out a survey. Each step leads to another Fabrik List, Fabrik Form, or Joomla article. That Steps List has a "Step#" field to indicate the order the steps should be performed.

Since many of those steps are common to all surveys I keep them all in one table and use a field in each (related) survey table record indicating what "Steps" to use for that survey. Sometimes a step is not needed.

I don't want the table to list "Steps" as 1, 2, 4, 6, 7 in surveys where Steps 3 and 5 are not needed.

I basically want to abandon the display of the original "Step#" in that filtered list and replace it with a counter to indicate the row# as it displays in the list - NOT the actual Step#. (So in my example the "Step#" column in the list will be shown as 1,2,3,4,5)

There is a way of including an incremental row counter in a mySQL query, but I don't know how to translate that into an element in a Fabrik list.

For example (non-Joomla) query code would be...

mysql_query("set @N = 0;");
$rs = mysql_query("SELECT @N := @N +1 AS step, name, [etc. - other fields listed]");

which would get me the correct step number (as "step") that I want to display.


I tried the count plugin but that didn't seem to work. (It caused only one row to be selected/displayed.)

 

A post by maxinic this morning led me in the right direction - and this is the solution.

Using a calc field labeled "this_count" and this as the calculation...

mysql_query("set @N = if(@N,@N,0);");
$rs = mysql_query("SELECT @N := @N +1 AS this_count;");
$row = mysql_fetch_assoc ( $rs );
return (INT) $row['this_count'];

(I tried using Joomla API syntax for db classes but got nowhere)

I now have my properly numbered "Steps".8)

Thanks maxinix.

 

I don't know - I don't have a survey with more than 10 steps.

Seriously though, you should be able to answer that off the top of your head better than I. Does the initial mysql query for a filtered list get every row of the filtered list or does it use the pagination value to query xx rows at a time? If the answer is the 2nd - then I assume it would be a problem and start with 0 for each page (Or maybe not - depending on how long mysql holds that @N counter value in memory. )

I'm not worried about it now myself - I'll cross that bridge if I ever come to it. (Or maybe I'll take some time over the weekend to test it for you.)

You must be thinking about the group counter bug I mentioned in another thread?

 

You could always "pre-query" the entire list without pagination limits and store the counter values paired with each internal id and a unique identifier id for the query (like session name?) in a separate table - similar to what you do for multiselect elements. Then use a LEFT JOIN query to that table to get the counter values. (You could use this method for the group count bug too?) This would involve a few more queries, but it would solve the pagination problem.

Thinking this out a little further last night - rather than creating individual tables for each mapped element or table (like you're doing for multiselect dropdowns) maybe you could put all these types of mappings in one master table? ? e.g. "#__fabrik_mappings"?

Say for example, if in a table named "#__fb_steps", you wanted to use this new ?rowcount? plugin. The plugin creates a read-only element (let?s call it "this_count") similar to what I did in my example that used the calc element type. But instead it links to the mappings table to get the row counter.

I went ahead and created a mapping table, wrote some php code, and tested this out - and it seems to work. My orignal idea was to use a combination of userid/listid to get unique mapping records - but that would eliminate using it on any lists viewable by Guests. So I went with session name. (Though I'm not sure how reliable and unique that will be if mappings created in old sessions aren't purged regularly.)

The prerequisite is a table named "#__fabrik_mappings" with the following structure...

# Column Type Extra
1 id int(11) AUTO_INCREMENT
2 session_name varchar(32)
3 original_id int(11)
4 mapped_value int(11)
5 list_id int(11)

I have yet to write a Joomla plugin - what's more a Fabrik plugin (and doubt I ever will). But this code is an example of how you could create the mapped records.

As is, it is hard-coded to be used on a table named "#__fb_steps". If you want to test it out, try including it as code in the php plugin. Once you create a "#__fabrik_mappings" table you can substitute any table you want for all occurrences of "#__fb_steps" in this code...

<?php
$db =& JFactory::getDBO();
$sessionstamp = session_name();
$listid = JRequest::getVar('listid');
// First check to see if the mapping has been run on this list for this user session as it only needs to be done once.
$myQuery = "SELECT session_name from #__fabrik_mappings WHERE session_name='".$sessionstamp."' AND list_id=".$listid;
$db->setQuery($myQuery);
$row = $db->loadResult();
if (empty($row)){
// Get mapping fields by running a query that includes the filters but disregard the pagination settings -
// add the session name as "session_name", counter value as "this_count", and list ID as "list_id".
$myQuery = "SET @this_count = 0, @sstamp = '".$sessionstamp."', @listid = ".$listid;
$db->setQuery($myQuery);
$db->query();
$myQuery1 = "SELECT id as original_id, @sstamp := @sstamp as session_name, @listid := @listid as list_id, @this_count := @this_count + 1 as mapped_value FROM #__fb_steps";
$myQuery = "INSERT into #__fabrik_mappings (original_id,session_name,list_id,mapped_value) ".$myQuery1;
$db->setQuery($myQuery);
$db->query();
}
?>

The important thing I left out here is that the query for "$myQuery1" in the code above should include the filters and order of the list (but not the Limit).

I just used Joomla API standards for handling the mySQL db tables. I tested this - it works. Now all you need to do is create the plugin. "All you need..." - hehe.

I thought about inserting this somewhere in the new "php_events" plugin on the list. But that plugin is basically just there. I, nor few others, have any idea how to use it. Maybe you guys will get around to some documentation for it soon because it sounds like a very valuable plugin.

I'm not sure how you'd get the original list query to use the query below (with the added JOIN) - but the code below would produce the counts using the mapping table. I had it after the conditional "if" in the code above to verify the mappings were done correctly and it seems to do what I want.

// Verify Mappings Table
// $myQuery = "SELECT #__fb_steps.*, #__fabrik_mappings.mapped_value FROM #__fb_steps LEFT JOIN #__fabrik_mappings ON #__fabrik_mappings.original_id = #__fb_steps.id
// WHERE (#__fabrik_mappings.session_name ='".$sessionstamp."' AND #__fabrik_mappings.list_id =".$listid.") LIMIT 10";
// $db->setQuery($myQuery);
// $mappings = $db->loadAssocList();
// var_dump($mappings);
// exit;

Another thing I thought of was that the old session mappings need to be wiped out and that code re-run every time the list order is changed.

Then of course there is the issue of purging the mappings table of old sessions.

I'm sure you've thought of a few ideas like this yourself. Just thought I'd pass this one by you.

 

I said the query would have to be re-run every time the order was changed (that includes if filters change).

OK - So as usual I'm just making things more difficult than they need to be. So do it that way. All I'm looking for is a read-only "counter" element that is treated as not part of the table, yet can be displayed in both the list and forms.

So your solution sounds like it would work. Instead of initializing the counter at 0 ("set @N = if(@N,@N,0);" - in my first example) change that to "set @N = if(@N,@N,".($page_number-1)*$page_limit.");"

That would initialize the counter at one less than the actual starting count number - and you would just need to replace the beginning "SELECT " of the final filtered query with "SELECT @N := @N +1 AS display_count, " to increment the counter and display the proper count as "display_count".

The only problem I see is Fabrik having to recognize this "element" as a counter and ignore it when updating the table from the form data - but that's probably the same thing you would do with any "read-only" field, no?

ps. I still like the idea of a mappings table. The idea of creating a new table for every mutiselect element is adding dozens of (IMO unnecessary) new tables to my database. Why not have just ONE table holding all mappings - with those same fields PLUS the "element name" as another field (e.g. - "fb_surveys_repeat_access_level") - then just querry the mappings table on that element name to get the mappings?

 

Whatever. If you were consistent in your argument then you?d agree that the Elements table is poor design. There should instead be individual tables for each Element Group. No?

There is nothing wrong with using a mix of normalized and denormalized tables.

You asked me to ?read up? ? so I did. Here?s a few reads that you might find interesting?

Should I Normalize My Database?
Sometimes It's OK To Denormalize!

Databases: Normalization or Denormalization. Which is the better technique?

As the last paragraph from the ?Final Words? section in this article states?

I?d say that a mappings table (just like the elements table) would be ?read-intensive?.

And if you?re worried about redundancy ? there is already redundancy in these ?repeat? tables that are being created. So what?s wrong with just storing the array values from any multiselect element in a form as a serialized or json_encoded string in a single (text) field that takes but a single record in a table? I?m just saying that I?ve never seen anyone do it this way. Usually serialize and unserialize ? or more recently json_encode() and json_decode() - is used to store multi-select values in a table (no need for a linked table). It sounds like you?ve made more work for yourself than needed.

 

I?m sure there are many historical (or ?hysterical? as we used to joke at the software company I worked for) reasons for a lot of the things I question. I?m just an inquisitive guy ? always asking ?Why?? I don?t suppose I should expect you to explain everything I question ? or the history behind it. I understand there are many decisions made when developing any software that might seem ?out of the norm? to the outsider. So I?ll try to be less ?inquisitive? before I have you guys any more pissed off at me than you already are. I suppose that if and when I finally do subscribe to your services, I will be doing that under a totally different name, alias, IP, and email address.

 

(...and the calculation would change the "this_count" row numbering with every permutation of filtering! It's meant to be a read-only field and the row values will get recalculated on every page refresh. Isn't that the whole idea of the calc field?)

Here you go buddy - now find fault....

Here?s a fix to that ?counter? calculation that takes care of pagination. (Hoping to make Hugh happy.)

$posta = JRequest::get( 'post' );
$list_id = $posta['listid'];
$start = (INT) $posta['limitstart'.$list_id];
$db =& JFactory::getDBO();
$myQuery = "SET @this_count = if(@this_count,@this_count,".$start.")";
$db->setQuery($myQuery);
$db->query();
$myQuery = "SELECT @this_count:=@this_count+1";
$db->setQuery($myQuery);
$kount=$db->loadResult();
return $kount;

Throw that code as the "Calculation" in a calc field in any List and publish it as the 1st column in the List - then let me know if you have any trouble with the numbers ordering correctly - regardless the filters or pagination.

BTW - Changing Grouping (at least on the Admin backend) seems to always throw me back to the default Administrator page. And if it works - it doesn't first reset the pagination. Then if you change the page - it returns to the default grouping.

PS. Did I crash the mySQL server by posting that code? I seemed to have lost connection for 15 minutes or so just as I posted it. Sorry.