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 daniels
      General
      This is a lightweight alternative to other newsletter & newsletter-subscription modules.
      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 You can find it here: https://github.com/danielstieber/SubscribeToMailchimp
      Let me know what you think and if I should add it to the Modules Directory.
      Setup
      Log into your Mailchimp account and go to  Profile > Extras > API Keys. If you don't have an API Key, create a new one. Copy your API Key and paste it in the module settings (Processwire > Modules > Site > SubscribeToMailchimp). Back in Mailchimp, go to the list, where you want your new subscribers. Go to Settings > List name and defaults. Copy the List ID an paste it in to the module settings.
      Usage
      To use the module, you need to load it into your template:
      $mc = $modules->get("SubscribeToMailchimp"); Now you can pass an email address to the module and it will try to edit (if the user exists) or create a new subscriber in your list.
      $mc->subscribe('john.doe@example.com'); You can also pass a data array, to add additional info.
      $mc->subscribe('john.doe@example.com', ['FNAME' => 'John', 'LNAME' => 'Doe']); You can even choose an alternative list, if you don't want this subscriber in your default list.
      $mc->subscribe('john.doe@example.com', ['FNAME' => 'John', 'LNAME' => 'Doe'], 'abcdef1356'); // Subscribe to List ID abcdef1356 If you want to unsubscribe a user from a list, you can use the unsubscribe method.
      $mc->unsubscribe('john.doe@example.com'); // Unsubscribe john.doe@example.com from the default list $mc->unsubscribe('john.doe@example.com', 'abcdef1356'); // Unsubscribe john.doe@example.com from the list abcdef1356 If you want to permantly delete a user, you can call the delete method. Carefully, this step cannot be undone
      $mc->delete('john.doe@example.com'); // Permanently deletes john.doe@example.com from the default list $mc->delete('john.doe@example.com', 'abcdef1356'); // Permanently deletes john.doe@example.com from the list abcdef1356  
      Important Notes
      This module does not do any data validation. Use a sever-sided validation like Valitron Make sure that you have set up your fields in your Mailchimp list. You can do it at Settings > List fields and *|MERGE|* tags Example
      Example usage after a form is submitted on your page:
      // ... validation of form data $mc = $modules->get("SubscribeToMailchimp"); $email = $input->post->email; $subscriber = [ 'FNAME' => $input->post->firstname, 'LNAME' => $input->post->lastname, ]; $mc->subscribe($email, $subscriber);  
      Troubleshooting
      In case of trouble check your ProcessWire warning logs.
      I can't see the subscriber in the list
      If you have enabled double opt-in (it is enabled by default) you will not see the subscriber, until he confirmed the subscription in the email sent by Mailchimp
      I get an error in my ProccessWire warning logs
      Check if you have the right List ID and API Key. Check if you pass fields, that exist in your list. Check if you pass a valid email address. Go to Mailchimps Error Glossary for more Information
      How To Install
      Download the zip file at Github or clone directly the repo into your site/modules If you downloaded the zip file, extract it in your sites/modules directory. You might have to change the folders name to 'SubscribeToMailchimp'. Goto the modules admin page, click on refresh and install it  
      Changelog
      0.0.2
      Note: You can update safely from 0.0.1 without any changes in your code
      New Features
      Added 'Unsubscribe' method $mc->unsubscribe($email, $list = "") Added 'Delete' method $mc->delete($email, $list = "") Bug Fixes and compatibility changes
      Removed type declarations to be compatible with PHP 5.1+* (thanks to wbmnfktr) Other
      Changed the way, the base url for the api gets called *I have only tested it with PHP 7.x so far, so use on owners risk
    • By BitPoet
      As threatened in the Pub sub forum in the "What are you currently building?" thread, I've toyed around with Collabora CODE and built file editing capabilities for office documents (Libre-/OpenOffice formats and MS Office as well as a few really old file types) into a PW module.
      If you are running OwnCloud or NextCloud, you'll perhaps be familiar with the Collabora app for this purpose.
      LoolEditor
      Edit office files directly in ProcessWire
      Edit your docx, odt, pptx, xlsx or whatever office files you have stored in your file fields directly from ProcessWire's page editor. Upload, click the edit icon, make your changes and save. Can be enabled per field, even in template context.
      Currently supports opening and saving of office documents. Locking functionality is in development.
      See the README on GitHub for installation instructions. You should be reasonably experienced with configuring HTTPS and running docker images to get things set up quickly.
      Pull requests are welcome!
      Here is a short demonstration:

    • By Robin S
      An Images field allows you to:
      Rename images by clicking the filename in the edit panel or in list view. Replace images, keeping metadata and filename (when possible) by dropping a new image on the thumbnail in the edit panel. Introduced here. But neither of these things is possible in File fields, which prompted this module. The way that files are renamed or replaced in this module is not as slick as in the Images field but it gets the job done. The most time-consuming part was dealing with the UI differences of the core admin themes. @tpr, gives me even more respect for the work that must go into AdminOnSteroids.
      Most of the code to support the rename/replace features is already present in InputfieldFile - there is just no UI for it currently. So hopefully that means these features will be offered in the core soon and this module can become obsolete.
       
      Files Rename Replace
      Allows files to be renamed or replaced in Page Edit.

      Usage
      Install the Files Rename Replace module.
      If you want to limit the module to certain roles only, select the roles in the module config. If no roles are selected then any role may rename/replace files.
      In Page Edit, click "Rename/Replace" for a file...
      Rename
      Use the text input to edit the existing name (excluding file extension).
      Replace
      Use the "Replace with" select to choose a replacement file from the same field. On page save the file will be replaced with the file you selected. Metadata (description, tags) will be retained, and the filename also if the file extensions are the same.
      Tip: newly uploaded files will appear in the "Replace with" select after the page has been saved.
       
      https://github.com/Toutouwai/FilesRenameReplace
      http://modules.processwire.com/modules/files-rename-replace/
    • By d'Hinnisdaël
      So I decided to wade into module development and created a wrapper module around ImageOptim, a service that compresses and optimizes images in the cloud. ImageOptim currently handles JPG, PNG and GIF files and, depending on the settings you use, shaves off between 15% and 60% in filesize. Great for bandwidth and great for users, especially on mobile.
      This module handles the part of uploading images to ImageOptim via their official API, downloading the optimized version and storing it alongside the original image.
       

       
      Download & Info
      GitHub / Module directory / Readme / Usage
       
      Why ImageOptim?
      There are other image optimization services out there, some of them free, that have outstanding ProcessWire modules. A few things make ImageOptim the best tool for most of my customers: It's not free, i.e. it will probably be around for a while and offers support. However, it's cheaper than some of the bigger competitors like Cloudinary. And it does PNG compression better than any of the free services out there, especially those with alpha channels.
       
      Installation
      Install the module like any other ProcessWire module, by either copying the folder into your modules folder or installing it via the admin. See above for downloads links on GitHub and in the module directory.
       
      Requirements
      To be able to upload images to the service, allow_url_fopen must be set on the server. The module will abort installation if that's not the case.
      I have only tested the module on ProcessWire 3.x installations. I don't see why it shouldn't work in 2.x, if anyone wants to try it out and report back.
       
      ImageOptim account
      To compress images, you first need to sign up for an ImageOptim account. They offer free trials to try the service.
       
      Usage (manual optimization)
      Images can be optimized by calling the optimize() method on any image. You can pass an options array to set ImageOptim API parameters.
      $image->size(800,600)->optimize()->url $image->optimize(['quality' => 'low', 'dpr' => 2]) // Set quality to low and enable hi-dpi mode
      Automatic optimization
      The module also has an automatic mode that optimizes all image variations after resizing. This is the recommended way to use this module since it leaves the original image uncompressed, but optimizes all derivative images.
      $image->size(800,600)->url // nothing to do here; image is optimized automatically
      To change compression setting for single images, you can pass an options array along with the standard ImageResizer options. Passing false disables optimization.
      $image->size(800, 600, ['optimize' => 'medium']) $image->size(800, 600, ['optimize' => ['quality' => 'low', 'dpr' => 2]]) $image->size(800, 600, ['optimize' => false])
      For detailed usage instructions and all API parameters, see the usage instructions on GitHub.
       
      Filenames
      Optimized images will be suffixed, e.g. image.jpg becomes image.optim.jpg. You can configure the suffix in the module settings.
       
      Roadmap
      Asynchronous processing. Not really high on the list. Image variations need to be created anyway, so waiting a few seconds longer on first load is preferable to adding complexity to achieve async optimization. Optimize image variations created by other modules. CroppableImage comes to mind. I don't use any of these, so if somebody wants to help out and submit a pull request — all for it! Add a dedicated page in the setup menu with a dashboard and detailed statistics. ImageOptim's API is very barebones for now, so not sure if that's feasible or even necessary.
      Stability
      I've been using this module on production sites for some time now, without hiccups. If you do notice oddities, feel free to comment here or investigate and submit PRs.
    • By PWaddict
      Module for hiding pages for non-superusers.
      Download
      HidePages
      Requirements
      ProcessWire 3.x Changelog
      1.0.1 (31 March 2018)
      Module renamed to prevent confusion 1.0.0 (30 March 2018)
      Initial release