bernhard

Several options. Admin custom files module. AdminOnSteroids has this option too I think. And finally my favourite way: via hook (several possibilities here again)

via page save hook would be the only reliable way. via some javascript would be the more user-friendly way. $(document).on('change', '#Inputfield_yourdatefield', function(e) { var yourdate = $(e.target).val(); var yourotherdate = ...; if(yourdate < yourotherdate) { alert('yourdate must be higher than ...'); // reset field $(e.target).val(''); } }); doing both would be the best don't think there's a built in way...

actually that's a good point because requesting the data from the server is one thing whereas presenting it is another. at datatables you have cell renderers that render your values differently in different situations. so your datefield would ideally be an integer (making it sortable) and be DISPLAYED in the table as a formatted string (using moment.js). so you would not need to format the date via sql because that is done on the client maybe that's why i was not really eager about it and you thought it would be more important. but having a findObject/Array method in the core would justify that feature to some extend. regarding the datatables there will also be an option for defining php functions (see here https://processwire.com/talk/topic/15524-preview-rockdatatables/?do=findComment&comment=158061 ). $table->data( $pages->find('template=item'), [ ['col_name1', 'label of this column', function() { return $this->page->title; }], ... So that would make it really simple to setup tables - you would not have to know or write anything related to SQL. You could just use php's date('Ymd', $page->yourdatefield) function. So all the SQL options are really only meant to be used for complex tables. I think every developer working with that kind of complex tables should not be afraid of using some (very simple btw) sql queries thanks for all your input!

I get your point but I don't think this level of reduction is good. For example if you looked for a way to format your date you would google "mysql date format" and see this example: SELECT DATE_FORMAT(BirthDate, "%W %M %e %Y") FROM Employees; I think it's a lot easier to communicate in the docs that you have to use the "data" column in your query than changing the syntax. So your query would be DATE_FORMAT(data, '%W %M %e %Y') I'm still hesitant about how often that would be needed... But if needed often this could be a handy timesaver for sure.

Don't think that would be a good idea because if you want to query repeaters or the like you can concat strings from subqueries and so on with my route. Maybe we could add a check wheter the statement begins with SELECT or not. If it begins with SELECT it takes the whole query, if not it creates the query for you... 'myfield' => 'YEAR(data)' --> (SELECT YEAR(data) FROM field_myfield WHERE pages_id = p.id) AS myfield 'myfield' => 'SELECT ... FROM ... WHERE ...' --> (SELECT ... FROM ... WHERE ...) AS myfield Not sure how often one would need this short syntax? But also no problem to implement...

not at all, sounds like this could be a great companion! at the moment I think I will also release the datatables module as open source. I've never worked with d3 but the company I'm writing my thesis at does some crazy stuff with it maybe a dedicated "d3 preview" thread would be nice? I really enjoy the discussion and the input here

hi francis, thanks for your suggestions! You mean something like "make a piechart of column x and y"? I'm already thinking of that but don't know if it's really worth the effort when you can write nice charts with some lines of chartjs code... maybe a simple example/tutorial would be of more help... Thanks, I already knew kendo and like the style very much. Though I'm sure I cannot provide all those features. But I already built my module to be extendable via plugins so everybody will be welcome to contribute. The filter plugin will for sure be one of the most important ones and your examples are nice starting points! and https://demos.telerik.com/kendo-ui/grid/filter-menu-customization Yeah, I also thought of that option. Would be quite easy. Could be used like this: $pages->findObjects('template=basic-page', [ 'id', 'templates_id', 'mydate' => 'SELECT YEAR(data) FROM field_mydate WHERE pages_id = p.id', 'multilang_example' => 'SELECT GROUP_CONCAT(#data# separator '|') FROM field_test_page WHERE pages_id = p.id' ]); 2 things here to mention: using subqueries makes this very easy to implement multilang would be possible replacing #data# by data1234 (data + langid) I really like where this goes so far

updated the hook in my previous post to support queries of the pages table itself (like templates_id, created_users_id and so on). edit: this solution is not the best, imho. It would be better to have one array with all fields and then split this array automatically (like this: if field part of pages table query this field directly else join field_xxx )

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