Jump to content

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.

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/

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
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
Link to post
Share on other sites
  • 2 weeks later...

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

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
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
Link to post
Share on other sites
  • 2 weeks later...

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

 

 

 

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

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
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 monollonom
      (once again I was surprised to see a work of mine pop up in the newsletter, this time without even listing the module on PW modules website 😅. Thx @teppo !)
      Github: https://github.com/romaincazier/FieldtypeQRCode
      Modules directory: https://processwire.com/modules/fieldtype-qrcode/
      This is a simple module I made so a client could quickly grab a QR Code of the page's url in the admin.
      There's not much to it for now, but if need be you can output anything using a hook:
      $wire->addHookAfter("FieldtypeQRCode::getQRText", function($event) { $event->return = "Your custom text"; }) You can also output the QR code on your front-end by calling the field:
      echo $page->qr_code_field; The module uses the PHP library QR Code Generator by Kazuhiko Arase. When looking for a way to generate a QR Code in PW I came across @ryan's integration in his TFA module. I'm not very familiar with fieldtype/inputfield module development so I blindly followed @bernhard (great) tutorial and his BaseFieldtypeRuntime. At some point I'll take a deeper look to make a module on my own.
      Some ideas for improvements :
      add the ability to choose what to ouput : page's url / editUrl / file(s) / image(s) / ... allow to output multiple QR codes ?
    • By Chris Bennett
      https://github.com/chrisbennett-Bene/AdminThemeTweaker
      Inspired by @bernhard's excellent work on the new customisable LESS CSS getting rolled into the core soon, I thought I would offer up the module for beta testing, if it is of interest to anyone.

      It takes a different approach to admin styling, basically using the Cascade part of CSS to over-ride default UiKit values.
      Values are stored in ModuleConfig Module creates a separate AdminThemeTweaker Folder at root, so it can link to AdminThemeTweaker.php as CSS AdminThemeTweaker.php reads the module values, constructs the CSS variables then includes the CSS framework Can be switched on and off with a click. Uninstall removes everything, thanks to bernhard's wonderful remove dir & contents function.
      It won't touch your core. It won't care if stuff is upgraded. You won't need to compile anything and you don't need to touch CSS unless you want to.

      It won't do much at all apart from read some values from your module config, work out the right CSS variables to use (auto contrast based on selected backgrounds) and throw it on your screen.
      You can configure a lot of stuff, leave it as it comes (dark and curvy), change two main colors (background and content background) or delve deep to configure custom margins, height of mastheads, and all manner of silly stuff I never use.

      Have been developing it for somewhere around 2 years now. It has been (and will continue to be) constantly tweaked over that time, as I click on something and find something else to do.
      That said, it is pretty solid and has been in constant use as my sole Admin styling option for all of those 2 years.

      If nothing else, it would be great if it can provide any assistance to @bernhard or other contributor's who may be looking to solve some of the quirkier UiKit behavior.
      Has (in my opinion) more robust and predictable handling of hidden Inputfields, data-colwidths and showIf wrappers.
      I am very keen to help out with that stuff in any way I can, though LESS (and any css frameworks/tools basically) are not my go.
      I love CSS variables and banging-rocks-together, no-dependency CSS you can write with notepad.



       

    • By opalepatrick
      I see old posts saying that repeaters are not the way to go in Custom Process Modules. If that is the case, when using forms (as I am trying to do) how would one tackle things like repeat contact fields where there can be multiple requirements for contact details with different parameters? (Like point of contact, director, etc) or even telephone numbers that have different uses?
      Just for background I am creating a process module that allows me to create types of financial applications in the admin area (no need to publish any of this, pure admin) that require a lot of personal or company information.
      Maybe I am thinking about this incorrectly?
    • By HMCB
      I ran across a reference to IftRunner module. The post was 6 years ago. I cant find it in available modules. Has it been pulled?
    • By tcnet
      PageViewStatistic for ProcessWire is a module to log page visits of the CMS. The records including some basic information like IP-address, browser, operating system, requested page and originate page. Please note that this module doesn't claim to be the best or most accurate.
      Advantages
      One of the biggest advantage is that this module doesn't require any external service like Google Analytics or similar. You don't have to modify your templates either. There is also no Javascript or image required.
      Disadvantages
      There is only one disadvantage. This module doesn't record visits if the browser loads the page from its browser cache. To prevent the browser from loading the page from its cache, add the following meta tags to the header of your page:
      <meta http-equiv="Cache-Control" content="no-cache, no-store, must-revalidate" /> <meta http-equiv="Pragma" content="no-cache" /> <meta http-equiv="Expires" content="0" /> How to use
      The records can be accessed via the Setup-menu of the CMS backend. The first dropdown control changes the view mode.

      Detailed records
      View mode "Detailed records" shows all visits of the selected day individually with IP-address, browser, operating system, requested page and originate page. Click the update button to see new added records.

      Cached visitor records
      View modes other than "Detailed records" are cached visitor counts which will be collected on a daily basis from the detailed records. This procedure ensures a faster display even with a large number of data records. Another advantage is that the detailed records can be deleted while the cache remains. The cache can be updated manually or automatically in a specified time period. Multiple visits from the same IP address on the same day are counted as a single visitor.

      Upgrade from older versions
      Cached visitor counts is new in version 1.0.8. If you just upgraded from an older version you might expire a delay or even an error 500 if you display cached visitor counts. The reason for this is that the cache has to be created from the records. This can take longer if your database contains many records. Sometimes it might hit the maximally execution time. Don't worry about that and keep reloading the page until the cache is completely created.
      Special Feature
      PageViewStatistic for ProcessWire can record the time a visitor viewed the page. This feature is deactivated by default. To activate open the module configuration page and activate "Record view time". If activated you will find a new column "S." in the records which means the time of view in seconds. With every page request, a Javascript code is inserted directly after the <body> tag. Every time the visitor switches to another tab or closes the tab, this script reports the number of seconds the tab was visible. The initial page request is recorded only as a hyphen (-).

      Settings
      You can access the module settings by clicking the Configuration button at the bottom of the records page. The settings page is also available in the menu: Modules->Configure->ProcessPageViewStat.
      IP2Location
      This module uses the IP2Location database from: http://www.ip2location.com. This database is required to obtain the country from the IP address. IP2Location updates this database at the begin of every month. The settings of ProcessPageViewStat offers the ability to automatically download the database monthly. Please note, that automatically download will not work if your webspace doesn't allow allow_url_fopen.
      Dragscroll
      This module uses DragScroll. A JavaScript available from: http://github.com/asvd/dragscroll. Dragscroll adds the ability in view mode "Day" to drag the records horizontally with the mouse pointer.
      parseUserAgentStringClass
      This module uses the PHP class parseUserAgentStringClass available from: http://www.toms-world.org/blog/parseuseragentstring/. This class is required to filter out the browser type and operating system from the server request.
×
×
  • Create New...