[deprecated] RockGrid - powerful, performant and beautiful data listing tool

[Beluga]

Spoiler

 

On 8/25/2018 at 10:40 PM, dragan said:

I tried it without output buffering and it works normally.

Where is this line 110 ob_start() exactly sitting? Do you use a prepend file? Is it in your basic_page.php template?

Is 

<div><?php $page->rockgrid; ?></div>

right after ob_start() ?

XAMPP here on Windows 8 and PHP 7.1.9, PW 3.0.106

Here: https://gitlab.com/baumrock/FieldtypeRockGrid/blob/master/InputfieldRockGrid.module.php#L110

I created a separate topic for my issue so as not to pollute the module topic.

 

 

[Beluga]

Spoiler

 

On 8/25/2018 at 10:48 PM, dragan said:

I tried it out now like this:

<div id="rockgriddemo">
  <?php
  $grid = $page->rockgriddemo;
  ob_start();
  echo $grid;
  ob_end_flush();
  ?>
</div>

And everything still works normally. But I haven't used output buffering in a long long time. Not even sure I am using it correctly...

Whoa! This solves the problem for me! Thanks for the unexpected solution ?

I hope some PHP guru can explain what is going on here. In any case, either the inputfield module or the documentation needs to be adjusted.

 

 

[Robin S]

8 hours ago, Beluga said:

<div><?php $page->rockgrid; ?></div>

inside the body

Maybe you are just missing an echo?

<div><?php echo $page->rockgrid; ?></div>

 

[Beluga]

7 hours ago, Robin S said:

Maybe you are just missing an echo?

<div><?php echo $page->rockgrid; ?></div>

 

Actually, this was it ?

I can't believe I wasted 15 hours on this due to a missing echo.

[bernhard]

7 hours ago, Beluga said:

I can't believe I wasted 15 hours on this due to a missing echo.

Don't you use Tracy Debugger? You could have easily done a bd($page->rockgrid) or d($page->rockgrid) and should have seen and been able to debug it quickly.

PS: Even if you lost 15 hours - the module took many many more to develop so you are still saving loads of hours ? 

[dragan]

I set up a simple RockGrid example, but see strange things in the backend, see screen capture and JS console errors (ignore the first error). Any idea what could cause this?

 

[dragan]

Another oddity: I get sometimes 3-4 times the same page listed. Here, instead of showing 1060 pages, I get 2162:

My setup:

// example for template "project"
document.addEventListener('RockGridItemBeforeInit', function(e) {
	if(e.target.id != 'RockGridItem_project') return;
	var grid = RockGrid.getGrid(e.target.id);

	var col = grid.getColDef('vertec');
	col.headerName = 'Vertec';

	var col = grid.getColDef('title');
	col.headerName = "Titel";

	var col = grid.getColDef('client_name');
	col.headerName = "Kunde";

  var col = grid.getColDef('year');
  col.headerName = "Erscheinungsjahr";

  var col = grid.getColDef('service');
  col.headerName = "Dienstleistung";

  var col = grid.getColDef('product');
  col.headerName = "Worum geht's?";

  var col = grid.getColDef('project_desc_short');
  col.headerName = "Referenz-Beschreibung";

	// set fixed column width
	var col = grid.getColDef('created');
	col.headerName = grid.js.created;
	col.width = 150;
	col.suppressSizeToFit = true;

	// add coldef plugins (shortcuts for dates, numbers, etc)
	grid.addColDefPlugins({
		created: {name: 'date', format: 'DD.MM.YYYY HH:mm:ss'},
	});
});

document.addEventListener('RockGridItemAfterInit', function(e) {
	if(e.target.id != 'RockGridItem_transactions') return;
	var col;
	var colDef;
	var grid = RockGrid.getGrid(e.target.id);

	// hide one column
	grid.columnApi().setColumnVisible('myhiddencolumn', false);
});

<?php namespace ProcessWire;

// optional: load external asset
$this->rg->assets->add($this->config->paths->siteModules . 'FieldtypeRockGrid/lib/moment.min.js');

// set data for this grid via rockfinder
$this->setData(new RockFinder("template=project, parent=1041, sort=-created, include=all", [
    'title',
    'client_name',
    'vertec',
    'year',
    'service',
    'product',
    'project_desc_short',
    'created',
]));

Looking at the debug infos in getSQL:

SELECT
    `rockfinder`.*
FROM
    /* original pw query */
    (SELECT pages.id 
    FROM `pages` 
    WHERE (pages.templates_id=44) 
    AND (pages.parent_id=1041) 
    GROUP BY pages.id 
    ORDER BY pages.created DESC ) as `pwfinder`

/* rockfinder */
LEFT JOIN (    SELECT
      `pages`.`id` AS `id`,
      `title`.`title` AS `title`,
      `client_name`.`client_name` AS `client_name`,
      `vertec`.`vertec` AS `vertec`,
      `year`.`year` AS `year`,
      `service`.`service` AS `service`,
      `product`.`product` AS `product`,
      `project_desc_short`.`project_desc_short` AS `project_desc_short`,
      `created`.`created` AS `created`
    FROM
      `pages`
    
    /* --- join title --- */
    LEFT JOIN (SELECT
      `pages_id` AS `pageid`,
      `title`.`data` AS `title`
    FROM `field_title` AS `title`) AS `title` ON `title`.`pageid` = `pages`.`id`
    /* --- end title --- */
    
    
    /* --- join client_name --- */
    LEFT JOIN (SELECT
      `pages_id` AS `pageid`,
      `client_name`.`data` AS `client_name`
    FROM `field_client_name` AS `client_name`) AS `client_name` ON `client_name`.`pageid` = `pages`.`id`
    /* --- end client_name --- */
    
    
    /* --- join vertec --- */
    LEFT JOIN (SELECT
      `pages_id` AS `pageid`,
      `vertec`.`data` AS `vertec`
    FROM `field_vertec` AS `vertec`) AS `vertec` ON `vertec`.`pageid` = `pages`.`id`
    /* --- end vertec --- */
    
    
    /* --- join year --- */
    LEFT JOIN (SELECT
      `pages_id` AS `pageid`,
      `year`.`data` AS `year`
    FROM `field_year` AS `year`) AS `year` ON `year`.`pageid` = `pages`.`id`
    /* --- end year --- */
    
    
    /* --- join service --- */
    LEFT JOIN (SELECT
      `service`.`pages_id` AS `pageid`,
     `service`.`data` AS `service`
    FROM `field_service` AS `service`
    GROUP BY `service`.`pages_id`, `service`.`data`) AS `service` ON `service`.`pageid` = `pages`.`id`
    /* --- end service --- */
    
    
    /* --- join product --- */
    LEFT JOIN (SELECT
      `product`.`pages_id` AS `pageid`,
     `product`.`data` AS `product`
    FROM `field_product` AS `product`
    GROUP BY `product`.`pages_id`, `product`.`data`) AS `product` ON `product`.`pageid` = `pages`.`id`
    /* --- end product --- */
    
    
    /* --- join project_desc_short --- */
    LEFT JOIN (SELECT
      `pages_id` AS `pageid`,
      `project_desc_short`.`data` AS `project_desc_short`
    FROM `field_project_desc_short` AS `project_desc_short`) AS `project_desc_short` ON `project_desc_short`.`pageid` = `pages`.`id`
    /* --- end project_desc_short --- */
    
    
    /* --- join created --- */
    LEFT JOIN (SELECT `id` AS `pageid`, `created` FROM `pages` AS `created`) AS `created` ON `created`.`pageid` = `pages`.`id`
    /* --- end created --- */
    
) AS `rockfinder` ON `pwfinder`.`id` = `rockfinder`.`id`
/* end rockfinder */

imho, I think this is wrong:

    /* --- join title --- */
    LEFT JOIN (SELECT
      `pages_id` AS `pageid`,
      `title`.`data` AS `title`
    FROM `field_title` AS `title`) AS `title` ON `title`.`pageid` = `pages`.`id`
    /* --- end title --- */

Well, or any other part of the whole SQL - do you assume every title is necessarily unique? The only unique thing is the page id. Did you run into a similar wrong output, or do I have to adjust my query? (selector)

[Beluga]

I should have just used my brain. Luckily I am not prone to alcoholism, so this public embarrassment will not lead to pathological results.

Anyway, let's pretend nothing happened last week and move on to the next topic. I noticed the ag-Grid demo has a cool filter, which allows us to use multiple strings. However, when used with RockGrid/PW, it breaks, if the field values include null ones. Long story short, I figured the most inexpensive change perf-wise would be telling PDO to treat nulls as strings. I did the change in RockFinder. Perhaps it would be unwise to include in upstream as some users would expect nulls to stay nulls.

The change in RockFinder.module.php:

public function getObjects($array = null) {
  $timer = $this->timer('getObjects');
  try {
    $this->database->setAttribute(\PDO::ATTR_ORACLE_NULLS, \PDO::NULL_TO_STRING);
    $results = $this->database->query($this->getSql());
    $objects = $results->fetchAll($array ? \PDO::FETCH_ASSOC : \PDO::FETCH_OBJ);
  }

In RockGrid code, there is also a possibility to use sql query as datasource, but I don't see it in the interface. Anyway, in case one would need to use it somehow:

InputfieldRockGrid.module.php:

// sql query as datasource
$this->database->setAttribute(\PDO::ATTR_ORACLE_NULLS, \PDO::NULL_TO_STRING);
$results = $this->database->query($sql);
return $results->fetchAll(\PDO::FETCH_OBJ);

Edit: later I noticed that dealing with the nulls in JS does not seem to affect perf even with a big data set, so I modified the filter example below to just do if (value === null) value = '';

Using the filter from ag-Grid demo in your .js:

document.addEventListener('RockGridItemBeforeInit', function(e) {
  if(e.target.id != 'RockGridItem_rockgrid') return;
  var grid = RockGrid.getGrid(e.target.id);

  var col = grid.getColDef('myfield');
  col.headerName = grid.js.myfield;
  col.filter = PersonFilter;
  col.floatingFilterComponent = PersonFloatingFilterComponent;

});

document.addEventListener('RockGridItemAfterInit', function(e) {
  if(e.target.id != 'RockGridItem_rockgrid') return;
  var col;
  var colDef;
  var grid = RockGrid.getGrid(e.target.id);  
});

function PersonFilter() {
}

PersonFilter.prototype.init = function (params) {
    this.valueGetter = params.valueGetter;
    this.filterText = null;
    this.params = params;    
    this.setupGui();
};

// not called by ag-Grid, just for us to help setup
PersonFilter.prototype.setupGui = function () {
    this.gui = document.createElement('div');
    this.gui.innerHTML =
        '<div style="padding: 4px;">' +
        '<div><input style="margin: 4px 0px 4px 0px;" type="text" id="filterText" placeholder="Multi-string search..."/></div>' +
        '</div>';

    var that = this;
    this.onFilterChanged = function () {
        that.extractFilterText();
        that.params.filterChangedCallback();        
    };

    this.eFilterText = this.gui.querySelector('#filterText');
    this.eFilterText.addEventListener("input", this.onFilterChanged);
};

PersonFilter.prototype.extractFilterText = function () {
    this.filterText = this.eFilterText.value;
};

PersonFilter.prototype.getGui = function () {
    return this.gui;
};

PersonFilter.prototype.doesFilterPass = function (params) {
    // make sure each word passes separately, ie search for firstname, lastname
    var passed = true;
    var valueGetter = this.valueGetter;
    
    this.filterText.toLowerCase().split(" ").forEach(function (filterWord) {
        var value = valueGetter(params);
        if (value === null) value = '';
        if (value.toString().toLowerCase().indexOf(filterWord) < 0) {
            passed = false;
        }
    });

    return passed;
};

PersonFilter.prototype.isFilterActive = function () {
    var isActive = this.filterText !== null && this.filterText !== undefined && this.filterText !== '';
    return isActive;
};

PersonFilter.prototype.getModelAsString = function (model) {
    return model ? model : '';
};

PersonFilter.prototype.getModel = function () {
    return this.eFilterText.value;
};

// lazy, the example doesn't use setModel()
PersonFilter.prototype.setModel = function (model) {
    this.eFilterText.value = model;
    this.extractFilterText();
};

PersonFilter.prototype.destroy = function () {
    this.eFilterText.removeEventListener("input", this.onFilterChanged);
};

function PersonFloatingFilterComponent() { }

PersonFloatingFilterComponent.prototype.init = function (params) {
    this.params = params;
    this.eGui = document.createElement('input');
    var eGui = this.eGui;
    this.changeEventListener = function () {
        params.onFloatingFilterChanged(eGui.value);
    };
    this.eGui.addEventListener('input', this.changeEventListener);
};

PersonFloatingFilterComponent.prototype.getGui = function () {
    return this.eGui;
};

PersonFloatingFilterComponent.prototype.onParentModelChanged = function (model) {
    // add in child, one for each flat
    if (model) {
        this.eGui.value = model;
    } else {
        this.eGui.value = '';
    }
};

PersonFloatingFilterComponent.prototype.destroy = function () {
    this.eGui.removeEventListener('input', this.changeEventListener);
};

 

[Beluga]

@dragan For me those blinking scrollbars and pagination occur reliably with a field height of 520 (Edit fields - Details).

[dragan]

The blinking is one thing, but why does it also change the number of results (and hence - pages)? I've set my window to height 600px.

[Beluga]

Another topic: are you able to hide the id column? I tried with the methods in the readme, but I can't make it go away.

[bernhard]

9 hours ago, dragan said:

I set up a simple RockGrid example, but see strange things in the backend, see screen capture and JS console errors (ignore the first error). Any idea what could cause this?

This happens when you have a grid that tries to show more rows, then adds scrollbars and therefore does not have enough space to show those rows and removes one. Then it tries the same thing again. You can set your grid to a fixed amount of rows. The number of rows in your case do not change. It's only the number of pages that changes (and that's correct of course, if you add/remove one row you'll end up with different page numbers).

 

9 hours ago, dragan said:

Another oddity: I get sometimes 3-4 times the same page listed. Here, instead of showing 1060 pages, I get 2162:

This can happen if you have page fields or repeaters with multiple items. The RockFinder might return those values as joined results with each referenced value in a separate row. Analyze your SQL or your RockFinder and the result itself to see where this comes from. You can comment out some fields of the RockFinder then you see which field causes the number of results to increase to more than you'd expect.

You can either create a different SQL (see the RockFinder docs, or - sorry - look into the code). Another way to prevent such situations is to create a new hidden field that gets populated via saveReady hook and holds the information you need. Eg you create a field "linked_pages" that holds an array of linked pages, eg

[{id:123,title:"demo page1"},{id:456,title:"demo page2"}]

You can then use a cellRenderer to show this information as you want (eg with icons, as comma separated list, as number of linked pages etc.). It might sound complex but it totally makes sense when you build more advanced grids.

Having said that, that's how it works right now. It's not perfect for sure. If you have any suggestions for improvement let me know.

 

9 hours ago, Beluga said:

However, when used with RockGrid/PW, it breaks, if the field values include null ones.

In that case just setup a valueGetter and make sure you return a proper value for all cells. This is how you make sure it is a number (for calculating sums/avg etc):

document.addEventListener('RockGridItemBeforeInit', function(e) {
  if(e.target.id != 'RockGridItem_rockgrid') return;
  var grid = RockGrid.getGrid(e.target.id);

  var col = grid.getColDef('yourcolumn');
  col.valueGetter = function(params) {
    if(typeof params.data == 'undefined') return;
    var val = params.data[colDef.field];
    return val*1; // make sure the value is a number and not a string
  }
});

 

7 hours ago, Beluga said:

Another topic: are you able to hide the id column? I tried with the methods in the readme, but I can't make it go away.

Of course you can hide it: https://www.ag-grid.com/javascript-grid-column-definitions/

document.addEventListener('RockGridItemBeforeInit', function(e) {
  if(e.target.id != 'RockGridItem_rockgrid') return;
  var grid = RockGrid.getGrid(e.target.id);

  var col = grid.getColDef('id');
  col.hide = true;

  var col = grid.getColDef('title');
  col.headerName = 'MyDemoTitle';
});

 

[Beluga]

Cool, thanks for the hiding tip!

Hmm, now that I tested it, changing null to empty in JS does not seem to affect perf in any way. I actually did a quick JS flame graph comparison in Firefox and the relevant call took the same amount of milliseconds either way. So without any PDO hackery, the rockgrid.js PersonFilter.prototype.doesFilterPass in my previous example can be modified like this:

    this.filterText.toLowerCase().split(" ").forEach(function (filterWord) {
        var value = valueGetter(params);
        if (value === null) value = '';
        if (value.toString().toLowerCase().indexOf(filterWord) < 0) {
            passed = false;
        }
    });

Btw. has anyone tried this: https://www.ag-grid.com/javascript-grid-row-height/#auto-row-height

I tested it, but the result was just huge CPU chewing for a while (for a large data set), but row height did not adapt for cells with overflowing content. So simply col.autoHeight = true;

[mel47]

6 hours ago, bernhard said:

This can happen if you have page fields or repeaters with multiple items. The RockFinder might return those values as joined results with each referenced value in a separate row. Analyze your SQL or your RockFinder and the result itself to see where this comes from. You can comment out some fields of the RockFinder then you see which field causes the number of results to increase to more than you'd expect. 

Hi. I'm searching since a couple of days why I get this behavior. I follow your example for pages but it never display with separator (in my picture id=1284 or 1286 or 1290). I have no idea why, maybe an expert eye could spot something.

$finder = new \ProcessWire\RockFinder('template=activite', ['title']); //really simple example
$field =$finder->addField('discipline', ['title']);  
$field->separator = "|";
return $finder->getSQL();

 

SELECT
    `rockfinder`.*
FROM
    /* original pw query */
    (SELECT pages.id 
    FROM `pages` 
    WHERE (pages.templates_id=58) 
    AND (pages.status<1024) 
    GROUP BY pages.id ) as `pwfinder`

/* rockfinder */
LEFT JOIN (    SELECT
      `pages`.`id` AS `id`,
      `title`.`title` AS `title`,
      `discipline`.`discipline` AS `discipline`,
      `discipline`.`title` AS `discipline:title`
    FROM
      `pages`
    
    /* --- join title --- */
    LEFT JOIN (SELECT
      `pages_id` AS `pageid`,
      `title`.`data` AS `title`
    FROM `field_title` AS `title`) AS `title` ON `title`.`pageid` = `pages`.`id`
    /* --- end title --- */
    
    
    /* --- join discipline --- */
    LEFT JOIN (SELECT
      `discipline`.`pages_id` AS `pageid`,
     `discipline`.`data` AS `discipline`,
      GROUP_CONCAT(`title`.`data` ORDER BY `discipline`.`sort` SEPARATOR ',') AS `title`
    FROM `field_discipline` AS `discipline`
    LEFT JOIN `field_title` AS `title` ON `title`.`pages_id` = `discipline`.`data`
    GROUP BY `discipline`.`pages_id`, `discipline`.`data`) AS `discipline` ON `discipline`.`pageid` = `pages`.`id`
    /* --- end discipline --- */
    
) AS `rockfinder` ON `pwfinder`.`id` = `rockfinder`.`id`
/* end rockfinder */

Thanks!!

[bernhard]

thanks @mel47 that is a bug! I'll fix it as soon as possible. then also your problem @dragan should be gone ? 

[bernhard]

hey @mel47 and @dragan please try the latest version (v1.0.8)

[dragan]

Well, over here, when I try to open a page for editing, it never gets loaded. It just keeps on loading.... forever.

PW logs show nothing, same goes for TD.

[bernhard]

1 hour ago, dragan said:

Well, over here, when I try to open a page for editing, it never gets loaded. It just keeps on loading.... forever.

I'm quite sure that this has nothing to do with either RockGrid or RockFinder. Please try a clean install and report back if you have any problems. Thanks.

[mel47]

@bernhard Thanks!!! I should have written before... ? Anyway, I can continue my exploration!

[bernhard]

@Beluga working on filters right now and just saw that your example could also be done with the quickfilter: https://www.ag-grid.com/javascript-grid-filter-quick/

maybe that's interesting for you?

[dotnetic]

Hey @bernhard. You should change the link to the repo in your first post to https://gitlab.com/baumrock/FieldtypeRockGrid as the URL has changed.

Also, is the information that this module is still an alpha correct?

[bernhard]

Hi @jmartsch thanks for the hint.

Regarding the alpha state: Yes. Still alpha. Whatever that means. I'm using it on several sites in production but I'm still developing things and sometimes change some concepts, so there might be breaking changes in the future. That's why it is alpha and I don't plan to change that, but it's working great for all my scenarios and it's already a huge step forward compared to the old datatables module/library ? 

[Beluga]

On 9/4/2018 at 7:19 PM, bernhard said:

@Beluga working on filters right now and just saw that your example could also be done with the quickfilter: https://www.ag-grid.com/javascript-grid-filter-quick/

maybe that's interesting for you?

Now that I looked into it, it does not seem to do what I need. The example has a separate field that filters all the columns. I couldn't figure out how to make it work so it would act as a "normal" column filter. It seems simpler to just continue using the PersonFilter thing.

[bernhard]

Ah, ok. Didn't know what you need exactly and thought I'd share it with you. I'll have an update filter-wise for you (and of course everybody else) soon on the dev-branch ? 

[dotnetic]

Hey Bernhard, I get an error that moment.js could not be loaded.

Then I found a date.md file which says 

Quote

You need to include `moment.js` to your assets:
```php
$this->rg->assets->add($this->config->paths->siteModules . 'FieldtypeRockGrid/lib/moment.min.js');
```

short note: the name of the module in this file is still RockGrid instead of FieldtypeRockGrid.

So I tried to do this, but now I get an error `Call to a member function add() on null`

What am I doing wrong? Here is my code. I use RockGrid inside of a module:

 $f = $this->modules->get('InputfieldRockGrid');
    $f->rg->assets->add($this->config->paths->siteModules . 'FieldtypeRockGrid/lib/moment.min.js');
    $f->name = 'stats';
    $f->themeBorder = 'none';
    $f->height = 0;
    $f->pageSize = 50; // set initial pagination to 25 rows

    $finder = new RockFinder('template=stelle', ['title', 'created']);
    $field = $finder->addField('mitarbeiterId', ['vorname', 'nachname']);

// $sql = $finder->getSQL();
// $finder->sql = "SELECT id, SUM(value) AS revenue, DATE_FORMAT(date, '%Y-%m') AS dategroup FROM ($sql) AS tmp GROUP BY dategroup";
// d($finder->getObjects());

    $f->setData($finder);

    return $f->render();