theo Posted March 1, 2018 Share Posted March 1, 2018 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 comment Share on other sites More sharing options...
bernhard Posted March 1, 2018 Author Share Posted March 1, 2018 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 comment Share on other sites More sharing options...
theo Posted March 1, 2018 Share Posted March 1, 2018 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. 1 Link to comment Share on other sites More sharing options...
theo Posted March 2, 2018 Share Posted March 2, 2018 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); 2 Link to comment Share on other sites More sharing options...
bernhard Posted March 14, 2018 Author Share Posted March 14, 2018 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): And the same query as datasource for a datatables field: 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 6 1 Link to comment Share on other sites More sharing options...
adrian Posted March 14, 2018 Share Posted March 14, 2018 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: VS 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? 1 Link to comment Share on other sites More sharing options...
bernhard Posted March 15, 2018 Author Share Posted March 15, 2018 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 Very interesting (and great imho) is also this test using closures: 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: And insanely fast without the closure @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. 2 Link to comment Share on other sites More sharing options...
theo Posted March 15, 2018 Share Posted March 15, 2018 @bernhard : Bravo! Looks great. I will test it later, no time atm. Link to comment Share on other sites More sharing options...
bernhard Posted March 15, 2018 Author Share Posted March 15, 2018 I'm wondering what you guys think of caching? I can think of two situations: 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?! 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: And it would reset whenever a client or an invoice is changed. Link to comment Share on other sites More sharing options...
adrian Posted March 15, 2018 Share Posted March 15, 2018 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() 1 Link to comment Share on other sites More sharing options...
bernhard Posted March 15, 2018 Author Share Posted March 15, 2018 Not to forget the memory drop from 20.63MB to 0.15MB 1 Link to comment Share on other sites More sharing options...
adrian Posted March 15, 2018 Share Posted March 15, 2018 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. 1 Link to comment Share on other sites More sharing options...
theo Posted March 19, 2018 Share Posted March 19, 2018 @bernhard This works great afaics. Well done. I think this is a valuable addition. I hate speed limits. 1 Link to comment Share on other sites More sharing options...
gebeer Posted March 20, 2018 Share Posted March 20, 2018 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. 1 Link to comment Share on other sites More sharing options...
dotnetic Posted March 20, 2018 Share Posted March 20, 2018 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. 1 Link to comment Share on other sites More sharing options...
bernhard Posted March 20, 2018 Author Share Posted March 20, 2018 https://gitlab.com/baumrock/RockSqlFinder is already online. The datatables module is in the works but will need quite some time... 1 Link to comment Share on other sites More sharing options...
dotnetic Posted March 20, 2018 Share Posted March 20, 2018 RockSqlFinder is sooooo fast and memory friendly, even when using one page. Really nice. 2 Link to comment Share on other sites More sharing options...
bernhard Posted March 20, 2018 Author Share Posted March 20, 2018 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 2 Link to comment Share on other sites More sharing options...
bernhard Posted March 31, 2018 Author Share Posted March 31, 2018 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. Todos: Action items for pw-panel editing Anything else that you think is necessary? 6 Link to comment Share on other sites More sharing options...
adrianmak Posted April 3, 2018 Share Posted April 3, 2018 how do i use it on my own module? any example code? Link to comment Share on other sites More sharing options...
bernhard Posted April 3, 2018 Author Share Posted April 3, 2018 24 minutes ago, adrianmak said: how do i use it on my own module? any example code? It is not released yet... Link to comment Share on other sites More sharing options...
adrianmak Posted April 3, 2018 Share Posted April 3, 2018 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 comment Share on other sites More sharing options...
Macrura Posted April 4, 2018 Share Posted April 4, 2018 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.. 1 Link to comment Share on other sites More sharing options...
bernhard Posted April 4, 2018 Author Share Posted April 4, 2018 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 comment Share on other sites More sharing options...
kongondo Posted April 4, 2018 Share Posted April 4, 2018 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. 1 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now