ParamQuery grid support forum
General Category => Help for ParamQuery Pro => Topic started by: motoguru on February 17, 2014, 07:25:20 pm
-
Hello again,
There is large and sophisticated search demo in free version. As I use PRO, I want to provide local search method (based on this: http://paramquery.com/pro/demos/filter), but I want to search by multiple columns (selectable) and multiple words. I mean search method would do something like this:
Search phrase (for example): "company 120",
and then search "engine" splits it for two words ("company" and "120") and searches in every column then marks every match in yellow (like in free version search).
I tried to pass multiple filter model details in filterhandler function:
function filterhandler(evt, ui) {
var $toolbar = $grid.find('.pq-toolbar-search'),
value = $toolbar.find(".filterValue").val(),
condition = $toolbar.find(".filterCondition").val();
//debugger;
var filterObject = [{ dataIndx: 0, condition: condition, value: value }, { dataIndx: 1, condition: condition, value: value },{ dataIndx: 2, condition: condition, value: value }];
$grid.pqGrid("filter", {
oper: 'replace',
data: filterObject,
});
}
but it does nothing.
Any solutions for this?
Thanks in advance.
-
Search is different from Filtering. Filter reduces the records to matching records only while search doesn't change the number of records.
In free version
1) search is done directly with help of javascript function string.indexOf
2) column.render callback is used for cell highlighting.
3) selection API is used to move from cell to cell.
If you use PRO' filter API, it would do filtering but not search.
var filterObject = [{ dataIndx: 0, condition: condition, value: value }, { dataIndx: 1, condition: condition, value: value },{ dataIndx: 2, condition: condition, value: value }];
Why are you using dataIndx as integers. Do you have Array data? What's your colModel.
-
I'm doing this because my dataIndx on every arrayData row is an integer (or there is no dataIndx provided). This method works well for me when single filter object (for one column) is provided. Yes, I know search is other than filter in behaviour, but I use filter behaviour for search as I want to show only those records whose matches the filter phrase. So, this is my code:
//colModel (don't look at the listeners, I used them for header filter and it worked well, but I don't want to use header filters at all. I left them here just for possible future cases):
colModelDetailsS = new Array();
colModelDetailsS.push({title:'Number',filter: {
type: 'textbox', condition: 'contain',
listeners: [{
change: function (evt, ui) {
filter(0, $(this).val(),'grid_summary');
}
}]
},
dataType: 'integer', width:45,align:'left'});
colModelDetailsS.push({title:'Title 1',filter: {
type: 'textbox', condition: 'contain',
listeners: [{
change: function (evt, ui) {
filter(1, $(this).val(),'grid_summary');
}
}]
},
dataType: 'string', width:100,align:'left'});
colModelDetailsS.push({title:'Title 2',filter: {
type: 'textbox', condition: 'contain',
listeners: [{
change: function (evt, ui) {
filter(2, $(this).val(),'grid_summary');
}
}]
},
dataType: 'string', width:100,align:'right'});
colModelDetailsS.push({title:'Title 3',filter: {
type: 'textbox', condition: 'contain',
listeners: [{
change: function (evt, ui) {
filter(3, $(this).val(),'grid_summary');
}
}]
},
dataType: 'string', width:100,align:'right'});
colModelDetailsS.push({title:'Title 4',filter: {
type: 'textbox', condition: 'contain',
listeners: [{
change: function (evt, ui) {
filter(4, $(this).val(),'grid_summary');
}
}]
},
dataType: 'string', width:100,align:'right'});
colModelDetailsS.push({title:'Title 5',filter: {
type: 'textbox', condition: 'contain',
listeners: [{
change: function (evt, ui) {
filter(5, $(this).val(),'grid_summary');
}
}]
},
dataType: 'string', width:100,align:'right'});
colModelDetailsS.push({title:'Title 6',filter: {
type: 'textbox', condition: 'contain',
listeners: [{
change: function (evt, ui) {
filter(6, $(this).val(),'grid_summary');
}
}]
},
dataType: 'string', width:100,align:'right'});
colModelDetailsS.push({title:'Title 7',filter: {
type: 'textbox', condition: 'contain',
listeners: [{
change: function (evt, ui) {
filter(7, $(this).val(),'grid_summary');
}
}]
},
dataType: 'string', width:50,align:'right'});
//previous filter method for header filtering - worked well - not important in my case
function filter(dataIndx, value, gridDivElementId) {
$("#" + gridDivElementId).pqGrid("filter", {
data: [{ dataIndx: dataIndx, value: value }]
});
}
//my dataArray:
var arrayDataS=[[0, 'Test 1', ' 290', ' 3', ' 37', ' 428', '8', '30'],
[1, 'Test 2', ' 61', ' 7', ' 8', ' 95', ' 2', '35'],
[2, 'Test 3', '5', '69', '8', '9', '5', '4'],
[3, 'Test 4', ' 90', '50', ' 1', ' 1', '3', '8'],
[4, 'Test 5', '9', '5', '4', '1', '2', '3'],
[5, 'Test 6', '33', '23', '32', '44', '1', '3'],
];
//grid declaration:
var obj = {
dataModel: {
data: arrayDataS,
sorting: "local",
},
colModel: colModelDetailsS,
width: 1000,
height: 400,
title: "test grid",
resizable: false,
flexHeight: false,
freezeCols: 1,
filterModel: { on: true, mode: "OR", header: false }, //turned on, but no header filtering needed
numberCell: { show: true },
editable: false,
selectionModel: { type: 'cell' },
scrollModel: { autoFit: true},
hwrap: false,
wrap: false,
toolbar: {
cls: "pq-toolbar-search",
items: [
{ type: "<span style='margin:5px;'>Search</span>" },
{ type: 'textbox', style: "border:1px solid #aaa;padding:1px 5px;", cls: "filterValue" },
{
type: 'select', style: "margin:0px 5px;", cls: "filterCondition", options: [
{ "begin": "Begins With" },
{ "contain": "Contains" },
{ "notcontain": "Does not contain" },
{ "equal": "Equal To" },
{ "notequal": "Not Equal To" },
{ "empty": "Empty" },
{ "notempty": "Not Empty" },
{ "end": "Ends With" },
{ "less": "Less Than" },
{ "great": "Great Than" }
]
},
//I don't need filterColumn as I want to search by every column which is not hidden
//{
// type: 'select', cls: "filterColumn", options: function (ui) {
// var CM = ui.colModel;
// var opts = [];
// for (var i = 0; i < CM.length; i++) {
// var column = CM[ i ];
// var obj = {};
// obj[column.dataIndx] = column.title;
// opts.push(obj);
// }
// return opts;
// }
//},
{ type: 'separator' },
{ type: 'button', label: 'Filter', icon: 'ui-icon-search', listeners: [{ click: filterhandler }] }
]
},
};
Current filterhandler method is in previous post. It uses
var filterObject = [{ dataIndx: 0, condition: condition, value: value }, { dataIndx: 1, condition: condition, value: value },{ dataIndx: 2, condition: condition, value: value }];
just for test case, but later I want to use it for every column selected for searching, like:
var filterObject = new Array();
for (n in selectedForSearchColumns) {
filterObject.push({ dataIndx: selectedForSearchColumns[n].indx, condition: condition, value: value });
}
and so on. Plus how to, using this filter method, yellowmark phrase in every filtered record passing the match, and how to use multiple words phrase for filtering?
//EDIT:
Changed: filterModel: { on: true, mode: "OR", header: false }, //turned on, but no header filtering needed
and filtering works, let's say. So how to:
using this filter method, yellowmark phrase in every filtered record passing the match, and how to use multiple words phrase for filtering?
-
The source code lost its formatting.
Could you please post your test case as attachment or is it possible for you to share the URL.
Thanks
-
I'm afraid I can't provide live example as it's intranet site. There You have better formatted code.
//colModel (don't look at the listeners, I used them for header filter and it worked well, but I don't want to use header filters at all. I left them here just for possible future cases):
colModelDetailsS = new Array();
colModelDetailsS.push({
title: 'Number', filter: {
type: 'textbox', condition: 'contain',
listeners: [{
change: function (evt, ui) {
filter(0, $(this).val(), 'grid_summary');
}
}]
},
dataType: 'integer', width: 45, align: 'left'
});
colModelDetailsS.push({
title: 'Title 1', filter: {
type: 'textbox', condition: 'contain',
listeners: [{
change: function (evt, ui) {
filter(1, $(this).val(), 'grid_summary');
}
}]
},
dataType: 'string', width: 100, align: 'left'
});
colModelDetailsS.push({
title: 'Title 2', filter: {
type: 'textbox', condition: 'contain',
listeners: [{
change: function (evt, ui) {
filter(2, $(this).val(), 'grid_summary');
}
}]
},
dataType: 'string', width: 100, align: 'right'
});
colModelDetailsS.push({
title: 'Title 3', filter: {
type: 'textbox', condition: 'contain',
listeners: [{
change: function (evt, ui) {
filter(3, $(this).val(), 'grid_summary');
}
}]
},
dataType: 'string', width: 100, align: 'right'
});
colModelDetailsS.push({
title: 'Title 4', filter: {
type: 'textbox', condition: 'contain',
listeners: [{
change: function (evt, ui) {
filter(4, $(this).val(), 'grid_summary');
}
}]
},
dataType: 'string', width: 100, align: 'right'
});
colModelDetailsS.push({
title: 'Title 5', filter: {
type: 'textbox', condition: 'contain',
listeners: [{
change: function (evt, ui) {
filter(5, $(this).val(), 'grid_summary');
}
}]
},
dataType: 'string', width: 100, align: 'right'
});
colModelDetailsS.push({
title: 'Title 6', filter: {
type: 'textbox', condition: 'contain',
listeners: [{
change: function (evt, ui) {
filter(6, $(this).val(), 'grid_summary');
}
}]
},
dataType: 'string', width: 100, align: 'right'
});
colModelDetailsS.push({
title: 'Title 7', filter: {
type: 'textbox', condition: 'contain',
listeners: [{
change: function (evt, ui) {
filter(7, $(this).val(), 'grid_summary');
}
}]
},
dataType: 'string', width: 50, align: 'right'
});
//previous filter method for header filtering - worked well - not important in my case
function filter(dataIndx, value, gridDivElementId) {
$("#" + gridDivElementId).pqGrid("filter", {
data: [{ dataIndx: dataIndx, value: value }]
});
}
//my dataArray:
var arrayDataS = [[0, 'Test 1', ' 290', ' 3', ' 37', ' 428', '8', '30'],
[1, 'Test 2', ' 61', ' 7', ' 8', ' 95', ' 2', '35'],
[2, 'Test 3', '5', '69', '8', '9', '5', '4'],
[3, 'Test 4', ' 90', '50', ' 1', ' 1', '3', '8'],
[4, 'Test 5', '9', '5', '4', '1', '2', '3'],
[5, 'Test 6', '33', '23', '32', '44', '1', '3'],
];
//grid declaration:
var obj = {
dataModel: {
data: arrayDataS,
sorting: "local",
},
colModel: colModelDetailsS,
width: 1000,
height: 400,
title: "test grid",
resizable: false,
flexHeight: false,
freezeCols: 1,
filterModel: { on: true, mode: "OR", header: false }, //turned on, but no header filtering needed
numberCell: { show: true },
editable: false,
selectionModel: { type: 'cell' },
scrollModel: { autoFit: true },
hwrap: false,
wrap: false,
toolbar: {
cls: "pq-toolbar-search",
items: [
{ type: "<span style='margin:5px;'>Search</span>" },
{ type: 'textbox', style: "border:1px solid #aaa;padding:1px 5px;", cls: "filterValue" },
{
type: 'select', style: "margin:0px 5px;", cls: "filterCondition", options: [
{ "begin": "Begins With" },
{ "contain": "Contains" },
{ "notcontain": "Does not contain" },
{ "equal": "Equal To" },
{ "notequal": "Not Equal To" },
{ "empty": "Empty" },
{ "notempty": "Not Empty" },
{ "end": "Ends With" },
{ "less": "Less Than" },
{ "great": "Great Than" }
]
},
//I don't need filterColumn as I want to search by every column which is not hidden
//{
// type: 'select', cls: "filterColumn", options: function (ui) {
// var CM = ui.colModel;
// var opts = [];
// for (var i = 0; i < CM.length; i++) {
// var column = CM[ i ];
// var obj = {};
// obj[column.dataIndx] = column.title;
// opts.push(obj);
// }
// return opts;
// }
//},
{ type: 'separator' },
{ type: 'button', label: 'Filter', icon: 'ui-icon-search', listeners: [{ click: filterhandler }] }
]
},
};
function filterhandler(evt, ui) {
var $toolbar = $grid.find('.pq-toolbar-search'),
value = $toolbar.find(".filterValue").val(),
condition = $toolbar.find(".filterCondition").val();
// dataIndx = $toolbar.find(".filterColumn").val();
// alert(dataIndx);
//debugger;
var filterObject = [{ dataIndx: "0", condition: condition, value: value }, { dataIndx: "1", condition: condition, value: value }, { dataIndx: "2", condition: condition, value: value }];
//alert(filterObject.toSource());
$grid.pqGrid("filter", {
oper: 'replace',
data: filterObject,
});
}
var $grid = $("#grid_summary").pqGrid(obj);
-
Let me simplify the problem...
I'm trying to:
1. filter multiple words separately:
for phrase: "word1 word2" filter when each row contain word1 OR word2,
2. Mark those words yellow when match in every cell one or another of they match
using:
$grid.pqGrid("filter", {
oper: 'replace',
data: filterObject,
});
I even tried to read Your code where the filter query is prepared (lines 3374 to 3376 in dev.js) and tried to modify my query string to use such a listener:
function filterhandler(evt, ui) {
var $toolbar = $grid.find('.pq-toolbar-search'),
value = $toolbar.find(".filterValue").val(),
condition = $toolbar.find(".filterCondition").val();
var allWords = value.split(" ");
var filterObject = new Array();
var word = "";
for (var pId in allWords) {
word=word+allWords[pId];
if (pId != allWords.length - 1) {
word =word + "%' or ==data== like '%";
}
}
for (var clm in colModelDetails) {
filterObject.push({ dataIndx: clm, condition: condition, value: word.replace("==data==",clm) });
}
// var filterObject = [{ dataIndx: "0", condition: condition, value: value }, { dataIndx: "1", condition: condition, value: value },{ dataIndx: "2", condition: condition, value: value }];
alert(filterObject.toSource());
$grid.pqGrid("filter", {
oper: 'replace',
data: filterObject,
});
}
But it doesn't work. Still searching for first, second or no word in phrase :/
======================
UPDATE:
I've found something like (or the same) feature as search in free version in Your code (implemented in PRO: lines 7985-8187 in dev). There is neither demo nor API doc of it. Maybe it could be used for such a filtering I want? I want to hide rows which doesn't match to multiword phrase and for those which match - hightlight found word [or string] (in multiword phrase). In every cell of grid.
Any solution?
-
You can search all the fields using filter API and highlight the matches with help of column.render callback.
I've updated the Filter demo to illustrate multiple field searching and highlighting of matches.
http://paramquery.com/pro/demos/filter_local
Searching for multiple keywords / conditions within a single field is not supported as of 2.0.3
-
Works fine, thank You.
Is there any chance to provide simple extension which could search by multiple word? Using Regex for example? Like word = /word1|word2..../ Or there is any chance to search for word1 in column1, word2 in column2 and so on?
One thing, in functionRender You have a line:
valUpper = val.toUpperCase();
which fails is val is not a String. My solution:
valUpper = safeToString(val).toUpperCase(),
Where safeToString() is:
function safeToString(x) {
switch (typeof x) {
case 'object':
return 'object';
case 'function':
return 'function';
default:
return x + '';
}
}
Regards
-
Thanks for sharing.
There is a 'regex' condition even though it's not mentioned in the API, you may try to use it. You may also split the text and pass it into different column conditions in the filter method.
Please let me know whether regex works for you.
Edit
==========================
'regex' works just like any other condition means single regex can be applied on a single field only.
-
I don't know if I understand You good, but I've changed my filterhandler function to format RegExp properly if 'regex' condition is selected (new filter condition in menu added: { "regexp": "Search phrase" }), and it looks now like:
function filterhandler(evt, ui) {
var $toolbar = $grid.find('.pq-toolbar-search'),
$value = $toolbar.find(".filterValue"),
value = $value.val(),
condition = $toolbar.find(".filterCondition").val(),
dataIndx = $toolbar.find(".filterColumn").val(),
filterObject;
var word = value;
if (condition == 'regexp') {
var spl = word.split(" ");
word = "";
for (var n in spl) {
word += spl[ n ];
if (n != spl.length - 1) {
word += "|";
}
}
word = new RegExp(word);
value = word;
}
if (dataIndx == "") {//search through all fields when no field selected.
filterObject = [];
var CM = $grid.pqGrid("getColModel");
for (var i = 0, len = CM.length; i < len; i++) {
var dataIndx = CM[ i ].dataIndx;
filterObject.push({ dataIndx: dataIndx, condition: condition, value: value });
}
}
else {//search through selected field.
filterObject = [{ dataIndx: dataIndx, condition: condition, value: value }];
}
$grid.pqGrid("filter", {
oper: 'replace',
data: filterObject
});
}
EDIT:
I've found that is not 'regex' but 'regexp' in dev, but still doesn't work for my test phrase.
But it won't work. For example, Giving "test1 test2" phrase gives "/test1|test2/" RegExp which is supposed to filter those strings which contain "test1" or "test2" words. But it won't. Just leaves all rows unchanged.
By the way, this is my solution. If it works - my life is saved :) Any suggestions?
-
Oops sorry!! I also checked it. regex doesn't work. It was not tested enough and it's required usage was not sure, that's why it's not published in API
BTW your code logic looks correct. I wish regex was working.
I would introduce it in 2.0.4
-
All right, thanks for help :)
-
One more question, after removing all filters previously hidden records are shown after those unfiltered. How to properly sort/order records after reverting filter (setting to "" or empty in value field)? I'm writting in this thread because it's related to source code shown above.
-
Records are automatically sorted when filters are applied/removed depending upon value of sortIndx and sortDir.
-
Hmm, according to my filter code (which we used in this thread before):
function filterhandler(evt, ui) {
var $toolbar = $grid.find('.pq-toolbar-search'),
$value = $toolbar.find(".filterValue"),
value = $value.val(),
condition = $toolbar.find(".filterCondition").val(),
dataIndx = $toolbar.find(".filterColumn").val(),
filterObject;
if (dataIndx == "") {//search through all fields when no field selected.
filterObject = [];
var CM = $grid.pqGrid("getColModel");
for (var i = 0, len = CM.length; i < len; i++) {
var dataIndx = CM[ i ].dataIndx;
filterObject.push({ dataIndx: dataIndx, condition: condition, value: value });
}
}
else {//search through selected field.
filterObject = [{ dataIndx: dataIndx, condition: condition, value: value }];
}
$grid.pqGrid("filter", {
oper: 'replace',
data: filterObject
});
}
Which is bind to:
{ type: 'button', label: 'Search', icon: 'ui-icon-search', listeners: [{ click: filterhandler }] },
Let's discuss this part of records:
No | Name | Another column
1 | !1111 | sth else
2. | !!!222| sth else...
4. | aaaaa| sth else...
5. | aabbb| sth else...
6. | ccsssa| sth else...
7. | ddddd| sth else...
When I have no sorting mode enabled (or no column for sorting "clicked" - data came like this from dataArray), then type: "aa" to filter and click "Search" (by column "Name", criteria "contain"), my records are like:
4. | aaaaa| sth else...
5. | aabbb| sth else...
But then, when I erase search field to "", and click Search, my records are:
4. | aaaaa| sth else...
5. | aabbb| sth else...
1 | !1111 | sth else
2. | !!!222| sth else...
6. | ccsssa| sth else...
7. | ddddd| sth else...
instead of:
1 | !1111 | sth else
2. | !!!222| sth else...
4. | aaaaa| sth else...
5. | aabbb| sth else...
6. | ccsssa| sth else...
7. | ddddd| sth else...
As You see previously filtered records are show below those, which filter included in view.
So how to reorder records naturally (to the view they were inserted do initial dataArray)?
Inb4: grid declaration is not changed and it's the same from this thread.
-
If you want to maintain the natural order of the records (without any apparent sorting)
1) keep a hidden column of unique values (may be primary key or numeric values 1,2,3, etc)
2) set the initial sortIndx equal to dataIndx of that hidden column.
-
Got it, thanks :)
-
Hello there, 2.0.4..
Ok then, I get:
TypeError: cd.indexOf is not a function
if (cd.indexOf(value) != -1) {
pqgrid.dev.js (row 9339)
In
function filterhandler(evt, ui) {
var $toolbar = $grid.find('.pq-toolbar-search'),
$value = $toolbar.find(".filterValue"),
value = $value.val(),
condition = $toolbar.find(".filterCondition").val(),
dataIndx = $toolbar.find(".filterColumn").val(),
filterObject;
var word = value;
if (condition == 'regexp') {
var spl = word.split(" ");
word = "";
for (var n in spl) {
word += spl[ n ];
if (n != spl.length - 1) {
word += "|";
}
}
word = new RegExp(word);
value = word;
}
if (dataIndx == "") {//search through all fields when no field selected.
filterObject = [];
var CM = $grid.pqGrid("getColModel");
for (var i = 0, len = CM.length; i < len; i++) {
var dataIndx = CM[ i ].dataIndx;
filterObject.push({ dataIndx: dataIndx, condition: condition, value: value });
}
}
else {//search through selected field.
filterObject = [{ dataIndx: dataIndx, condition: condition, value: value }];
}
$grid.pqGrid("filter", {
oper: 'replace',
data: filterObject
});
}
when using condition "regex". You said my code logic is good before, so any suggestions?
-
Could you try to pass word as it is instead of creating a new RegExp.
-
This one solved the problem, thank You. [EDIT: contain criteria didn't work because I had problem with filterrender funcition]
If somebody is interested in having regexp hightlited, this is the example of altered filterrender function:
//filterRender to highlight matching cell text.
function filterRender(ui) {
var val = ui.cellData,
filter = ui.column.filter;
var valTest = safeToString(val);
if (valTest.indexOf("<a") != -1 || valTest.indexOf("<img") != -1) {
return val;
}
if (filter && filter.on && filter.value) {
var condition = filter.condition,
valUpper = safeToString(val).toUpperCase(),
txt = filter.value,
txtUpper = txt.toUpperCase(),
indx = -1;
if (condition == "end") {
indx = valUpper.lastIndexOf(txtUpper);
//if not at the end
if (indx + txtUpper.length != valUpper.length) {
indx = -1;
}
}
else if (condition == "contain") {
indx = valUpper.indexOf(txtUpper);
}
else if (condition == "begin") {
indx = valUpper.indexOf(txtUpper);
//if not at the beginning.
if (indx > 0) {
indx = -1;
}
}
else if (condition == "regexp") {
txt = txt.replace("/").split("|");
val = safeToString(val);
for (var n in txt) {
var txtNoRegexp = txt[n];
txt[n] = new RegExp(txt[n],"ig");
val = val.replace(txt[n], "<span style='background:yellow;color:#333;'>" + txtNoRegexp + "</span>");
}
return val;
}
if (indx >= 0) {
var txt1 = val.substring(0, indx);
var txt2 = val.substring(indx, indx + txt.length);
var txt3 = val.substring(indx + txt.length);
return txt1 + "<span style='background:yellow;color:#333;'>" + txt2 + "</span>" + txt3;
}
else {
return val;
}
}
else {
return val;
}
}
safeToString() is the method mentioned above ITT.
-
Thanks for sharing.