Author Topic: Search for multiple words in multiple columns + yellowmark matches  (Read 19903 times)

motoguru

  • Pro Enterprise
  • Newbie
  • *
  • Posts: 35
    • View Profile
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.

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6275
    • View Profile
Re: Search for multiple words in multiple columns + yellowmark matches
« Reply #1 on: February 17, 2014, 08:03:31 pm »
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.



motoguru

  • Pro Enterprise
  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: Search for multiple words in multiple columns + yellowmark matches
« Reply #2 on: February 17, 2014, 08:33:10 pm »
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?
« Last Edit: February 17, 2014, 08:36:57 pm by motoguru »

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6275
    • View Profile
Re: Search for multiple words in multiple columns + yellowmark matches
« Reply #3 on: February 17, 2014, 08:40:15 pm »
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
« Last Edit: February 17, 2014, 08:42:17 pm by paramquery »

motoguru

  • Pro Enterprise
  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: Search for multiple words in multiple columns + yellowmark matches
« Reply #4 on: February 17, 2014, 09:01:35 pm »
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);

« Last Edit: February 17, 2014, 09:04:07 pm by motoguru »

motoguru

  • Pro Enterprise
  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: Search for multiple words in multiple columns + yellowmark matches
« Reply #5 on: February 18, 2014, 12:53:05 am »
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?
« Last Edit: February 18, 2014, 05:06:54 am by motoguru »

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6275
    • View Profile
Re: Search for multiple words in multiple columns + yellowmark matches
« Reply #6 on: February 18, 2014, 01:57:18 pm »
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

motoguru

  • Pro Enterprise
  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: Search for multiple words in multiple columns + yellowmark matches
« Reply #7 on: February 18, 2014, 02:57:32 pm »
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
« Last Edit: February 18, 2014, 03:29:15 pm by motoguru »

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6275
    • View Profile
Re: Search for multiple words in multiple columns + yellowmark matches
« Reply #8 on: February 18, 2014, 04:05:49 pm »
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.

« Last Edit: February 18, 2014, 04:46:56 pm by paramquery »

motoguru

  • Pro Enterprise
  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: Search for multiple words in multiple columns + yellowmark matches
« Reply #9 on: February 18, 2014, 06:05:38 pm »
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?
« Last Edit: February 18, 2014, 06:14:02 pm by motoguru »

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6275
    • View Profile
Re: Search for multiple words in multiple columns + yellowmark matches
« Reply #10 on: February 18, 2014, 06:21:08 pm »
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



motoguru

  • Pro Enterprise
  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: Search for multiple words in multiple columns + yellowmark matches
« Reply #11 on: February 18, 2014, 06:25:56 pm »
All right, thanks for help :)

motoguru

  • Pro Enterprise
  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: Search for multiple words in multiple columns + yellowmark matches
« Reply #12 on: February 24, 2014, 08:38:39 pm »
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.

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 6275
    • View Profile
Re: Search for multiple words in multiple columns + yellowmark matches
« Reply #13 on: February 24, 2014, 08:45:13 pm »
Records are automatically sorted when filters are applied/removed depending upon value of sortIndx and sortDir.

motoguru

  • Pro Enterprise
  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: Search for multiple words in multiple columns + yellowmark matches
« Reply #14 on: February 24, 2014, 09:54:28 pm »
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.
« Last Edit: February 24, 2014, 09:58:33 pm by motoguru »