Preview/Discussion: RockDataTables

[kathep]

Excellent @bernhard! 

I would also buy a promodule, if it allowed the ability to have frontend pages.

[bernhard]

34 minutes ago, kathep said:

I would also buy a promodule, if it allowed the ability to have frontend pages.

Can you explain your exact usecase? I'm still not sure how useful the module could be on the frontend... It's really easy to implemenet a Datatable on your own on the frontend and then you have all the flexebility that custom code provides. Not sure if it makes sense to trade flexibility against ease of use...

This is an awesome example by @Macrura http://ohmspeaker.com/speaker-filter/?length=13&width=17

That would not be possible with my module. At least it would not be easier than without using it... So I'm not sure if my module could provide what you expect

[bernhard]

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)

[adrian]

@bernhard - I love the flexibility of pure SQL as well, but for those less familiar with it, I was wondering if you have experimented with findMany() ?

The other option might be findIDs() - could you perhaps build an SQL query from the returned IDs to get the other requested fields?

Maybe one of these options would still be performant enough and a little more user friendly? Maybe the option for SQL or a selector so users have the choice depending on their needs an skills?

Just some random thoughts that might not be useful at all

[bernhard]

2 hours ago, adrian said:

Just some random thoughts that might not be useful at all

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

[adrian]

Hey @bernhard - glad you're going to offer both. 

On the use of findIDs()  - I don't think I explained properly. My ideas was to take the returned IDs and build up a manual SQL query using those. Here is what it might look like this. It's rough, but it's running on 5000 pages and this returns the id and the title in 47ms and all that is needed is a simple selector - in this case: parent_id=xxxx

Does this suit your needs?

[bernhard]

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

[adrian]

23 minutes ago, bernhard said:

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

If the selector is that simple, then sure SQL is easy to write. 

Maybe I am missing something, but my assumption is that currently the user will have to write an SQL query themselves to get the pages they want. Is that correct? If it is, then my thought was that using findIDs would let you take a selector (generated from InputfieldSelector) and then pass those IDs into an SQL that is generated by your module. That way the user wouldn't have to code anything - all GUI via the InputfieldSelector (what is used by Lister/ListerPro etc). 

Does that make sense, or am I still just confused about how this module works?

PS - in my example above there is not actually any reason to even get the id from the pages table because you already have that (obviously). So you probably just need to get from the db tables for each of the fields that you need to present.

[bernhard]

@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

[adrian]

Just now, bernhard said:

if you only define data by a selector, how would you define which columns to show (which fields, which columns headers)?

 

The same way Lister does - with an ASM field that lets them select fields and sort the order they will be displayed in.

[bernhard]

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]

Hey @bernhard - I just quickly hacked together a new $pages->findArray() method that you can use like this:

$pages->findArray("parent=12593", array('title', 'integer'), 'object');

It takes 3 arguments:

• selector
• array of fields to return
• return an array or object

It needs to be cleaned up (eg properly bind values etc), but as you can see in the screenshot below that it returns an array of the requested fields for 5000 pages in 66ms. I wonder whether there is justification for something like this in the core? It seems like a pretty efficient way to get just the fields you're interested in without the overhead of page objects.

You can obviously then do:

Object

foreach($pages->findArray("parent=12593", array('title', 'integer'), 'object') as $p) {
    d($p->title.':'.$p->integer);
}

OR

Array

foreach($pages->findArray("parent=12593", array('title', 'integer'), 'array') as $p) {
    d($p['title'].':'.$p['integer']);
}

This certainly isn't specific to your module - I am definitely going to be making use of this in the future for my sites when I need data for tables / charts, etc.

Here is the hook code:

Spoiler

$this->addHook("Pages::findArray", function($event) {

    $selector = $event->arguments(0);
    $fields = $event->arguments(1);
    $type = $event->arguments(2) == 'array' ? PDO::FETCH_ASSOC : PDO::FETCH_OBJ;

    $fieldsStrArr = array();
    $fieldsJoins = '';
    foreach($fields as $f) {
        $fieldsStrArr[] = "field_$f.data AS `$f`";
        $fieldsJoins .= "LEFT JOIN field_$f ON pages.id = field_$f.pages_id\n";
    }
    $fieldsStr = implode(', ', $fieldsStrArr);

    $items = $this->pages->findIDs($selector);
    $sql = "
    SELECT pages.id, $fieldsStr FROM pages
    $fieldsJoins
    WHERE id IN (" . implode(',',$items) . ")
    ";
    $results = $this->database->query($sql);
    $event->return = $results->fetchAll($type);

});

 

 

BTW - the "title" field in the results below is correct to be a number - these were 5000 API generated pages with numeric titles.

 

[bernhard]

hi adrian

On 17.12.2017 at 4:27 PM, adrian said:

I just quickly hacked together a new $pages->findArray() method that you can use like this

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?

[adrian]

28 minutes ago, bernhard said:

does anybody know any limitations for the length of the query (querying 10.000 pages means having 10.000 ids in the sql statement!)

The only limit is what is hit by the "max_allowed_packet" setting.

As for the performance, I think we're probably OK because "id" is indexed. If it wasn't, we'd be in trouble. Might need to do an extreme test to really know. I suppose an SQL query completely from scratch would be more efficient, but using findIDs certainly simplifies lots of things as you noted, and especially if the selector gets complex - groups, subfield values etc.

[bernhard]

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 )

[FrancisChung]

Great work @bernhard !

The only suggestion that comes on top of my head is maybe some built-in default charts? 

Also, you could look at the features list of other commercial data table products out in the market, (not just for PHP/JS but other areas like .NET etc) and see if there's anything interesting features you would want to incorporate into yours.

Companies I can think of are : Infragistics, Telerik, DevExpress, Syncfusion etc.

https://js.devexpress.com/

https://www.telerik.com/kendo-ui/grid

https://www.infragistics.com/products/ignite-ui/grids-and-lists/data-grid

https://www.syncfusion.com/products/javascript/ejgrid

As for whether it becomes free or paid version, I'd certainly be happy to pay for it if I had a use case for it and it was within the budget constraints. It certainly could win clients over if you could show them your CMS has reporting capabilities that others may or may not have.

[adrian]

@bernhard - I haven't looked into the best way to incorporate it yet, but before I forget, I think it would be great to be able to add mysql functions to these new $pages methods. For example, it would be great to be able to directly get the YEAR from a datetime field using YEAR(datetime) AS year. Lots of other options of course, but that should give you an idea of what I am thinking.

[bernhard]

hi francis,

thanks for your suggestions!

7 hours ago, FrancisChung said:

The only suggestion that comes on top of my head is maybe some built-in default charts? 

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

7 hours ago, FrancisChung said:

Also, you could look at the features list of other commercial data table products out in the market

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

4 hours ago, adrian said:

@bernhard - I haven't looked into the best way to incorporate it yet, but before I forget, I think it would be great to be able to add mysql functions to these new $pages methods. For example, it would be great to be able to directly get the YEAR from a datetime field using YEAR(datetime) AS year. Lots of other options of course, but that should give you an idea of what I am thinking.

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:

1. using subqueries makes this very easy to implement
2. multilang would be possible replacing #data# by data1234 (data + langid)

I really like where this goes so far

[kongondo]

1 minute ago, bernhard said:

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

Hi. Just so you know, I have been working (on the side, slow pace) on a free data visualisation module based on d3. It might or might not include a data cleanup tool (like Pivot Tables in Excel). It's a hobby project. Will this conflict with your plans for your datatables module?

[bernhard]

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

[adrian]

3 hours ago, bernhard said:

'mydate' => 'SELECT YEAR(data) FROM field_mydate WHERE pages_id = p.id',

Could this be abstracted to something like this:

'YEAR(mydate)' => 'year'

This would result in a "year" key in the returned array/object that returns the YEAR from the mydate field.

[bernhard]

29 minutes ago, adrian said:

Could this be abstracted to something like this:

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

[adrian]

5 minutes ago, bernhard said:

Not sure how often one would need this short syntax?

But you know SQL - I am trying to think of the devs that don't and are used to the PW API. Remember you not only need to know SQL, but you also need a good understanding of the PW database tables structure with the names of the tables and that content is stored in the "data" field.

To this end, I would suggest:

'myfield' => 'YEAR()'

I'd love to see that converted to:

SELECT YEAR(data) AS year FROM field_myfield WHERE pages_id = p.id AS myfield

so take the name of the function and lowercase it and return it as "year" for the key/property in the array/object.

I think this could be really useful.

[bernhard]

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.

[adrian]

3 minutes ago, bernhard said:

I'm still hesitant about how often that would be needed

I think it would be used quite often in a datatables type application, but I also am not sure about just how far to go in terms of getting away from SQL which is already very effective. I guess my main goal is to remove the need to understand the PW table/field structure/relationships, but you have put a lot more thought into this than I, so feel free to ignore my ramblings