1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice

Row Counter

Discussion in 'Fabrik 3.x Testing' started by Bauer, Jun 22, 2012.

  1. Bauer

    Bauer Well-Known Member

    Level: Community
    Is there a plugin (or programming trick) to display the current row count in a filtered list?
  2. troester

    troester Well-Known Member Staff Member

    Level: Community
    List option "Show total" (Navigation group)?
  3. Bauer

    Bauer Well-Known Member

    Level: Community
    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.)
  4. troester

    troester Well-Known Member Staff Member

    Level: Community
    One way to do it (you won't be excited;))
    a custom template:
    just add one column displaying your counter in default_row.php (and in default_headings.php to keep the table header aligned)

    or a calc element (I'm not sure what to put in)

    or (always a possibility if you want to do complex MySQL statements):
    create a MySQL view including your count, than link a fabrik list to this view (you need to define the list's PK and set access levels so it's readonly)
  5. Bauer

    Bauer Well-Known Member

    Level: Community
    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.
  6. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    Out of interest, does this work if you paginate? So going from page 1 to page 2 of a list, does it restart the count at 1 on page 2?

    -- hugh
  7. Bauer

    Bauer Well-Known Member

    Level: Community
    I don't know - I don't have a survey with more than 10 steps.:p

    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.:confused: )

    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?;)
  8. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    That's why I asked, as we use MySQL's LIMIT clause. So I strongly suspect that if you paginate, that count will start over at 1.

    Which is why troester suggested the custom template or MySQL View approach, as we've run in to these issues before. Even using a custom template it's tricky, as you have to track the limit vars being posted with the pagination requests, and factor those in to your row counting.

    Running row counters are notoriously tricky to achieve at the database level, when paginating with a LIMIT. I usually achieve them with MySQL views, although even that can add some almighty overhead if your table has a lot of rows.

    It's one of those things I've considered writing an element plugin to handle, as we get this request on a semi-regular basis, which would have no presence on forms, but would display the simple row count / display order on Lists. But as usual, it's a case of finding the time to do it.

    -- hugh
  9. Bauer

    Bauer Well-Known Member

    Level: Community
    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...

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

    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.
  10. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    Not really a workable solution, given that the row numbering would change with every permutation of filtering.

    The element I had in mind would simply factor in the current limit arg (essentially the list display's "page number"). So instead of starting at 1 each page, it would add "page num * rows per page".

    -- hugh
  11. Bauer

    Bauer Well-Known Member

    Level: Community
    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?
  12. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    Well, if you need it to show in form view, you'll have to write it yourself. As I said, the element I have in mind:

    Note the bold text. On the basis that a row ranking which is dependent on on-the-fly changes to list ordering and filtering, on a per-user basis, makes no sense to display in a form, where there is no "list" context to go by. If you need to represent that row numbering in a form based on the last ordering selection by that user (or based on a session ID for guests), that's something different.

    It would have to be re-run every time you added or changed a pre-filter on the List or a Menu link, or changed the ordering in the List settings, every time a filter is added on the query string or a content plugin, every time the ordering was changed on the front end, every time any element filters were changed, every time data was added / removed or edited, and would have to be maintained on a per-user / guest session ID basis. Which would require building a very complex unique hash that takes all those things in to account, and would require an extra database lookup on every list access, to calculate and lookup the hash, garbage collect and rewrite the state table.

    Because that would be the wrong way to do it, for oh ... I can't even begin to count the reasons. Read up on database normalization and indexing.

    -- hugh
  13. Bauer

    Bauer Well-Known Member

    Level: Community
    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.
  14. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    Both of your assumptions are incorrect, and comparison with the elements metadata table is apples and oranges, not a valid comparison. But I'm just too tired to discuss the gory details with you right now.

    If you'd like to fork the project and rewrite it from the bottom up, please feel free. I suspect that's the only way you'd find out why we've built things the way we have.

    -- hugh
  15. Bauer

    Bauer Well-Known Member

    Level: Community
    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. :D
  16. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    Nobody minds inquisitive. But when it's combined with a terrier like tenacity, and a Silver Back level of aggression, it can get a little tiring. It'd be nice if occasionally you could accept my "that's not the way to do it" answers at face value, bearing in mind that between Rob and I we have almost a decade and a half of working on this code, and in that time have come to know MySQL pretty damn well, when it comes to wringing the best performance, felxibility and efficiency out of it, whilst retaining the ability to build even the most complex "application" from the ground up on every page load from first principles and a set of metadata.

    Yes, there is always room for improvement, and yes, there will always be bugs. But when it comes to fundamental design issues, like why repeat elements use auto-created one-to-many joins ... you can safely assume we've put a lot of thought into why we do things the way we do. And that I may not always have the time or energy to explain all the gory details.

    -- hugh
  17. Bauer

    Bauer Well-Known Member

    Level: Community
    (...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.")";
    $myQuery = "SELECT @this_count:=@this_count+1";
    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.
    tagger and mudshark79 like this.

Share This Page