Filtering : Multi-fields filter and default search type

Talk about Filter (search) Settings

Moderator: alexandrleonenko

Cannelle
Newbie
Newbie
Posts: 8
Joined: Mar 10th, '10, 18:00

Filtering : Multi-fields filter and default search type

Postby Cannelle » Mar 3rd, '11, 12:31

Hi,

I'm setting up filtering with DG 6.4.1.

Three filters are mono-field, the 4th filter is multi-fields. For this 4th filter to work, I have to change its search type to OR instead of the default AND.

How can I do that?

administrator
Site Admin
Site Admin
Posts: 6086
Joined: Jan 7th, '09, 23:18
Contact:

Re: Filtering : Multi-fields filter and default search type

Postby administrator » Mar 3rd, '11, 20:48

Can you explain in more detail please?
Turn on debug mode and post here select sql that you get on filtering.

Cannelle
Newbie
Newbie
Posts: 8
Joined: Mar 10th, '10, 18:00

Re: Filtering : Multi-fields filter and default search type

Postby Cannelle » Mar 4th, '11, 12:34

Here are my filtering fields.
The 3 first filters (Jour, Heure, Genre) are mono-field filters ("field"=>"field_name_1",).
The 4th filter (Mot-clef) is a multi-fields filter ("field"=>"field_name_1, field_name_2, field_name_3",)

Code: Select all

$filtering_fields = array(
         "Jour"=>array(
            "type"=>"dropdownlist",
            "table"=>"mednat_animations",
            "field"=>"sorting",
            "field_view"=>"jour",
            "filter_condition"=>"",
            "order"=>"ASC",
            "source"=>"self",
            "condition"=>"",
            "show_operator"=>"false",
            "default_operator"=>"=",
            "case_sensitive"=>"false",
            "comparison_type"=>"string",
            "width"=>"",
            "on_js_event"=>""),
         "Heure"=>array(
            "type"=>"dropdownlist",
            "table"=>"mednat_animations",
            "field"=>"heure",
            "field_view"=>"heure",
            "filter_condition"=>"",
            "order"=>"ASC",
            "source"=>$fill_from_array,
            "condition"=>"",
            "show_operator"=>"false",
            "default_operator"=>">",
            "case_sensitive"=>"false",
            "comparison_type"=>"string",
            "width"=>"",
            "multiple"=>"false",
            "multiple_size"=>"4",
            "on_js_event"=>""),
         "Genre"=>array(
            "type"=>"dropdownlist",
            "table"=>"mednat_animations",
            "field"=>"genre",
            "field_view"=>"genre",
            "filter_condition"=>"",
            "order"=>"ASC",
            "source"=>"self",
            "condition"=>"",
            "show_operator"=>"false",
            "default_operator"=>"=",
            "case_sensitive"=>"false",
            "comparison_type"=>"string",
            "width"=>"",
            "on_js_event"=>""),
        "Mot-clef"=>array(
            "type"=>"textbox",
            "table"=>"mednat_animations",
            "field"=>"animateur, titre, descriptif",
            "filter_condition"=>"",
            "show_operator"=>"false",
            "default_operator"=>"%like%",
            "case_sensitive"=>"false",
            "comparison_type"=>"string",
            "width"=>"",
            "on_js_event"=>"")
     );

Here is the SQL query.
Filtering by Jour, Heure, Genre, works fine. But filtering by Mot-clef returns zero results because, in the WHERE clause, the default search type for this Mot-clef filter is AND instead of OR:

Code: Select all

SELECT ID, sorting, jour, heure, salle, genre, titre, animateur, descriptif
FROM mednat_animations
WHERE 1 =1
AND (
(
mednat_animations.sorting = '1'
)
AND (
mednat_animations.heure > '09h00'
)
AND (
mednat_animations.genre = 'Film'
)
AND (
mednat_animations.animateur LIKE '%carbon%'
)
AND (  // should be OR
mednat_animations.titre LIKE '%carbon%'
)
AND (  // should be OR
mednat_animations.descriptif LIKE '%carbon%'
)
)
ORDER BY sorting ASC
LIMIT 0 , 25

For this SQL query to return a correct result, the last two AND have to be replaced by OR.

I tried to add "search_type"=>"OR" in my Mot-clef filter array, but it doesn't work :)
I tried to uncomment this line

Code: Select all

$dgrid->SetDefaultFiltering(array("table"=>"", "field_name"=>"", "field_value"=>"", "date_format"=>"", "field_type"=>"", "search_type"=>"OR"));

but it doesn't work neither.

administrator
Site Admin
Site Admin
Posts: 6086
Joined: Jan 7th, '09, 23:18
Contact:

Re: Filtering : Multi-fields filter and default search type

Postby administrator » Mar 6th, '11, 09:06

SetDefaultFiltering works only for non-searching mode, in other words only if you don't perform searching.
We'll try to improve this feature in the next version and add possibility to define search type OR|AND for each field in filtering

Cannelle
Newbie
Newbie
Posts: 8
Joined: Mar 10th, '10, 18:00

Re: Filtering : Multi-fields filter and default search type

Postby Cannelle » Mar 6th, '11, 16:35

I obviously didn't understood the use of SetDefaultFiltering, did I? :roll:

Note that the multi-fields filter alone works fine; After removing all other filters to keep only the multi-fields filter, the search type is correctly set to OR in the SQL query:

Code: Select all

SELECT ID, sorting, jour, heure, salle, genre, titre, animateur, descriptif
FROM animations
WHERE 1 =1
AND (
(
animations.animateur LIKE '%carbon%'
)
OR (
animations.titre LIKE '%carbon%'
)
OR (
animations.descriptif LIKE '%carbon%'
)
)
ORDER BY sorting ASC
LIMIT 0 , 25

So, to circumvent the problem with this version of DG, I converted the multi-3-fields filter to 3 mono-field filters. Now, the complete $filtering_fields array looks like this:

Code: Select all

$filtering_fields = array(
         "Jour"=>array(
            "type"=>"dropdownlist",
            "table"=>"animations",
            "field"=>"sorting",
            "field_view"=>"jour",
            "filter_condition"=>"",
            "order"=>"ASC",
            "source"=>"self",
            "condition"=>"",
            "show_operator"=>"false",
            "default_operator"=>"=",
            "case_sensitive"=>"false",
            "comparison_type"=>"string",
            "width"=>"",
            "on_js_event"=>""),
         "Heure (dès)"=>array(
            "type"=>"dropdownlist",
            "table"=>"animations",
            "field"=>"heure",
            "field_view"=>"heure",
            "filter_condition"=>"",
            "order"=>"ASC",
            "source"=>$fill_from_array,
            "condition"=>"",
            "show_operator"=>"false",
            "default_operator"=>">",
            "case_sensitive"=>"false",
            "comparison_type"=>"string",
            "width"=>"",
            "multiple"=>"false",
            "multiple_size"=>"4",
            "on_js_event"=>""),
         "Genre"=>array(
            "type"=>"dropdownlist",
            "table"=>"animations",
            "field"=>"genre",
            "field_view"=>"genre",
            "filter_condition"=>"",
            "order"=>"ASC",
            "source"=>"self",
            "condition"=>"",
            "show_operator"=>"false",
            "default_operator"=>"=",
            "case_sensitive"=>"false",
            "comparison_type"=>"string",
            "width"=>"",
            "on_js_event"=>""),
        "Animateur"=>array(
            "type"=>"textbox",
            "table"=>"animations",
            "field"=>"animateur",
            "filter_condition"=>"",
            "show_operator"=>"false",
            "default_operator"=>"%like%",
            "case_sensitive"=>"false",
            "comparison_type"=>"string",
            "width"=>"",
            "on_js_event"=>""),
        "Titre"=>array(
            "type"=>"textbox",
            "table"=>"animations",
            "field"=>"titre",
            "filter_condition"=>"",
            "show_operator"=>"false",
            "default_operator"=>"%like%",
            "case_sensitive"=>"false",
            "comparison_type"=>"string",
            "width"=>"",
            "on_js_event"=>""),
        "Descriptif"=>array(
            "type"=>"textbox",
            "table"=>"animations",
            "field"=>"descriptif",
            "filter_condition"=>"",
            "show_operator"=>"false",
            "default_operator"=>"%like%",
            "case_sensitive"=>"false",
            "comparison_type"=>"string",
            "width"=>"",
            "on_js_event"=>"")
     );

Thank you.

administrator
Site Admin
Site Admin
Posts: 6086
Joined: Jan 7th, '09, 23:18
Contact:

Re: Filtering : Multi-fields filter and default search type

Postby administrator » Mar 7th, '11, 12:01

The purpose of this feature is to run it when you open the page first time.
We his feature is still requires improvement and we'll try to do this in the next version.

wonoz
Senior
Senior
Posts: 41
Joined: Oct 14th, '10, 13:43

Re: Filtering : Multi-fields filter and default search type

Postby wonoz » Mar 7th, '11, 13:47

You can use your own filter like I do...

You just need to set an input text box, in your page before the datagrid declaration, wich will take the value of your filter :

Code: Select all

<form method="post"><input type="text" name="myFiltre" onchange="this.form.submit();" /></form>


Then, add your filter result in your SQL definition :

Code: Select all

$sql = "SELECT * FROM myTable WHERE id > 0";
if(isset($_POST['myFiltre']) $sql = $sql . " OR $_POST['myFiltre'] ";

It works well if you think to save the $_POST['myFiltre'] value in the datagrid.class, everywhere, between the "<form> </form>" tag. (without this, the filter value will be lost on changing page)


Quoi que je vois que tu es francais également, si tu n'as pas compris mon baratin presque anglais.. Je peux mieux l'expliquer en francais :D

Cannelle
Newbie
Newbie
Posts: 8
Joined: Mar 10th, '10, 18:00

Re: Filtering : Multi-fields filter and default search type

Postby Cannelle » Mar 8th, '11, 12:13

Now I understood the use of SetDefaultFiltering, I'm using it, this is just what I needed.

Thank you for your tip wonoz. I got the idea.


Return to “Settings: Filtering (search) / Autocomplete”