bernhard

hi adrian This is a GREAT idea Very helpful indeed! I think that should definitely go into the core. I see you are using joins for your example. As mentioned here I think it's better to use subqueries in our case because it makes the query easier (to read, write and construct) and it makes it easier to query other fieldtypes (like repeaters or the like). here is my modified version of your hook. I think that could really be helpful in many situations. I would suggest using $pages->findArray() and $pages->findObject() as different methods. Easier to remember and more self-explaining $this->addHook("Pages::findObject", function($event) { $event->return = $this->pages->findArray($event->arguments(0), $event->arguments(1), $event->arguments(2), true); }); $this->addHook("Pages::findArray", function($event) { $selector = $event->arguments(0); $fields = $event->arguments(1); $fields_pages = $event->arguments(2) ?: []; $type = $event->arguments(3) ? \PDO::FETCH_OBJ : \PDO::FETCH_ASSOC; // todo: check for empty pages find operation and early exit // $this->pages->findIDs($selector) // https://processwire.com/talk/topic/18558-mysql-database-to-processwire-pages-opinions/?do=findComment&comment=162328 // build sql string $sql = "SELECT\n p."; // add fields of pages table $fields_pages[] = 'id'; // make sure we return the page id $fields_pages = array_unique($fields_pages); $sql .= implode(",\n p.", $fields_pages); foreach($fields as $f) { $field = $this->fields->get($f); if(!$field) continue; $fieldtype = $field->type; // fielddata is always stored in the "data" column of the field's table // multilang fields have several data columns identified by the language id // we use a variable to query the current user's language, eg data1234 $data = "data"; switch(true) { // if it is a multilang field we append the language id to query the correct column case $fieldtype instanceof FieldtypeTextLanguage: case $fieldtype instanceof FieldtypeTextareaLanguage: if($this->user->language->name != 'default') $data .= $this->user->language->id; // no break here intended! // build sql query case $fieldtype instanceof FieldtypeText: $sql .= ",\n (SELECT $data FROM field_$f WHERE pages_id = p.id) AS $f"; break; case $fieldtype instanceof FieldtypePage: $sql .= ",\n (SELECT GROUP_CONCAT($data SEPARATOR ',') FROM field_$f WHERE pages_id = p.id) AS $f"; break; default: $sql .= ",\n '$fieldtype not supported' AS $f"; } } $sql .= "\nFROM\n pages AS p"; $sql .= "\nWHERE\n p.id IN (" . implode(",", $this->pages->findIDs($selector)) . ")"; $results = $this->database->query($sql); $event->return = $results->fetchAll($type); }); This is really easy to extend for all other fieldtypes (and maybe we could also implement a feature to add a custom query as returned field?). I also added support for multilanguage fields (see field1 in the example): btw: querying the database via a pages->findIDs query is a very good idea because it already takes care of page status and access control does anybody know any limitations for the length of the query (querying 10.000 pages means having 10.000 ids in the sql statement!). edit: I'm querying 10.000 pages again in the example and this was the resulting query: SELECT p.id, (SELECT data FROM field_title WHERE pages_id = p.id) AS title, (SELECT data11041 FROM field_field1 WHERE pages_id = p.id) AS field1, (SELECT GROUP_CONCAT(data SEPARATOR ',') FROM field_test_page WHERE pages_id = p.id) AS test_page, 'FieldtypeRepeater not supported' AS test_repeater FROM pages AS p WHERE p.id IN (1016,1017,1018,1019,1020,1021...) PS: The "FieldtypeRepeater not supported" part is just for demonstration. I think a repeater should return ids just like the "test_page" field in my example does. Maybe we should use a pipe as separator so that you can instantly use the returned value as a $pages->find() selector?

thx, this was only for testing thx, didn't know that! writing on an answer in the datatables thread - i'm working on your findArray hook

after seeing @adrian also used joins in this example (https://processwire.com/talk/topic/15524-preview-rockdatatables/?do=findComment&comment=158104) I did a quick google and it seems that there is a slight performance benefit in using joins over using subqueries. When writing quieries manually (without any helping functions like in adrians example) i think it is better to use subqueries as they are a lot easier to write, read and maintain and they are less likely to return wrong results imho. see also https://stackoverflow.com/questions/2577174/join-vs-sub-query I also did a quick test on my setup: t(); $rows = []; $result = $this->db->query("select id, title.data as title, field1.data as field1 from pages as p left join field_title as title on title.pages_id = p.id left join field_field1 as field1 on field1.pages_id = p.id where p.templates_id = 44"); while($row = $result->fetch_object()) { $rows[] = $row; } d($rows); d(t(), 'joins'); $rows = []; $result = $this->db->query("select id, (select data from field_title where pages_id = p.id) as title, (select data from field_field1 where pages_id = p.id) as field1 from pages as p where p.templates_id = 44"); while($row = $result->fetch_object()) { $rows[] = $row; } d($rows); d(t(), 'subqueries'); Most of the time the joins were a little faster, but not always - so I think it should not matter regarding performance which one to use:

I'm sure you'll love it I'm glad you chose my blogpost as a tutorial but keep in mind that this was intended to show how to build process modules. I guess there are better resources for regular modules out there edit: just saw you already mentioned it: yes, you don't need a process module for that. i created the tutorial because there are lots of tutorials and examples of how to create regular modules but only very few that cover process modules. creating your own fields (fieldtype/inputfield modules) is a little more complex. i would recommend you start by creating a simple module that loads some javascript, hooks into something (like modifying markup of other fields) this is a simple example of one of my first modules that covers lots of basic principles and that you can use for learning: https://github.com/BernhardBaumrock/TemplatePreviewImages (note that $config->scripts->add() only works when the fields are not ajax loaded)

hi laps, I understand your concerns. Though, as the others already said, the frontend is totally up to you (by design). I agree that this makes forms a little more work than we are used to from all the other pw magic. I used NetteForms for a website with lots of forms since it brings you client&server side validation in one run! I don't know any other tool that does this and I was always looking for something like this. @tpr built a module for netteforms, but i can't find a link right now? What was "hard" exactly? How would you suggest an easier workflow? Do you have any examples of how other systems solve that topic better?

nice site 2 suggestions (or maybe more 1 suggestion and 1 feedback): it would be nice to have different markers for sold/available - can save lots of clicking. i would prefer opening the details on hover and not on click (needing a little caution on mobile of course)

Ok, thanks. I get your point now. Actually I did not think of having a GUI for my tables at all but it seems it could make a lot of sense. Taking this idea further it could also be used as a Lister replacement... I have to think about that and how to put all those usecases under one hood.

@adrian I'll explain more details tomorrow just a quick question: if you only define data by a selector, how would you define which columns to show (which fields, which columns headers)? But you brought up yet another idea in my head

sorry, I'm still not sure if I get what you mean... if you use findIDs then you have to use an sql query afterwards. not sure why that should be better than having a query like this: select ... from pages where parent_id=12593 Maybe you are talking about more complex examples where it could be easier to use a pw selector than having several WHERE ... and ... and ... in your SQL? Not sure how often those cases would rise up... but it's no problem anyhow because i plan to offer those two options of defining table data: $table->data( $pages->find('template=item'), [ ['col_name1', 'label of this column', function() { return $this->page->title; }], ['col_name2', __('multilang label'), function() { return $this->page->parent->title; }], ['col_name3', 'another good label', function() { return implode('<br>', $this->page->repeater->each('path')); }], ]); defining the table like this would really be simple and i really like this way so thank you for bringing this up again $table->data( 'SELECT field1, field2, field3 FROM pages WHERE templates_id=44', [ ['col_name1', 'label of this column', 'fiel1'], ['col_name2', __('multilang label'), 'fiel2'], ['col_name3', 'another good label', 'fiel3'], ]); the other option will be any sql query, so you could also create this sql query like you did in your example combining it with a $pages->findIDs()

hi adrian, totally useful thoughts as always! i agree that for simple tables it would be easier to use the pw api. and i think that this will make sense in many situations, so i will build the module to support both options. it's definitely not necessary to go the sql route when all you want to do is listing 20 or so pages i did some tests on findMany and findIDs but as i need to access fields of the page it's not enough to just load the ids. and then it gets slow again...

ok... i get closer it's definitely the best to build the tables by sql queries. so the module will get an easy sql editor with live preview like shown here: you see that the query loads 10.000 rows in around 500ms another huge benefit of writing sql queries is that you can group and sum your data easily, like in this example, where i build sums of "field3" grouped by month: thanks to @adrian 's idea i zipped the json response of the sql query making it shrink from 2.7mb in the first example to 121kb I'll add some helpers to make multilang queries easy and to query views (for kind of a modular setup)

hm... not sure why you have <region...></div> in your example? you could also use a variable + if in your _main.php: // _main.php if($config->sidebar) { // your sidebar markup // grid // region body // region sidebar } else { // no sidebar // region body } and in your template file $config->sidebar = false; <region id="body">your content</region> // or $config->sidebar = true; <region id="body">your content</region> <region id="sidebar">your sidebar</region> but i think there's also nothing wrong about setting regions to null

thanks kongondo, all valid points. the reason why i want to have all date loaded at once is because it makes the development of my module a lot easier and has some huge benefits over loading data paginated via ajax. when i have all the data available at the client i can use datatables' api to filter, sort, query, draw charts etc.; that would be very hard to achieve using server side techniques. i was also trying to avoid direct sql queries, that's why i took other approaches for my first two versions of the module. but it turnes out that all of that approaches have some really big drawbacks. actually building the queries via SQL manually is not as difficult as i thought (the genious api and the easy page->find() operations was one of the main reasons i fell in love with processwire). and i have some nice ideas how to make it even more comfortable and easy. I'm quite sure it will be really easy to use for everybody

nice do you think you could extend this module to also store images pasted from the clipboard? PS: i think it would be great if the images were added via AJAX right after pasting (either urls or clipboard). what do you think @Robin S ?

because it's a LOT more performant. constructing my datastring via pages->find() and a foreach takes 16 seconds for 10.000 rows and 5 columns (needing some extra seconds for every column added) whereas querying the database directly needs only some milliseconds. my other idea was to cache the table rows on the dedicated templates but that leads to problems when you have a "parent" or "category" column because then it would take several seconds to recreate the cache of the table when the name of the category changes (updating up to thousands of rows' cache). It also leads to a lot of redundant data. All of that problems are solved when the DB is queried directly

glad it helped. actually i think that the usability of this site/module could be improved. it was also not self-explaining for me when i did my first steps with multilanguage setups...

there's a thread for phpstorm and vscode - feel free to open one for atom...

thanks for your suggestion dragan! i modified your example a little bit. it's also clear and easy to extend with other fields... but still i find this one easier to read and maintain (having one line for each field vs. in your example (and my previous tries) it's 3 lines for each field): select id, (select data from field_title where pages_id = p.id) as title, (select data from field_field1 where pages_id = p.id) as field1, (select data11041 from field_field1 where pages_id = p.id) as field1_de, parent_id, (select data from field_title where pages_id = p.parent_id) as parent_title, (select group_concat(data separator ',') from field_test_page where pages_id = p.id) as test_page from pages as p where p.templates_id = 44 i did some tests and it seems that both have the same performance... 65ms for a query over all 10.000 rows See "test_page" in the example above - also not that hard to get. Only problem could be special characters and quotes - not sure about that... but i think in that situations it would be the best to add a field that gets populated via a save hook then you could populate whatever value you want easily via PHP.

hi cesco, of course this is possible, maybe you have to refresh your file list (that caught me some time too):

yep, i know i talked to him during my writeup of the blogpost. i just wanted to help as i think 100mb for everybody is not the best solution. i'm fine now, but i think it makes sense to restrict new users more than active ones. thanks for taking care of it now!

would be a perfect fit as every developer should use tracy anyhow no idea how different the IDEs work or how difficult that would be (i guess it should not be too hard?), but it would be great to support different IDEs of course. at least vscode

https://themeforest.net/ https://templated.co/ https://html5up.net/

Just had an idea: what if we created a module that creates some kind of static file that the IDE can read listing all fields of a given template. Maybe we could also extend this module to parse all hooks even for properties that where added via a hook does anyone need a challenge for christmas? to your question, pwuser1 (welcome btw): some use phpstorm, some vscode, some sublime, some atom...

I'm still looking for the best solution for my datatables module and I think there is no way around using direct sql queries... That makes it more complicated to setup but saves a lot of other troubles like proper caching, cache maintenance etc... Unfortunately such sql queries can get quite complex unless I'm missing any pw magic that makes it easier to do? This is what i have so far to query all pages of type "basic-page": select id, title.data as title, headline.data as headline, body.data as body, (select group_concat(data separator ',') from field_test_page where pages_id = p.id) as test_page, test_repeater.data as test_repeater from pages as p left join field_title as title on title.pages_id = p.id left join field_headline as headline on headline.pages_id = p.id left join field_body as body on body.pages_id = p.id left join field_test_repeater as test_repeater on test_repeater.pages_id = p.id where p.templates_id = 29 the repeater is a little tricky for example. it gets even worse when you have to handle multilanguage fields or you want references to other pages' field values... edit: maybe better like this? select id, (select data from field_title where pages_id = p.id) as title, (select data from field_field1 where pages_id = p.id) as field1, (select data11041 from field_field1 where pages_id = p.id) as field1_de, parent_id, (select data from field_title where pages_id = p.parent_id) as parent_title from pages as p where p.templates_id = 44 thanks for any hints

I think that's two totally different things... Migrator is for applying changes to sites in a programmatical way. So you can do... migrations Page Import/Export may be helpful when you transfer sites or parts of sites. But I don't think it will be helpful when you have a live site and want to add some new features while you cannot take the site offline and there might be changes in data/content while you are working on the updates. But I haven't used both of them so far. But I'm watching them for a long time... For me until now it was sufficient to work directly on the live server (having proper backups of course). I did some tests writing my own modules and applying update scripts... that was also quite easy. But there where situations where I wished I had a helper that takes care of some things like checking if the field that i want to create already exists, deleting all pages before deleting a template etc.; not sure how or if migrator handles that or what you plan exactly by your rewrite but I'm quite sure I would pay for a module that makes this process easier. And I'm quite sure that there is some demand on this staging/production migration topic... But I also think that tracydebugger has the bigger audience - so this would be more interesting as a business case