bernhard

Preview/Discussion: RockDataTables

Recommended Posts

30 minutes ago, bernhard said:

Why not? Or what exactly?

Because if you need foolproof, there is the Processwire API.

This is about speed and foolproof <> speed! ;)

I think it is sometimes enough to show the way (As you and adrian did here). Others can then change the code to fit their requirements.

If you implement 1000 options, then I'm sure someone will need option number 1001. ;)

 

30 minutes ago, bernhard said:

Jep, can imagine that it shines here :)

Yes, and with the fast multi level navigation above. :)

Thank you.

Share this post


Link to post
Share on other sites
1 minute ago, theo said:

Because if you need foolproof, there is the Processwire API.

This is about speed and foolproof <> speed! ;)

I think it is sometimes enough to show the way (As you and adrian did here). Others can then change the code to fit their requirements.

Yep, that's not the intention. I just asked to see if I missed any culprits.

2 minutes ago, theo said:

Yes, and with the fast multi level navigation above. :)

Now I'm offtopic: You know about PW's caching tools? https://processwire.com/api/ref/wire-cache/

Share this post


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

Now I'm offtopic: You know about PW's caching tools? https://processwire.com/api/ref/wire-cache/

Thank you.

I have never used it, but I think the best idea is, to be efficient on all levels.

Create the tree fast using findArray, maybe even load parts via Ajax, and/or use caching.

If creation is as fast as shown in the example (794 Titles in 0.021 seconds ), Ajax and caching are probably not necessary.

  • Like 1

Share this post


Link to post
Share on other sites

Now i've had a hard time with strange results until I found out that MySQL WHERE IN () does not necessarily retain the sort order of the ids you pass it.

You have to pass the ids again in an ORDER BY statement.

        $pagesset = implode(",", $pages);
        $sql .= "\nFROM\n  pages AS p";
        $sql .= "\nWHERE\n  p.id IN (" . $pagesset . ")";
        $sql .= "\nORDER BY FIELD(p.id," . $pagesset . ")";
        echo '<pre>' . $sql . '</pre>';
        $results = $this->database->query($sql);
        $event->return = $results->fetchAll($type);

???

  • Like 2

Share this post


Link to post
Share on other sites

made huge progress on this :)

Example query with all the features:

$pages->findObjects('template=item, limit=100', [
    'title', // page title field
    'field1', // regular textfield
    '(SELECT #data# FROM field_field1 WHERE pages_id = pages.id) AS `sql`', // custom sql query (multilanguage)
    'images:description', // imagefield + description
    'pagetest:status:created:templates_id', // pagefield + page status + created time + template id
    'pagetitle' => function($page) {
        return $page->title; // example of a closure
    },
]);

Explanation:

  • regular textfields can easily be queried just by defining their name as string
  • any custom sql query is possible, even multilanguage when using #data# keyword
  • images:description syntax works for all file fields
  • page:status:created works for all pagefields
  • and last but not least a very nice helper for fast and easy queries and small amounts of data: closures :)

Output (default language):

sqldemo.thumb.png.874bc6e69d2d16f03fcfc74749339d3a.png

And the same query as datasource for a datatables field:

datatables.thumb.png.752b7bc3fbffeb52bbf2d102739e52f5.png

This will be really, really awesome :)

@theo I plan to add a lot of other stuff to my datatables module and that will take some time. I put the new pageFinder in a separate module. You can have a look here: https://gitlab.com/baumrock/RockSqlFinder

Maybe you want to test it? Also @adrian might be interested?

Currently it supports FieldtypeText, FieldtypePage and FieldtypeFile - but it should be easy to add others... Like Repeaters for example. Any other fields could be queried via custom SQL queries... And for really complex joins there will be another module :)

  • Like 6
  • Thanks 1

Share this post


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

Maybe you want to test it? Also @adrian might be interested?

Just playing around with it now - so far looks fantastic in implementation. The only catch is that I am actually seeing slower response times. Take these two examples:

image.png.b559610962867229b54f2cde05d3f5f0.png

VS

image.png.321696eb8aab329edd1ad0095baf8dfb.png

Note that I am using the Console panels ability to only run the selected code.

The ms values don't look good though for some reason - any ideas why?

 

 

  • Like 1

Share this post


Link to post
Share on other sites

Thanks adrian, I also noticed that but it seems it was too late yesterday... forgot to check if there are any closures to execute so it loaded all the page objects even if there were no closures :) please check the update: https://gitlab.com/baumrock/RockSqlFinder/blob/master/RockSqlFinder.module.php#L59

5aaa341459318_2018-03-1509_38_21-PagesProcessWiredatatables_to.png.72b45637a7dd8b9c4ff79ad70b74b65f.png

5aaa3416cf089_2018-03-1509_37_51-PagesProcessWiredatatables_to.png.2e0f6f5799d95d5f5530822a799ec93c.png

5aaa34138d540_2018-03-1509_39_07-PagesProcessWiredatatables_to.png.fb1dc6d4876f32f9e2796e3121da99fe.png

Very interesting (and great imho) is also this test using closures:

5aaa36423d6b3_2018-03-1509_59_14-PagesProcessWiredatatables_to.png.453b0c0b9e86c8bde0d2f32b5c4f8fad.png

5aaa36435ef16_2018-03-1510_00_01-PagesProcessWiredatatables_to.png.a9e88ee3695e400ffd60ce34b4f0f5f9.png

So, of course with closures it is a LOT slower (because we load the page objects in memory), but the additional costs for each closure are less than for the first one. Thats because the page is already available in memory after the first closure: https://gitlab.com/baumrock/RockSqlFinder/blob/master/RockSqlFinder.module.php#L62-63

And finally one with "images:description" syntax:

5aaa382c0269b_2018-03-1510_08_24-PagesProcessWiredatatables_to.png.90b16a795e6ac8139bacfac923535d5e.png

And insanely fast without the closure :)

5aaa3a14c3703_2018-03-1510_16_44-PagesProcessWiredatatables_to.png.7726166b2fc3202b09432c8b672e072b.png

 

@all

Any ideas wich character to use for image field descriptions (and maybe other fields)? At the moment I'm using the pipe, but that would lead to problems when a user uses the pipe for descriptions (see the above screenshot)

 

For file- and page fields the pipe should be fine because we could use it directly for further selectors and files should not contain any pipes.

 

  • Like 2

Share this post


Link to post
Share on other sites

I'm wondering what you guys think of caching? I can think of two situations:

  1. A query without closures and without custom SQL
    Here we could do caching based on the selector via adding a $pages->findOne(... sort=-modified) and check if that date has changed. If the date is greater than from the cached one we create a new result, otherwise we can return the cached one. I think it should even be safe to do this by default?!
     
  2. A query with closures or custom SQL
    In this situation the data could also change in a referenced field or in a cell queried by sql. Here we would need to specify our own logic for caching. Maybe we could provide an option to specify a page selector to query for the last changed timestamp? For example when listing all clients and the related invoices, the data would need to change whenever an invoice or a client changed:

    'cache' => "template=client|invoice" (adding sort=-modified automatically)

Alltogether it would be something like this:

$pages->findObjects([
  'selector' => 'template=client',
  'columns' => [
    'title',
    'invoice_sums' => function($page) {
      return implode(',', $page->invoices->each('gross'));
    },
  ],
  'cache' => 'template=client|invoice',
]);

And the output would be something like:

5aaa5654930fc_2018-03-1512_17_24-PagesProcessWiredatatables_to.png.e42da6a3cdb5ed5bbf4afca15c7da624.png

And it would reset whenever a client or an invoice is changed.

Share this post


Link to post
Share on other sites

Hey @bernhard - new version is much better - thanks.

I tested with 5000 pages and it is an order of magnitude faster than a regular find()

image.png.f5f379a43350592707d8e09204bb32a2.png

image.png.8d9cd442b142fe800a14f6747b0b63df.png

  • Like 1

Share this post


Link to post
Share on other sites

Not to forget the memory drop from 20.63MB to 0.15MB ;)

  • Like 1

Share this post


Link to post
Share on other sites
1 minute ago, bernhard said:

Not to forget the memory drop from 20.63MB to 0.15MB ;)

Yeah it really is an incredible improvement when you don't need all the stuff associated with a full PW page object.

  • Like 1

Share this post


Link to post
Share on other sites

@bernhard This works great afaics.

Well done.

I think this is a valuable addition.

I hate speed limits. ;)

  • Like 1

Share this post


Link to post
Share on other sites

Love this thread.

Once you make a PR for the findArray to the PW repo, please let us know, so we can upvote it.

I would definitely be willing to pay if RockDatatables was released as a pro module. 

  • Like 1

Share this post


Link to post
Share on other sites

Wow @bernhard. This looks so good. Can't wait to get my hands on it. Could you give me access to the source so I can play with it?

Really looking forward to this module.

  • Like 1

Share this post


Link to post
Share on other sites

RockSqlFinder is sooooo fast and memory friendly, even when using one page. Really nice.

chrome_2018-03-20_23-40-57.thumb.png.b076033b8087b7d090a34971f352ecc7.png

chrome_2018-03-20_23-41-24.thumb.png.0030c1e8185ce276ce7bd8fd0fd8a59c.png

  • Like 2

Share this post


Link to post
Share on other sites

Offtopic, Tracy praise:

You can use d() to dump and if you select some lines in the console only those lines will be executed. No need for commenting out ;)

  • Like 2

Share this post


Link to post
Share on other sites

Update: Seems like I'll switch to agGrid for my module. 

  • It is also MIT licensed in the basic version.
  • It can handle multiple thousands of rows (loading 30k rows with pages->findObjects needs 6 seconds here; 10k pages = 800ms; without caching, of course)
  • It has very nice filters built in (contains, starts with, not equal, etc)
  • It is easy to create custom filters -> the regex filter is built by myself
  • It has the possibility of custom cell renderers (see the percent bars; though I will have to adopt how this works for my module)
  • It looks cleaner then datatables
  • It has CSV export that seems to work just as good as the excel export of datatables (it is also possible to modify the cell values before export)
  • I managed to rebuild the column statistics functionality (for all rows and only selected rows) that I have built for datatables. This is a must have feature, making it easy to show eg. the sum of all revenues in a specific month.
  • It is pure JavaScript, so no dependencies have to be installed. It should also be possible to use this module on the frontend quite easily.

Only thing that I'm missing from datatables is an easy way to access data. datatables seems to be superior in this regard. But I've also rebuilt this functionality making things like this possible, showing the average value of the grid's "rand" column, taking into account all filters and returning all rows (not only selected ones):

return grid.avg('rand', {filter:true, selected:false});

This statement is used for the stats row at the bottom of the grid.

aggrid.gif.ae803fc7c25d4203e9a4d4296a125c47.gif

 

Todos:

  • Action items for pw-panel editing
  • Anything else that you think is necessary?
  • Like 6

Share this post


Link to post
Share on other sites
24 minutes ago, adrianmak said:

how do i use it on my own module? any example code?

It is not released yet...

Share this post


Link to post
Share on other sites

Is it good to fetch all records from database, and using js/jquery lib for pagination and searching ?

I was come into a situation, a client's existing website (not developed by me), the implementation for tabular data is fetch all records from database and use js/jquery for pagination and searching. The client asked for a modification to include search for some of other fields from records.

Since the search function provided by js lib only search data where columns are populated on the table, other record details not on the columns cannot be search.

And the  whole tabular data page is fetch from ajax request, for this modification it required to rewrite from scratch for this part.

It took too much time to study the code (not using php framework, just vanilla php code) written by others for that modification. And finally I rejected the client's request and asked him to look for someone else for modification

 

 

 

Share this post


Link to post
Share on other sites
4 hours ago, adrianmak said:

Is it good to fetch all records from database, and using js/jquery lib for pagination and searching ?

it's really pretty easy, just get a json array of the data and use DataTables to output the data.. the DataTables documentation is good enough to get you going, should you decide to try it out..

  • Like 1

Share this post


Link to post
Share on other sites

Also take a look at the example of agGrid: https://www.ag-grid.com/javascript-getting-started/

I think it is superior to (and in most cases also easier than) datatables, that's why I'm switching. You just need to grab data via RockSqlFinder (see above) and echo that in your gridOptions definition in the javascript tags.

Share this post


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

I think it is superior to (and in most cases also easier than) datatables, that's why I'm switching.

I'd never heard of this grid, so thanks!  I just had a quick look and it looks very powerful. Btw, I couldn't find info about the differences between the free and paid versions. Do you know what these are? Thanks.

  • Like 1

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.