Preview/Discussion: RockDataTables

[bernhard]

22 minutes ago, adrian said:

but you have put a lot more thought into this than I, so feel free to ignore my ramblings

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!

 

[FrancisChung]

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

2

Yes, a simple tutorial would be a great starting point. If there's enough clamour / demand for it, you could consider doing some concrete implementations based on Chartjs. Exciting stuff.
 

 

[adrian]

5 hours ago, FrancisChung said:

you could consider doing some concrete implementations based on Chartjs.

Rather than chartjs, can I suggest something that outputs SVG (not canvas) and is potentially based on D3js to make it easy to manipulate beyond the charting library's built-in options. SVG gives more options for exporting the charts for use in vector graphics editors.

I admit I haven't done a chart heavy project in a couple of years so I am not fully up to date, but I quite like http://c3js.org/ - they are not the sexiest charts by default, but you can manipulate however you want via CSS and D3js making it incredibly flexible. There might be other good/better options out there now also so definitely worth some research time.

[bernhard]

thanks @adrian very nice library! I've used google charts and chartjs in the past but it seems c3 has some very nice concepts as well. will have a look. @kongondo I guess you are working on a C3 module or are you really talking about D3 itself?

[kongondo]

4 minutes ago, bernhard said:

@kongondo I guess you are working on a C3 module or are you really talking about D3 itself?

No. I am using D3 itself. Early on, I researched quite a number of charting libraries (including all the usual suspects). I also looked at C3 (and similar). It is a wrapper around D3 to help you avoid the D3 learning curve. I decided to stick with D3 itself for a number of reasons.

1. I didn't mind the D3 learning curve. In fact, I didn't find it as challenging as some sell it to be. No disrespect to C3 and maybe it is the ProcessWire in me but I wanted to get my hands dirty instead of being on the other side of some other library. Besides, if I was going to learn C3 API, I might as well learn D3.
2. There's thousands of D3 examples with thousands of developers around the world using it. Whatever you want to do, there's a high likelihood that somebody else has already done it; So, there is already an existing solution to your problem .

[adrian]

21 minutes ago, bernhard said:

very nice library!

It really is supercool - so incredibly flexible. I think it's initial look might put some people off, but you have so much control over what is output which is what makes it so powerful - sounds like something else we all love around here 

[adrian]

I agree with @kongondo that D3 isn't really that hard - I think it comes down the how much flexibility you need. I think you have to take the approach that best fits the task - as always.

PS - this was my original D3 type approach to generating SVG and PNG diagrams/charts programmatically: https://pear.php.net/package/Image_Canvas

It was very cool back in the day

[gmclelland]

Came across this article that y'all might like https://dashbouquet.com/blog/frontend-development/9-best-javascript-charting-libraries

[Beluga]

10 hours ago, gmclelland said:

Came across this article that y'all might like https://dashbouquet.com/blog/frontend-development/9-best-javascript-charting-libraries

Note that the Flot library listed here is essentially orphaned.

Pure D3 sounds like a solid choice. I really like where this thread has gone so far I hope I can make use of this and buy bernhard dozens of drinks in the future (his liver better be made of steel).

[WinnieB]

Wow, RockDataTables would be perfect for an upcoming project, and for where I'm working in general (dashboards collating info from diverse sources via external APIs). Please keep us updated, I would definitely pay a reasonable fee for a Pro module!

[theo]

@bernhard:

This is great!

Is there a more complete version of this function somewhere? I mean more supported Fieldtypes "and stuff"?

Thank you.

[bernhard]

It's still the version linked above. I only added the comment for the early exit if no pages are found.

16 hours ago, theo said:

I mean more supported Fieldtypes "and stuff"?

Which fieldtypes do you need support for? It's very easy to add your own Fieldtypes to the switch statement. Any contributions are welcome. I'll start working on the module in the next days and I can start with that function as it is very important for the module. Though, as I said, it's already very easy to modify it to your needs and you don't have to wait for my version..

[theo]

6 minutes ago, bernhard said:

Which fieldtypes do you need support for? It's very easy to add your own Fieldtypes to the switch statement. Any contributions are welcome. I'll start working on the module in the next days and I can start with that function as it is very important for the module. Though, as I said, it's already very easy to modify it to your needs and you don't have to wait for my version..

I just wanted to ask if I missed something.

It is not urgent for me, I am still evaluating what's possible with PW to be able to decide if it's the right tool for a certain job.

Are you going to publish your code in the next weeks or so?

Thank you.

[bernhard]

7 minutes ago, theo said:

Are you going to publish your code in the next weeks or so?

I plan to do so, yes.

[theo]

@bernhard: Another thing to keep in mind.

Field Names can contain uppercase characters whereas the database fields are all lowercase, it seems.

So after getting the type with the name possibly containing uppercase chars, the field name has to be converted to lowercase for the DB table "field_xyz"

    foreach ($fields as $f) {
        $field = $this->fields->get($f);
        $f=strtolower($f);

 

[theo]

Playing around with this, I have tested repeaters and there is some code to get titles in addition to the index from FieldtypeOptions  (Not sure if you like this).

Just sharing what I have.

$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;
    // 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;
        $f = strtolower($f);
        // 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:
            case $fieldtype instanceof FieldtypeCheckbox:
                $sql .= ",\n  (SELECT $data FROM field_$f WHERE pages_id = p.id) AS $f";
                break;
            case $fieldtype instanceof FieldtypePage:
            case $fieldtype instanceof FieldtypeRepeater:
                $sql .= ",\n  (SELECT GROUP_CONCAT($data SEPARATOR ',') FROM field_$f WHERE pages_id = p.id) AS $f";
                break;
            case $fieldtype instanceof FieldtypeFile:
                $sql .= ",\n  (SELECT $data FROM field_$f WHERE pages_id = p.id) AS $f";
                //$sql .= ",\n  (SELECT description FROM field_$f WHERE pages_id = p.id) AS ".$f."_desc";
                break;
            case $fieldtype instanceof FieldtypeOptions:
                $sql .= ",\n  (SELECT $data FROM field_$f WHERE pages_id = p.id) AS $f";
                $sql .= ",\n  (SELECT title FROM fieldtype_options WHERE fields_id=(SELECT id FROM fields WHERE name = '$f') AND option_id=$f) AS " . $f . "_title";
                break;
            default:
                $sql .= ",\n  '$fieldtype not supported' AS $f";
        }
    }

    $pages = $this->pages->findIDs($selector);

    if (count($pages) == 0) {
        $event->return = [];
    } else {
        $sql .= "\nFROM\n  pages AS p";
        $sql .= "\nWHERE\n  p.id IN (" . implode(",", $pages) . ")";
        //echo '<pre>' . $sql . '</pre>';
        $results = $this->database->query($sql);
        $event->return = $results->fetchAll($type);
    }
});

 

Testing with:

foreach ($pages->findArray("template=families, sort=title, limit=5000", array('title'), array()) as $p) {
    foreach ($pages->findArray("parent=" . $p['id'] . ",sort=title, limit=5000", array('title'), array()) as $m) {
        foreach ($pages->findArray("parent=" . $m['id'] . ",sort=title, limit=5000", array('title', 'sku12', 'Sound_Sample'), array()) as $s) {
            echo ($p['title'] . ' ' . $m['title'] . ' ' . $s['title'] . '<br>');
            $repeater = $s['sound_sample'];
            if ($repeater) {
                foreach ($pages->findArray('id=' . str_replace(',', '|', $repeater) . ",limit=5000", array('name', 'Sound_Type_ss', 'Files_ss'), array()) as $r) {
                    echo '<h5>' . $r['files_ss'] . '</h5>';
                    echo '<h5>' . $r['sound_type_ss_title'] . '</h5>';
                }
            }
        }
    }
}

 

[theo]

Playing more...

If you need a fast multi level nav, you can try the code below (using findArray).

Set "has_parent" to the id of the parent page.

The difference to looping through pages -> children is remarkable.

Objects : 794 / Execution time : 0.021355867385864 seconds

 

function buildTree($flat, $pidKey, $idKey = null) {
    $grouped = array();
    foreach ($flat as $sub) {
        $grouped[$sub[$pidKey]][] = $sub;
    }
    $fnBuilder = function($siblings) use (&$fnBuilder, $grouped, $idKey) {
        foreach ($siblings as $k => $sibling) {
            $id = $sibling[$idKey];
            if (isset($grouped[$id])) {
                $sibling['children'] = $fnBuilder($grouped[$id]);
            }
            $siblings[$k] = $sibling;
        }
        return $siblings;
    };
    $tree = $fnBuilder($grouped[$flat[0][$pidKey]]); //!!
    return $tree;
}

$arr = $pages->findArray("has_parent=1029, sort=parent_id, include=all, limit=5000", array('title'), array('id', 'parent_id', 'name'));
$tree = buildTree($arr, 'parent_id', 'id');

foreach ($tree as $p1) {
    echo '<h2>' . $p1['title'] . '</h2>';
    if (isset($p1['children']))
        foreach ($p1['children'] as $p2) {
            echo '<h3>' . $p2['title'] . '</h3>';
            if (isset($p2['children']))
                foreach ($p2['children'] as $p3) {
                    $count++;
                    echo '<a href="../myparent/' . $p1['name'] . '/' . $p2['name'] . '/' . $p3['name'] . '/">';
                    echo '<h6>' . $p3['title'] . '</h6></a>';
                }
        }
}

 

Function buidTree is from here: https://stackoverflow.com/questions/4196157/create-array-tree-from-array-list

@bernhard Am I hijacking your thread?

[bernhard]

8 minutes ago, theo said:

@bernhard Am I hijacking your thread?

Totally fine, I enjoy the discussion (and added this tag to the thread title).

Your example shows that the findArray method would be useful not only for the datatables module but also for other scenarios. I think it would be a good addition to the core (as already mentioned by adrian). I'll work on this next week and make a PR ans see what ryan says.

For reference, here is info regarding the limit for WHERE IN(...): https://stackoverflow.com/questions/4275640/mysql-in-condition-limit

[theo]

Quote

> Totally fine, I enjoy the discussion (and added this tag to the thread title).

Great!

Although it would be a great addition from the community, I am not sure if ryan would like to add it to core.

You lose all the comfort of Processwire.

For example I've written some code last evening to access image-urls and descriptions (snippet, experimental):

case $fieldtype instanceof FieldtypeImage:
  $sql .= ",\n  (SELECT GROUP_CONCAT($data SEPARATOR 0x1D) FROM field_$f WHERE pages_id = p.id) AS $f";
  $sql .= ",\n  (SELECT GROUP_CONCAT(description SEPARATOR 0x1D) FROM field_$f WHERE pages_id = p.id) AS " . $f . "_desc";
break;

foreach ($pages->findArray("id=1, sort=title, limit=1000", array('title', 'images'), array()) as $p) {
 $images = explode(chr(29), $p['images']);
 $images_desc = explode(chr(29), $p['images_desc']);
 $imgs = array_combine($images, $images_desc);
 $langid = $this->user->language->id;
  if ($langid == $languages->getDefault()->id)
      $langid = 0;

 foreach ($imgs as $url => $descr) {
  $ajson = json_decode($descr, true);
  if (isset($ajson[$langid])) $desc=$ajson[$langid]; else $desc="";
  echo "URL=" . $p['id'] . '/' . $url . ", DESCR=" . $desc;
 }
}

 

You see, it works if you know how, but it feels a bit like writing assembler.
But yes, it is fast!

[bernhard]

The method could just be another option for find(), just like findIDS and findMany etc. exist. It will be simple for basic usage and offer the option for custom SQL queries for advanced use. I plan to implement it like this (pseudo):

$pages->findArray('template=foo, sort=bar', [
  'field1',
  'field2',
  'url' => 'SELECT GROUP_CONCAT($data SEPARATOR 0x1D) FROM field_$f WHERE pages_id = p.id) AS $f',
  'desc' => 'SELECT GROUP_CONCAT(description SEPARATOR 0x1D) FROM field_$f WHERE pages_id = p.id) AS $f_desc',
]);

field1 and 2 would be simple, url and desc are an example of advanced use.

[theo]

15 minutes ago, bernhard said:

The method could just be another option for find(), just like findIDS and findMany etc. exist. It will be simple for basic usage and offer the option for custom SQL queries for advanced use. I plan to implement it like this (pseudo):

$pages->findArray('template=foo, sort=bar', [
  'field1',
  'field2',
  'url' => 'SELECT GROUP_CONCAT($data SEPARATOR 0x1D) FROM field_$f WHERE pages_id = p.id) AS $f',
  'desc' => 'SELECT GROUP_CONCAT(description SEPARATOR 0x1D) FROM field_$f WHERE pages_id = p.id) AS $f_desc',
]);

field1 and 2 would be simple, url and desc are an example of advanced use.

Yes, speed must be the first priority here. Load only things you need.

In case of Images, only desc is "advanced use".

"data" is already the filename and url is not in the database.

URL is (pseudo) assets/files/$pageid/$data

But I think we should offer a more convenient way for "description" than writing the select statement yourself.

"description" exists for FieldtypeFile too.

[theo]

@bernhard: Or like this:

 

$this->addHook("Pages::findArray", function($event) {
...
    foreach ($fields as $f) {
        $field_extra = explode(':', $f);
        $f = $field_extra[0];
        $field_extra = array_slice($field_extra, 1);
        $field = $this->fields->get($f);
...
            case $fieldtype instanceof FieldtypeImage:
                $sql .= ",\n  (SELECT GROUP_CONCAT($data SEPARATOR 0x1D) FROM field_$f WHERE pages_id = p.id) AS $f";
                if (in_array('description', $field_extra))
                    $sql .= ",\n  (SELECT GROUP_CONCAT(description SEPARATOR 0x1D) FROM field_$f WHERE pages_id = p.id) AS " . $f . "_description";
                break;
            case $fieldtype instanceof FieldtypeOptions:
                $sql .= ",\n  (SELECT $data FROM field_$f WHERE pages_id = p.id) AS $f";
                if (in_array('title', $field_extra))
                    $sql .= ",\n  (SELECT title FROM fieldtype_options WHERE fields_id=(SELECT id FROM fields WHERE name = '$f') AND option_id=$f) AS " . $f . "_title";
                break;

 

Then request extra data like 'images:description':

foreach ($pages->findArray("id=1, sort=title, limit=1000", ['title', 'images:description'], []) as $p) {

 

[bernhard]

Ok, took some time but now I get your point  And I like it!

This could definitely speed things up and make things a lot easier. Not sure how to implement this properly, though. We have different table structures for some fields. The first and easiest is for a simple textfield:

pages_id | data

then we have image/field fields:

pages_id | data | sort | description | modified | created

then we have option fields:

pages_id | data | sort

 

So if I understood you correctly, you are suggesting something like this:

$pages->findArray('foo=bar', [
  'mytextfield',
  'myimagefield:description',
  'myoptionsfield:title',
]);

Resulting in an output like this:

array (5)
mytextfield => "demo page" (9)
myimagefield => "foo.jpg|bar.jpg" (15)
myimagefield_desc => "my foo image|my bar image" (25)
myoptionsfield => "1|2|4" (5)
myoptionsfield_title => "option 1|option 2|option 4" (26)

Right?

What if we changed this a little bit and used arrays. See the examples below:

$pages->findArray('foo=bar', [
    // examples below
]);

// simple textfield
// request:
    'mytextfield'
// return: data column of this field
    'this is some text'

// image field
// request
    'myimagefield'
// return
    'foo.jpg|bar.jpg'
    
// image field with more requested info
// request
    'myimagefield' => [
        'description',
        'sort',
        'mycustomsql' => 'SELECT ... FROM ...',
    ],
// return
    'myimagefield' => 'foo.jpg|bar.jpg',
    'myimagefield_description' => 'foo desc|bar desc',
    'myimagefield_sort' => '1|2',
    'myimagefield_mycustomsql' => 'x|y'
    
// simple options field
// request
    'myoptionsfield'
// return
    '1|2|4'
    
// options field more info
// request
    'myoptionsfield' => [
        'title'
    ],
// return
    'myoptionsfield' => '1|2|4',
    'myoptionsfield_title' => 'option 1|..2|..4',

// custom sql field
// request
    'mycustomsqlfield' => 'SELECT ... FROM ...',
// return
    'mycustomsqlfield' => 'some return value',
    
// page field
// request
    'mypagefield' => [
        'pages:templates_id',
        'pages:status',
    ],
// return
    'mypagefield' => '1010|1011|1012',
    'mypagefield_templates_id' => '44|45|44',
    'mypagefield_status' => '1|1|8192',

Some notes:

• If a simple string is requested, the "data" column of this table will be returned (or data1234 for multilang fields)
• all concatenated fields MUST be sorted via the sort column in order to have a consistant sort order across multiple array items
• What if image descriptions are empty? Would it still work?
• If a key => value is requested and the "value" is an array we do sub-queries
  • if the subquery has a colon we query this table instead of the current field's table (pages:status instead of mypagefield:status)

 

This should give us a lot of flexibility, I think. For more complex scenarios we will still need to use joins. I've already started working on a proof of concept SQL editor module: 

With both tools in our hands I think it will be quite easy to query all the data we need and it will still be quite simple to use.

@adrian I would be very interested in your opinion about this

[theo]

1 hour ago, bernhard said:

Ok, took some time but now I get your point  And I like it!

This could definitely speed things up and make things a lot easier. Not sure how to implement this properly, though. We have different table structures for some fields. The first and easiest is for a simple textfield:

 

It is easy to make it "all purpose" with the code above.

Just change it to return all the requested values (foreach):

case $fieldtype instanceof FieldtypeImage:
   $sql .= ",\n  (SELECT GROUP_CONCAT($data SEPARATOR 0x1D) FROM field_$f WHERE pages_id = p.id) AS $f";
   foreach ($field_extra as $exfld)
      $sql .= ",\n  (SELECT GROUP_CONCAT($exfld SEPARATOR 0x1D) FROM field_$f WHERE pages_id = p.id) AS " . $f . "_".$exfld;
break;

Query like this:

foreach ($pages->findArray("id=1, sort=title, limit=1000", ['title', 'images:description:modified:created'], ['created']) as $p) {
echo $p['created']; //Page created
echo $p['images_created']; //Images created. Delimited String
echo $p['images_modified']; //Images modified.Delimited String

Created SQL looks like

SELECT p.created, p.id, (SELECT data1012 FROM field_title WHERE pages_id = p.id) AS title, 
(SELECT GROUP_CONCAT(data SEPARATOR 0x1D) FROM field_images WHERE pages_id = p.id) AS images, 
(SELECT GROUP_CONCAT(description SEPARATOR 0x1D) FROM field_images WHERE pages_id = p.id) AS images_description, 
(SELECT GROUP_CONCAT(modified SEPARATOR 0x1D) FROM field_images WHERE pages_id = p.id) AS images_modified, 
(SELECT GROUP_CONCAT(created SEPARATOR 0x1D) FROM field_images WHERE pages_id = p.id) AS images_created FROM pages AS p WHERE p.id IN (1)

Not sure if array or delimited makes a difference.

I don't think this is ever going to be a foolproof tool, but it's great base which you can extend to your taste.

For my purpose (Exporting data to CSV fast) it's almost ready.

[bernhard]

33 minutes ago, theo said:

Not sure if array or delimited makes a difference.

array makes two things possible:

1. add custom sql queries as sub-queries (don't know if that would be useful in some cases? or unnecessary?)
2. add pages:status syntax - this makes a lot of sense to me, because I've come over this problem a lot with tables where you want to know the status of the referenced page. you would need joins for that and using a subquery could make it possible to avoid this complexity. have to make performance tests though.

36 minutes ago, theo said:

I don't think this is ever going to be a foolproof tool

Why not? Or what exactly?

37 minutes ago, theo said:

For my purpose (Exporting CSV Data fast) it's almost ready.

Jep, can imagine that it shines here