Open cyberhobo opened 12 years ago
Yes, server-loaded data would be a very helpful feature!
I second that
@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.
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.
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" reply@reply.github.com:
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
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:
// 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];
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
@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?
How is the progress?
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(siteid) 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(siteid) 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 ?
I've got it working. http://datatables.net/forums/discussion/11991#Item_1
@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.
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:
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
p.s. Oops, didn't mean to link to issues
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" notifications@github.com Aan: "cyberhobo/ColumnFilterWidgets" ColumnFilterWidgets@noreply.github.com Cc: "TrueOsiris" tim.chaubet@pandora.be 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 .
Suggested here: http://datatables.net/forums/discussion/comment/26755#Comment_26755