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 bernhard
      WHY?
      This module was built to fill the gap between simple $pages->find() operations and complex SQL queries.
      The problem with $pages->find() is that it loads all pages into memory and that can be a problem when querying multiple thousands of pages. Even $pages->findMany() loads all pages into memory and therefore is a lot slower than regular SQL.
      The problem with SQL on the other hand is, that the queries are quite complex to build. All fields are separate tables, some repeatable fields use multiple rows for their content that belong to only one single page, you always need to check for the page status (which is not necessary on regular find() operations and therefore nobody is used to that).
      In short: It is far too much work to efficiently and easily get an array of data based on PW pages and fields and I need that a lot for my RockGrid module to build all kinds of tabular data.

      Basic Usage

       
      Docs & Download
      https://modules.processwire.com/modules/rock-finder/
      https://gitlab.com/baumrock/RockFinder/tree/master
       
      Changelog
      180516 change sql query method, bump version to 1.0.0 180515 multilang bugfix 180513 beta release <180513 preview/discussion took place here: https://processwire.com/talk/topic/18983-rocksqlfinder-highly-efficient-and-flexible-sql-finder-module/
    • By daniels
      This is a lightweight alternative to other newsletter & newsletter-subscription modules.
      You can find the Module in the Modules directory and on Github
      It can subscribe, update, unsubscribe & delete a user in a list in Mailchimp with MailChimp API 3.0. It does not provide any forms or validation, so you can feel free to use your own. To protect your users, it does not save any user data in logs or sends them to an admin.
      This module fits your needs if you...
      ...use Mailchimp as your newsletter / email-automation tool ...want to let users subscribe to your newsletter on your website ...want to use your own form, validation and messages (with or without the wire forms) ...don't want any personal user data saved in any way in your ProcessWire environment (cf. EU data regulation terms) ...like to subscribe, update, unsubscribe or delete users to/from different lists ...like the Mailchimp UI for creating / sending / reviewing email campaigns *I have only tested it with PHP 7.x so far, so use on owners risk
      EDIT:
      I've updated the module to 0.0.3. I removed the instructions from this forum, so I don't have to maintain it on multiple places. Just checkout the readme on github 🙂
      If you have questions or like to contribute, just post a reply or create an issue or pr on github. 
    • By blynx
      Hej,
      A module which helps including Photoswipe and brings some modules for rendering gallery markup. Feedback highly appreciated
      (Also pull requests are appreciated 😉 - have a new Job now and don't work a lot with ProcessWire anymore, yet, feel free to contact me here or on GitHub, Im'm still "online"!)

      Modules directory: http://modules.processwire.com/modules/markup-processwire-photoswipe
      .zip download: https://github.com/blynx/MarkupProcesswirePhotoswipe/archive/master.zip
      You can add a photoswipe enabled thumbnail gallery / lightbox to your site like this. Just pass an image field to the renderGallery method:
      <?php $pwpswp = $modules->get('Pwpswp'); echo $pwpswp->renderGallery($page->nicePictures); Options are provided like so:
      <?php $galleryOptions = [ 'imageResizerOptions' => [ 'size' => '500x500' 'quality' => 70, 'upscaling' => false, 'cropping' => false ], 'loresResizerOptions' => [ 'size' => '500x500' 'quality' => 20, 'upscaling' => false, 'cropping' => false ], 'pswpOptions' => (object) [ 'shareEl' => false, 'indexIndicatorSep' => ' von ', 'closeOnScroll' => false ] ]; echo $pswp->renderGallery($page->images, $galleryOptions); More info about all that is in the readme: https://github.com/blynx/MarkupProcesswirePhotoswipe
      What do you think? Any ideas, bugs, critique, requests?
      cheers
      Steffen
    • By flydev
      OAuth2Login for ProcessWire
      A Module which give you ability to login an existing user using your favorite thrid-party OAuth2 provider (i.e. Facebook, GitHub, Google, LinkedIn, etc.)..
      You can login from the backend to the backend directly or render a form on the frontend and redirect the user to a choosen page.
      Built on top of ThePhpLeague OAuth2-Client lib.
      Registration is not handled by this module but planned.
       
      Howto Install
      Install the module following this procedure:
       - http://modules.processwire.com/modules/oauth2-login/
       - https://github.com/flydev-fr/OAuth2Login
      Next step, in order to use a provider, you need to use Composer to install each provider
      ie: to install Google, open a terminal, go to your root directory of pw and type the following command-line: composer require league/oauth2-google
      Tested providers/packages :
          Google :  league/oauth2-google     Facebook: league/oauth2-facebook     Github: league/oauth2-github     LinkedIn: league/oauth2-linkedin
      More third-party providers are available there. You should be able to add a provider by simply adding it to the JSON config file.

      Howto Use It
      First (and for testing purpose), you should create a new user in ProcessWire that reflect your real OAuth2 account information. The important informations are, Last Name, First Name and Email. The module will compare existing users by firstname, lastname and email; If the user match the informations, then he is logged in.
      ie, if my Google fullname is John Wick, then in ProcessWire, I create a new user  Wick-John  with email  johnwick@mydomain.com
      Next step, go to your favorite provider and create an app in order to get the ClientId and ClientSecret keys. Ask on the forum if you have difficulties getting there.
      Once you got the keys for a provider, just paste it into the module settings and save it. One or more button should appear bellow the standard login form.
      The final step is to make your JSON configuration file.
      In this sample, the JSON config include all tested providers, you can of course edit it to suit your needs :
      { "providers": { "google": { "className": "Google", "packageName": "league/oauth2-google", "helpUrl": "https://console.developers.google.com/apis/credentials" }, "facebook": { "className": "Facebook", "packageName": "league/oauth2-facebook", "helpUrl": "https://developers.facebook.com/apps/", "options": { "graphApiVersion": "v2.10", "scope": "email" } }, "github": { "className": "Github", "packageName": "league/oauth2-github", "helpUrl": "https://github.com/settings/developers", "options": { "scope": "user:email" } }, "linkedin": { "className": "LinkedIn", "packageName": "league/oauth2-linkedin", "helpUrl": "https://www.linkedin.com/secure/developer" } } }  
      Backend Usage
      In ready.php, call the module :
      if($page->template == 'admin') { $oauth2mod = $modules->get('Oauth2Login'); if($oauth2mod) $oauth2mod->hookBackend(); }  
      Frontend Usage
      Small note: At this moment the render method is pretty simple. It output a InputfieldForm with InputfieldSubmit(s) into wrapped in a ul:li tag. Feedbacks and ideas welcome!
      For the following example, I created a page login and a template login which contain the following code :
      <?php namespace ProcessWire; if(!$user->isLoggedin()) { $options = array( 'buttonClass' => 'my_button_class', 'buttonValue' => 'Login with {provider}', // {{provider}} keyword 'prependMarkup' => '<div class="wrapper">', 'appendMarkup' => '</div>' ); $redirectUri = str_lreplace('//', '/', $config->urls->httpRoot . $page->url); $content = $modules->get('Oauth2Login')->config( array( 'redirect_uri' => $redirectUri, 'success_uri' => $page->url ) )->render($options); }
      The custom function lstr_replace() :
      /* * replace the last occurence of $search by $replace in $subject */ function str_lreplace($search, $replace, $subject) { return preg_replace('~(.*)' . preg_quote($search, '~') . '~', '$1' . $replace, $subject, 1); }  
      Screenshot
       



    • By gRegor
      Updated 2018-05-06:
      Version 2.0.0 released
      Updated 2017-03-27:
      Version 1.1.3 released
      Updated 2016-04-11:
      Version 1.1.2 released

      Updated 2016-02-26:
      Officially in the module directory! http://modules.processwire.com/modules/webmention/

      Updated 2016-02-25:
      Version 1.1.0 is now released. It's been submitted to the module directory so should appear there soon. In the meantime, it's available on GitHub: https://github.com/gRegorLove/ProcessWire-Webmention. Please refer to the updated README there and let me know if you have any questions!
      ------------
      Original post:
       
      This is now out of date. I recommend reading the official README.
       
      I've been working on this one for a while. It's not 100%, but it is to the point I'm using it on my own site, so it's time for me to release it in beta. Once I finish up some of the features described below, I will submit it to the modules directory as a stable plugin.
      For now, you can install from Github. It works on PW2.5. I haven't tested on PW2.6, but it should work there.
      Feedback and questions are welcome. I'm in the IRC channel #processwire as well as #indiewebcamp if you have any questions about this module, webmention, or microformats.
      Thanks to Ryan for the Comments Fieldtype which helped me a lot in the handling of webmentions in the admin area.
      ProcessWire Webmention Module
      Webmention is a simple way to automatically notify any URL when you link to it on your site. From the receiver's perspective, it is a way to request notification when other sites link to it.
      Version 1.0.0 is a stable beta that covers webmention sending, receiving, parsing, and display. An easy admin interface for received webmentions is under development, as well as support for the Webmention Vouch extension.
      Features
      * Webmention endpoint discovery
      * Automatically send webmentions asynchronously * Automatically receive webmentions * Process webmentions to extract microformats   Requirements * php-mf2 and php-mf2-cleaner libraries; bundled with this package and may optionally be updated using Composer. * This module hooks into the LazyCron module.   Installation Github: https://github.com/gRegorLove/ProcessWire-Webmention

      Installing the core module named "Webmention" will automatically install the Fieldtype and Inputfield modules included in this package.   This module will attempt to add a template and page named "Webmention Endpoint" if the template does not exist already. The default location of this endpoint is http://example.com/webmention-endpoint   After installing the module, create a new field of type "Webmentions" and add it to the template(s) you want to be able to support webmentions. Sending Webmentions
      When creating or editing a page that has the Webmentions field, a checkbox "Send Webmentions" will appear at the bottom. Check this box and any URLs linked in the page body will be queued up for sending webmentions. Note: you should only check the "Send Webmentions" box if the page status is "published."   Receiving Webmentions This module enables receiving webmentions on any pages that have have "Webmentions" field, by adding the webmention endpoint as an HTTP Link header. If you would like to specify a custom webmention endpoint URL, you can do so in the admin area, Modules > Webmention.   Processing Webmentions (beta) Currently no webmentions are automatically processed. You will need to browse to the page in the backend, click "Edit," and scroll to the Webmentions field. There is a dropdown for "Visibility" and "Action" beside each webmention. Select "Process" to parse the webmention for microformats.   A better interface for viewing/processing all received webmentions in one place is under development.   Displaying Webmentions (beta) Within your template file, you can use `$page->Webmentions->render()` [where "Webmentions" is the name you used creating the field] to display a list of approved webmentions. As with the Comments Fieldtype, you can also generate your own output.   The display functionality is also under development.   Logs This module writes two logs: webmentions-sent and webmentions-received.   Vouch The Vouch anti-spam extension is still under development.   IndieWeb The IndieWeb movement is about owning your data. It encourages you to create and publish on your own site and optionally syndicate to third-party sites. Webmention is one of the core building blocks of this movement.   Learn more and get involved by visiting http://indiewebcamp.com.   Further Reading * http://indiewebcamp.com/webmention * http://indiewebcamp.com/comments-presentation * http://indiewebcamp.com/reply