bernhard

Preview/Discussion: RockDataTables

Recommended Posts

can you post your $pages->findObjects() call please?

Sorry, missed that you already did that.

Quote

in the DBMS it take 0.3908 second(s) for 18119 pages.

You can try dumping the query that is created by my module and throw that to the dbms and analyse it.

As I wrote in the related thread I'm working on another implementation of RockSqlFinder as I came across some limitations yesterday

  • Like 1

Share this post


Link to post
Share on other sites

Okay,

Not sure how to debug the right query in the module, but

The findIDs() in buildQuery(), in the DBMS take 0.3087 second for 18119 pages.

The query ($query) in findObjects(), in the DBMS take 1.9846 seconds for 18119 pages.

 

And, why we have a difference of ~10 seconds when calling findObjects() or even findMany() from Tracy ?

 

Edit 1:  same average with 96611 pages.

Edit 2 :

I stumbled on this thread :

 

If you are interested, I have the code of two hooks which implement COUNT(), SUM() and DATEDIFF().

Share this post


Link to post
Share on other sites
1 hour ago, flydev said:

And, why we have a difference of ~10 seconds when calling findObjects() or even findMany() from Tracy ?

Sorry, no idea. Only situation where I know that it is slow is when it loads pages because of closures.

Do you have the current version? Is this check available in your module? https://gitlab.com/baumrock/RockSqlFinder/commit/17464b0397ed5f3d62bc86177d0c2395f33b5d9e

Quote

If you are interested, I have the code of two hooks which implement COUNT(), SUM() and DATEDIFF().

Thanks, no need at the moment :)

  • Like 1

Share this post


Link to post
Share on other sites

I finally found my mistake - it was an autoload module.

Also another mistake was the selector. I was using the has_parent keyword on a ~360k pages, growing each night. Selecting a direct parent reduced considerably the time of the aggregation.  It take ~18 seconds to do some calculation on 112 689 pages fetched, but it take 60 seconds to build and show two charts from theses 112k pages.

Still looking to improve it before scratching my head into MySQL stocked procedures.

 

Didn't checked the repo, do you have pushed an update regarding the implementation in RockSqlFinder yet ?

 

On 11/04/2018 at 2:23 PM, bernhard said:

Thanks, no need at the moment :)

okay

Share this post


Link to post
Share on other sites
8 minutes ago, flydev said:

I finally found my mistake - it was an autoload module.

Also another mistake was the selector. I was using the has_parent keyword on a ~360k pages, growing each night. Selecting a direct parent reduced considerably the time of the aggregation.  It take ~18 seconds to do some calculation on 112 689 pages fetched, but it take 60 seconds to build and show two charts from theses 112k pages.

Still looking to improve it before scratching my head into MySQL stocked procedures.

thanks for the headsup :) glad you found it!

 

8 minutes ago, flydev said:

Didn't checked the repo, do you have pushed an update regarding the implementation in RockSqlFinder yet ?

I'm right now working on a complete rewrite of the module. The syntax will change a little bit but be a lot more flexible. I'm working on a recursive version that should make it possible to easily query pages that are referenced by pagefields. This is a huge timesaver when working with mysql because this joins can really be a tedious task. This setup should also make it possible to do easy aggregations via custom SQL statements. That's why I think I will not need your aggregation functions for $pages api :)

Hmm... Thinking about it... Maybe this could be helpful in some situations. Maybe you want to share it in the dev board with all of us?

 

I hope I can push an update this week.

  • Like 2

Share this post


Link to post
Share on other sites
17 minutes ago, bernhard said:

Maybe you want to share it in the dev board with all of us?

I Will make a thread 👍

  • Like 1

Share this post


Link to post
Share on other sites

Hey @bernhard any updates on a release date or progress with RockDataTables?

RockFinder repo at gitlab even mentions, that you renamed it to RockGrid?!

I can't wait to get this module into my hands.

  • Like 2

Share this post


Link to post
Share on other sites

hey @jmartsch, not really. I'm fighting with it on several new projects ;) Some changes turn out to be not as simple as I thought (as always). I'll release a version when I finished my projects and did some more testing.

Yes, it will be renamed to "RockGrid" because I changed the grid library from datatables.net to ag-grid.com

Share this post


Link to post
Share on other sites

I'm making progress! Current state is really, really nice. See these two examples of a feedback software that I've built for a client:

demo1.thumb.png.c7035271b11f957146628e0863f9a1e5.png

  • full multi-language-support
  • all kinds of custom cell stylings (backgrounds, icons, etc)
  • custom filters
  • filter by doubleclick on a cell (really handy)
  • custom buttons-plugin (not part of aggrid):
    • reload data via ajax (very performant thanks to RockFinder)
    • reset filters
    • fullscreen mode (really handy for large grids)
    • excel export as CSV data
  • reload grid automatically when a pw-panel is closed

 

Another example: A list of all ratings for several categories

demo2.thumb.png.69b4d01f6c56d82cca97905f90259213.png

See the bottom line: this is another plugin that is not part of aggrid. You can just show the sum of the column (like the second column) or render custom statistics (like min, max, avg). When you select multiple lines you also get the statistics only for the selected rows :) This is also an example how you can use pinned rows with aggrid (really awesome library!).

 

Example of a range filter (aggrid standard feature):

demo3.png.d25f04bd6dd39f469a3a2232b876317f.png

  • Like 7

Share this post


Link to post
Share on other sites

Great work bernhard! I'm really interested in giving this a spin. Also the Enterprise features like pivots, filters, grouping are really great. Would it be possible to integrate these features by a third party without touching the module?

  • Like 1

Share this post


Link to post
Share on other sites

Sorry for the delay @arjen

On 5/10/2018 at 10:01 AM, arjen said:

Also the Enterprise features like pivots, filters, grouping are really great. Would it be possible to integrate these features by a third party without touching the module?

Don't think there should be any restrictions. My module should not break any functionality that comes with aggrid. Maybe some of my additional tools might need to be adopted to also support enterprise features, I don't know, but the module will be extendible via plugins so you can do whatever you want with it.

Another feature that I've implemented is called Batcher. This makes it easy to create batch processes sent via AJAX and execute custom actions on the server. An action can be as simple as that:

$this->ajax('trash', function($data) {
  foreach($data as $id) {
    $p = pages($id);
    if(!$p->trashable()) continue;
    $this->pages->trash($p);
  }
});

And the GUI:

var Batcher = RockGrid.batcher;
Batcher.items = grid.pluck('id', {selected:true});
Batcher.batchSize = 2;
Batcher.action = function(items) {
  // send ajax request to this grid
  var ajax = grid.ajax({
    action: 'trash',
    data: items,
  }).done(function(params) {
    Batcher.nextBatch();
  });
};
Batcher.confirmStart({
  msg: 'Are you sure you want to delete the selected rows?',
  onYes: function() {
    Batcher.nextBatch();
  },
  onNo: function() {
    $(grid.getWrapperDOM()).find('.rockgridbutton.trash i').removeClass('fa-spin fa-spinner').addClass('fa-trash');
    Batcher.abort();
  }
});
Batcher.onStart = function() {
  $(grid.getWrapperDOM()).find('.rockgridbutton.trash i').removeClass('fa-trash').addClass('fa-spin fa-spinner');
};
Batcher.onEnd = function() {
  $(grid.getWrapperDOM()).find('.rockgridbutton.refresh').click();
  $(grid.getWrapperDOM()).find('.rockgridbutton.trash i').removeClass('fa-spin fa-spinner').addClass('fa-trash');
  setTimeout(function() { Batcher.vex.close(); }, 500);
};

batcher.gif.af5333a0f1fbf2eb49131f0c3612bc60.gif

😎

Edit: The screencast does not really delete mails and it's a lot slower than real-life, because the ->trash() operation is replaced by a sleep(1) 😉

Share this post


Link to post
Share on other sites
15 hours ago, bernhard said:

Sorry for the delay @arjen

On 5/10/2018 at 10:01 AM, arjen said:

No problem, thanks for responding.

15 hours ago, bernhard said:

the module will be extendible via plugins so you can do whatever you want with it

Super sweet. You've created something really powerful.

15 hours ago, bernhard said:

Another feature that I've implemented is called Batcher.

Great feature!

On a particular project we use ListerPro a lot, but working with lots of data (10k+ pages with 30 fields represented in columns) in ListerPro is becoming more and more tedious. Response times are sometimes slowing down. Especially since there are sometimes hundreds of concurrent users. I am thinking to use your DataTables and Rockfinder since it feels a lot snappier than ListerPro.

Share this post


Link to post
Share on other sites
2 hours ago, arjen said:

On a particular project we use ListerPro a lot, but working with lots of data (10k+ pages with 30 fields represented in columns) in ListerPro is becoming more and more tedious. Response times are sometimes slowing down. Especially since there are sometimes hundreds of concurrent users. I am thinking to use your DataTables and Rockfinder since it feels a lot snappier than ListerPro.

Not sure about this on such large numbers. 10k pages per se should be easy. But with hundreds of users..?! Would need a test-run for sure. Maybe caching would be necessary or at least helpful. Are you interested in a closed beta test run? -> pm

  • Like 1

Share this post


Link to post
Share on other sites

Here is a really nice example of a simple plugin. You can develop custom plugins and just place them in /site/assets/RockGrid/plugins or we can share them in the community. Look at that lovely UI for the user and how quickly he can get the information he wants in a very attractive way:

Doubleclick = filter this row with the selected value
Tripleclick = reset filter for this row (but leave all other columns untouched)

filterdemo.thumb.gif.cf9aa8e7dc0a175f7003334b68eb1eaf.gif

document.addEventListener('RockGridItemLoadPlugins', function(e) {
  RockGrid.getGrid(e.target).registerPlugin(function() {
    this.name = 'doubleClickFilter';
  
    this.onLoad = function() {
      var grid = this.grid;
      
      var clicks = 0;
      var timer, timeout = 350; // time between each click

      var doubleClick = function(e) {
        var colId = e.column.colId;
        var filter = grid.api().getFilterInstance(colId);
        filter.setModel({
          type: 'equals',
          filter: e.value,
        });
        grid.api().onFilterChanged();
        grid.api().deselectAll();
      }

      var tripleClick = function(e) {
        var colId = e.column.colId;
        var filter = grid.api().getFilterInstance(colId);
        filter.setModel({});
        grid.api().onFilterChanged();
        grid.api().deselectAll();
      }

      // click timer
      grid.api().addEventListener('cellClicked', function(e) {
        clearTimeout(timer);
        clicks++;
        var evt = e;
        timer = setTimeout(function() {
          if(clicks==2) doubleClick(evt);
          if(clicks==3) tripleClick(evt);
          clicks = 0;
        }, timeout);
      });
    }
  });
});

Not that complicated, right? But huge result 🙂 

PS: Do you see the click on the reload button? Loading > 900 rows of data with RockFinder in a breeze 🙂 

  • Like 4

Share this post


Link to post
Share on other sites

It's getting better and better. Love it,

  • Like 1

Share this post


Link to post
Share on other sites

refreshtimer.gif.2f1034c05e15678224bc3642e1e66fea.gif

  grid.enablePlugin('refreshTimer');
  grid.pluginSettings('refreshTimer', {timeout: 2});

😎

  • Thanks 1

Share this post


Link to post
Share on other sites

When does the public beta come out and we can give it a try?? Waiting for it eager ly.

Gideon

  • Like 1

Share this post


Link to post
Share on other sites
8 hours ago, Gideon So said:

When does the public beta come out and we can give it a try?? Waiting for it eager ly.

Gideon

Should have something available within the next month, but can't guarantee that..

  • Like 3
  • Thanks 1

Share this post


Link to post
Share on other sites

Finally I was able to release RockGrid

I decided to release it as MIT, so everybody can contribute and maybe use it for other great additions to ProcessWire. I think it's a great way of listing data and I can think of several situations where it would make the admin UI a lot better than it is now with our existing tools. For example @adrian s batch child editor could maybe benefit from such a tool? I don't know. Let's see what happens...

The module is still alpha and not perfect... But it was also a lot of work and can't provide anything better than that at the moment. If anybody here can do it better: Please feel free to take over 🙂 

Thanks everybody for all the feedback here in this thread! Special thanks to @MrSnoozles for showing me agGrid and making me replace datatables  🙂 

 

 

  • Like 7
  • Thanks 2

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By thomasaull
      Some time ago I created a site profile for creation of a REST API with ProcessWire. Since I kept struggeling with updating stuff between different projects which use this, I decided to convert it into a module. It is now ready for testing: https://github.com/thomasaull/RestApi
      Additionally I added a few small features:
      automatic creation of JWT Secret at module install routes can be flagged as auth: false, which makes them publicly accessible even though JWT Auth is activated in module settings To check things out, download and install the module and check the folder /site/api for examples.
      If you find any bugs or can think of improvements, please let me know!
    • By kongondo
      Sites Manager
       
      16 September 2018:
      FOR NOW, PLEASE DO NOT USE THIS MODULE IN A PRODUCTION SITE. A  RECENT ProcessWire UPDATE HAS BROKEN THE MODULE. I AM WORKING ON A FIX.
       
      ################
       
      Sites Manager is a module for ProcessWire that allows Superusers to easily create/install ProcessWire sites on the same serverspace the module is running in. Only Superusers can use the module. You can create both stand-alone and multi-sites.
       
      Single/Stand-alone Sites
      Stand-alone or single-sites are sites that will run in their own document root/directory with their own wire and site folders, .htaccess, index.php, etc. In other words, a normal ProcessWire site.
      Multiple Sites
      Multi-sites are sites that will run off one wire folder (shared amongst two or more sites) each having their own site folder and database. In this regard, it is important to note that Sites Manager is not in itself a multiple sites solution! Rather, it is a utility that helps you create multi-sites to be run using the ProcessWire core multiple sites feature. For more on this core feature, see the official ProcessWire documentation, specifically the solution referred to as Option #1.
      Option #1 approach requires the site admin to initially install ProcessWire in a temporary directory for each new site. The directory then needs to be renamed as site-xxx, where ‘xxx’ is any name you want to use to differentiate the installation from other sites, before it is moved to the webroot. For instance, site-mysite, site-another, site-whatever. In addition, the /wire/index.config.php file must be copied/moved to the webroot. Each time a site is added, the index.config.php has to be edited to add ‘domain’ => ‘site-directory’ key=>value pairs for the site. This process can become a bit tedious. This module aims to automate the whole multi-site site creation process.
      The module is based off the official ProcessWire installer. Creating a site is as simple as completing and submitting a single form! You also have the option to type and paste values or reuse a pre-defined install configuration.
       
      The module will:
      Install a ProcessWire site in your named directory, applying chmod values as specified
      Move the directory to your webroot
      Update/Create a Superuser account as per the submitted form, including setting the desired admin theme and colour
      For multi sites, update sites.json (used by index.config.php to get array of installed sites)
       
      For multi sites, the only difference in relation to the core multi-sites index.config.php is that this file is slightly different from the one that ships with ProcessWire.
      Download from GitHub: Sites Manager (Beta Release)
      Features
      Install unlimited number of sites in one (multi-sites) or independent (single-site) ProcessWire installs. Install by completing a Form, Typing or pasting in configurations or using pre-created install configurations. Choose an Admin Theme to auto-install along with the site installation. For single-sites installation, download, save and reuse ProcessWire versions of your choice. Install and maintain site profiles for reuse to create other sites. Create install configurations to speed up installation tasks. Client and server-side validation of site creation values. Edit uploaded profiles (e.g., replace profile file). Lock installed sites, configurations and profiles to prevent editing. Bulk delete items such as site profiles, installed site directories and/or databases (confirmation required for latter two). View important site details (admin login, chmod, etc). Links to installed sites home and admin pages. Timezones auto-complete/-suggest. Pre-requisites, Installation & Usage
      Please see the documentation.
      Technicalities/Issues
      Only Superusers can use the module.
      ProcessWire 2.7 - 3.x compatible
      Currently using ProcessWire 2.7 installer (install.php)
      For multi-sites, potential race condition when sites.json is being updated on a new site install vs. index.config.php accessing the json file?
      Not tested with sub-directory installs (for instance localhost/pw/my-site-here/)
      Currently not doing the extra/experimental database stuff (database charset and engine)
      Future Possibilities
      Install specified modules along with the ProcessWire install
      Profile previews?
      Credits
      @ryan: for the ProcessWire installer
      @abdus: for the index.config.php reading from JSON idea
      @swampmusic: for the challenge
      Video Demo
      Demo  showing how quick module works on a remote server [YMMV!]. Video shows downloading and processing two versions of ProcessWire (~takes 7 seconds) and installing a single/stand-alone ProcessWire 3 site using the new Admin Theme UI Kit (~2 seconds) on a remote server.
       
      Screens
      1

      2

       
       
    • By Robin S
      Breadcrumb Dropdowns
      Adds dropdown menus of page edit links to the breadcrumbs in Page Edit.

      Installation
      Install the Breadcrumb Dropdowns module. The module requires ProcessWire >= v3.0.83 and AdminThemeUikit.
      There is a checkbox option in the module config that determines if the breadcrumb dropdowns will include pages that the user does not have permission to edit.
      Features/details
      The module adds an additional breadcrumb item at the end for the currently edited page. That's because I think it's more intuitive for the dropdown under each breadcrumb item to show the item's sibling pages rather than the item's child pages. In the dropdown menus the current page and the current page's parents are highlighted in a crimson colour to make it easier to quickly locate them in case you want to edit the next or previous sibling page. Unpublished and hidden pages are indicated in the dropdowns with similar styling to that used in Page List. If the option to include uneditable pages is selected then those pages are indicated by italics with a reduced text opacity and the "not-allowed" cursor is shown on hover. There is a limit of 25 pages per dropdown for performance reasons and to avoid the dropdown becoming unwieldy. If the current user is allowed to add new pages under the parent page an "Add New" link is shown at the bottom of the breadcrumb dropdown. If the currently edited page has children or the user may add children, a caret at the end of the breadcrumbs reveals a dropdown of up to the first 25 children and/or an "Add New" link. Overriding the listed siblings for a page
      If you want to override the siblings that are listed in the dropdowns you can hook the BreadcrumbDropdowns::getSiblingsmethod and change the returned PageArray. For most use cases this won't be necessary.
      Incompatibilities
      This module replaces the AdminThemeUikit::renderBreadcrumbs method so will potentially be incompatible with other modules that hook the same method.
       
      https://modules.processwire.com/modules/breadcrumb-dropdowns/
      https://github.com/Toutouwai/BreadcrumbDropdowns
    • By bernhard
      Some of you might have followed the development of this module here: https://processwire.com/talk/topic/15524-previewdiscussion-rockdatatables/ . It is the successor of "RockDataTables" and requires RockFinder to get the data for the grid easily and efficiently. It uses the open source part of agGrid for grid rendering.
       
      WHY?
      ProcessWire is awesome for creating all kinds of custom backend applications, but where it is not so awesome in my opinion is when it comes to listing this data. Of course we have the built in page lister and we have ListerPro, but none of that solutions is capable of properly displaying large amounts of data, for example lists of revenues, aggregations, quick and easy sorts by the user, instant filter and those kind of features. RockGrid to the rescue 😉 
       
      Features/Highlights:
      100k+ rows Instant (client side) filter, search, sort (different sort based on data type, eg "lower/greater than" for numbers, "contains" for strings) extendable via plugins (available plugins at the moment: fullscreen, csv export, reload, batch-processing of data, column sum/statistics, row selection) all the agGrid features (cell renderers, cell styling, pagination, column grouping etc) vanilla javascript, backend and frontend support (though not all plugins are working on the frontend yet and I don't plan to support it as long as I don't need it myself)  
      Limitations:
      While there is an option to retrieve data via AJAX the actual processing of the grid (displaying, filtering, sorting) is done on the client side, meaning that you can get into troubles when handling really large datasets of several thousands of rows. agGrid should be one of the most performant grid options in the world (see the official example page with a 100k row example) and does a lot to prevent problems (such as virtual row rendering), but you should always have this limitation in mind as this is a major difference to the available lister options that do not have this limitation.
      Currently it only supports AdminThemeUikit and I don't plan to support any other admin theme.
       
      Download: https://gitlab.com/baumrock/FieldtypeRockGrid
      Installation: https://gitlab.com/baumrock/RockGrid/wikis/Installation
      Quikckstart: https://gitlab.com/baumrock/RockGrid/wikis/quickstart
      Further instructions: https://gitlab.com/baumrock/RockGrid/wikis/quickstart#further-instructions
      Changelog: https://gitlab.com/baumrock/FieldtypeRockGrid/raw/master/changelog.md
       
      Module status: alpha, License: MIT
      Note that every installation and uninstallation sends an anonymous google analytics event to my google analytics account. If you don't want that feel free to remove the appropriate lines of code before installation/uninstallation.
       
      Contribute:
      You can contribute to the development of this and other modules or just say thank you by
      testing, reporting issues and making PRs at gitlab liking this post buying me a drink: paypal.me/baumrock/5 liking my facebook page: facebook.com/baumrock hiring me for pw work: baumrock.com  
      Support: Please note that this module might not be as easy and plug&play as many other modules. It needs a good understanding of agGrid (and JavaScript in general) and it likely needs some looks into the code to get all the options. Please understand that I can not provide free support for every request here in the forum. I try to answer all questions that might also help others or that might improve the module but for individual requests I offer paid support for 60€ per hour (excl vat).
       
      Use Cases / Examples:
      Colored grid cells, Icons, Links etc. The Grid also has a "batcher" feature built in that helps communicating with the server via AJAX and managing resource intensive tasks in batches:

      Filters, PW panel links and instant reload on panel close:

      You can combine the grid with a chart library like I did with the (outdated) RockDataTables module:

    • By Ken Muldrew
      I'm trying to get a short routine to run once per day that will look at some pages and send a reminder email when that customer's subscription (yearly) is about to expire. When I run the code in a template then it works without issue, but inside my lazycron service routine, I get an "Error: Uncaught Error: Call to a member function get() on null" as if the database cannot be found. My autoload module is just the sample HelloWorld module included with ProcessWire, editted to perform this task. The whole of it is included below (I've stripped out the code that generates the email because it never gets past $pages->find):
       
      <?php namespace ProcessWire;
      /**
       * ProcessWire 'LazyCronLoad'  module
       *
       */
      class LazyCronLoad extends WireData implements Module {
          public static function getModuleInfo() {
              return array(
                  'title' => 'LazyCronLoad', 
                  'version' => 1, 
                  'summary' => 'Just loads a lazy cron callback.',
                  'singular' => true, 
                  'autoload' => true, 
                  );
          }
          public function init() {
              // initialize the hook in the AutoLoad module
              $this->addHook('LazyCron::everyDay', $this, 'myHook');
              
          }
          public function myHook(HookEvent $e) {
              // called once per day
              wire('log')->save('user_activities',' lazy cron service routine');
                  $transport_pages = $pages->find("template=aggregate-entry, aggregate_type.title='Transport'");
                  foreach ($transport_pages as $page) {
                      if (($page->purchase_date + 30325800 < time()) && ($page->purchase_date + 30412600 > time())) { // between 351 and 352 days
                          wire('log')->save('user_activities', $page->id . ' email reminder sent');
                          // send email
                  }
              }
          }        
          
      }
      The first wire('log') shows up but the second one doesn't (the purchase_date condition is met (as demonstrated by running the code in a template close in time to when the lazycron routine executes)). The error log gives the Uncaught Error shown above. 
      I think this is a beginner's mistake with something obvious being missed and would be grateful for any assistance in fixing it.