Work with server-loaded data

[cyberhobo]

Suggested here: http://datatables.net/forums/discussion/comment/26755#Comment_26755

[arbyter]

Yes, server-loaded data would be a very helpful feature!

[aphofstede]

I second that

[mbdesign]

@cyberhobo

can you built a server-loaded data script for me. I try to do that but no chance :(

I also would like to Donate this feature.

[cyberhobo]

Thanks for the offer @mbdesign. I'm not available for hire right now, but if you email me I can get back to you.

Does anyone who wants this feature have server loaded data working without ColumnFilterWidgets? It would be helpful to know what goes wrong when ColumnFilterWidgets is added.

[mbdesign]

I let you know if ill be back in Office the next days.

Thanks in advance

Gruß Markus Brandt

Via Commodore C64 - Exchange V1.0

Am 05.07.2012 um 16:20 schrieb "Dylan Kuhn" [email protected]:

Thanks for the offer @mbdesign. I'm not available for hire right now, but if you email me I can get back to you.

Does anyone who wants this feature have server loaded data working without ColumnFilterWidgets? It would be helpful to know what goes wrong when ColumnFilterWidgets is added.

---

Reply to this email directly or view it on GitHub: https://github.com/cyberhobo/ColumnFilterWidgets/issues/2#issuecomment-6779757

[venzy]

If by server-loaded data you mean just the standard table data coming back from the server (via ajax), yes I have it partially working. There are two issues that I see so far:

• Quick bugfix - This may also impact non-ajax tables (i.e. just all served up at once) - the version of fnGetColumnData() incorporated into ColumnFilterWidgets.js is not quite up to date, and is intolerant of something to do with the way I'm using mDataProp or bVisible etc. If you take the newer version from http://datatables.net/plug-ins/api, the key change is:

            // New way - more forgiving of hidden vs visible column numbering
            // or aaData member naming or something
            var sValue = this.fnGetData(iRow, iColumn);
            // Old way
            // var aData = this.fnGetData(iRow);
            // var sValue = aData[iColumn];

• Biggest issue - fnGetColumnData() itself is not really appropriate for paginated, ajax-fetched data (I think). If you're only getting one page at a time, there may be many more possible values sitting in other pages on the server (so to speak). Or the desire to present a fixed set of values to select from. There would be a non-trivial bit of rework involved:
  • have the server return some extra data about allowed filter criteria for each column
  • possibly have the server do the narrowing down of the allowed criteria based on current criteria (if desried - in my case I don't actually want this to be done)
  • get ColumnFilterWidgets to work with the above.

[aphofstede]

Hi Venzy,

I finally went along and implemented my own system for filtering in the meantime. I (naturally) came across many of the issues you describe.

The extra data problem you describe I solved by defining the filters server-side. Each widget takes the same construction arguments; so you can define each widget with either a fixed set of options, or an Ajax call pretty easily.

The filters themselves are Javascript widgets that get constructed with meta-data while rendering the page's view. It's currently totally separate from ColumnFilterWidgets and could use some more tweaking (possibly supporting the JQuery Widget library) but if you're interested I can open up the code somewhere so you can have a look-see.

Cheers, Alex

[cyberhobo]

@venzy thanks for mentioning fnGetColumnData updates, I started issue #15 for that

@aphofstede it does sound like the implementation might be significantly different enough to warrant a separate plug-in - maybe you could kick that off?

[vegarda]

How is the progress?

[TrueOsiris]

You'll need to prefetch a distinct set of column data so data from all pages is in the dropdowns. I made a working query to do this (which is rather fast) depending on several php variables : $query (which is the base table in query form) $excludeWidgets (which is an array of to-be-excluded columns) $columns (which is an array of the column names used in DataTables) The QueryDatabase function obviously is defined elsewhere. it returns the data as a php array.

    $nc=count($columns);
$r='<script type="text/javascript">';

// get unique column content in order to set ColumnFilterWidgets
$s = "with s as ( $query ) select * from ( select ";
for ($i=0;$i<$nc;$i++) {
    if ($excludeWidgets!="none") {
        if (!(in_array($i,$excludeWidgets))) {
            $s.= " case when row_number() over (partition by ".$columns[$i]." order by ".$columns[$i].") = 1 then ".$columns[$i]." else null end as ".$columns[$i].",";
        }
    } else {
        $s.= " case when row_number() over (partition by ".$columns[$i]." order by ".$columns[$i].") = 1 then ".$columns[$i]." else null end as ".$columns[$i].",";
    }
}
if (substr($s, -1)==',') {
    $s=substr($s, 0, -1);
}
$s.= " from s ) t where ";
for ($i=0;$i<$nc;$i++) {
    if ($excludeWidgets!="none") {
        if (!(in_array($i,$excludeWidgets))) {
            $s.= " ".$columns[$i]." is not null or";
        }
    } else {
        $s.= " ".$columns[$i]." is not null or";
    }
}
if (substr($s, -2)=='or') {
    $s=substr($s, 0, -2);
}
global $qhcon;
$cfw_ray=QueryDatabase($qhcon,$s);
$cfw_rno=count($cfw_ray);
$cfw_cno=count($cfw_ray[0]);
$cfw=array();
for ($i=0;$i<$cfw_rno;$i++) {
    for ($j=0;$j<$cfw_cno;$j++) {
        if ($cfw_ray[$i][$j]!=null) {
            $cfw[$j][]=utf8_encode($cfw_ray[$i][$j]);
        }
    }
}
// build jscript array
$r.='var DropdownData = $.parseJSON('.json_encode($cfw).');';

This query returns

with s as ( select site_id , (site_id + ' - ' + ISNULL(descr,'') + ' ' + ISNULL(city,'') + ' (' + country_id + ')') as descr , country_id from site WHERE country_id IN ('BEL') AND UPPER(SUBSTRING(LOWER(site_id),1,3)) IN ('BEL') AND LOWER(site_id) LIKE '[a-z][a-z][a-z][_]%' ) select * from ( select case when row_number() over (partition by site_id order by site_id) = 1 then site_id else null end as site_id, case when row_number() over (partition by descr order by descr) = 1 then descr else null end as descr, case when row_number() over (partition by country_id order by country_id) = 1 then country_id else null end as country_id from s ) t where site_id is not null or descr is not null or country_id is not null

when my base table =

select site_id , (site_id + ' - ' + ISNULL(descr,'') + ' ' + ISNULL(city,'') + ' (' + country_id + ')') as descr , country_id from site WHERE country_id IN ('BEL') AND UPPER(SUBSTRING(LOWER(site_id),1,3)) IN ('BEL') AND LOWER(site_id) LIKE '[a-z][a-z][a-z][_]%'

The lower part of the script will rearrange the array to strip the nulls.

This gives me all Dropdown-data, but I don't now how to enter the returned data into ColumnFilterWidgets. I'm a newbie in jQuery.

Could you explain how to custom-fill the Widgets ?

[TrueOsiris]

I've got it working. http://datatables.net/forums/discussion/11991#Item_1

[mighty55]

@TrueOsiris do you or anyone else have this working with PHP and SQL? I can get the information into the filter buttons but when searching the rsult is always nothing.

[timothyjoh]

We need this too. ColumnFilterWidgets works great with our server-side pagination, and the filters work, querying the server too. The ONLY problem is what is listed above, the drop downs don't populate with all the data needed.

I'm looking into the following possibilities: #1. passing in arrays to the oColumnFilterWidgets options on initial page load, where the data is pulled from the database and written into a JS array. This other Datatables filter plugin does this already http://jquery-datatables-column-filter.googlecode.com/svn/trunk/index.html By using their custom filters: http://jquery-datatables-column-filter.googlecode.com/svn/trunk/customFilters.html

#2. Including the JS arrays for the drop downs inside the JSON response from the Ajax call data tables gets its data from. Not sure where to put it yet, but I will look into it.

#2 is my preference, but may not be practical either. I could use the other plugin which is much more advanced, but this one seems to be a bit better written. So I will attempt it.

p.s. Oops, didn't mean to link to issues

[TrueOsiris]

Hi

Kinda missed this mail in the enormous list of spam I get. Is the question still valid ? (I'm cleaning up once & for all now)

Vriendelijke groeten, cordialement, kind regards,

Tim Chaubet

----- Oorspronkelijk bericht -----

Van: "Jordan Robinson" [email protected] Aan: "cyberhobo/ColumnFilterWidgets" [email protected] Cc: "TrueOsiris" [email protected] Verzonden: Woensdag 16 oktober 2013 02:07:04 Onderwerp: Re: [ColumnFilterWidgets] Work with server-loaded data (#2)

@TrueOsiris do you or anyone else have this working with PHP and SQL? I can get the information into the filter buttons but when searching the rsult is always nothing.

— Reply to this email directly or view it on GitHub .