We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Grid filtering at backend.

Thread ID:

Created:

Updated:

Platform:

Replies:

129087 Feb 25,2017 09:41 AM Aug 28,2018 01:18 AM PHP 11
loading
Tags: Grid
lukasz borzecki
Asked On February 25, 2017 09:41 AM

Hi there, I downloaded and use the community edition of the js product line - got some question about the filtering of the grid.
it does send some syntax of this
(startswith(tolower(name),'asdf')) and (startswith(tolower(first_name),'fff'))

Is there already any parser written for such case to handle or do i must write it myself. Could not find any php backend libraries either.

Regards.
LB.

Jayaprakash Kamaraj [Syncfusion]
Replied On February 27, 2017 07:51 AM

 
Hi Lukasz,  
 
Thank you for contacting Syncfusion support. 
  
To perform Searching, Filtering, sorting and paging operation, we need to process the query sting values (parse url) and make the SQL queries. So, use UrlAdaptor while using UrlAdaptor to get the searching, filtering, sorting and paging params as collections.    
  
Filtering: In my SQL query, we need to map the corresponding operators. In the following code example, we have mapped only the number filter operator and performed the filtering using “WHERE” clause.     
  
  
  
/* Filtering operation */  
if($filter !=null){  
    if(!$filter[0]['isComplex']){  
        $where= whereFilter($filter[0]);  
    }  
    else{  
        $where = performFiltering($filter[0]['predicates']);  
    }  
    
  
    if($searchQuery!= null){  
        $query = $query. " AND (" .$where. ")";  
    }  
    else{  
        $query = $query. " WHERE " .$where;  
    }  
    $sql= $query;  
}  
  
  
function performFiltering($filter){  
    $temp;  
    $value;  
    foreach ($filter as $v) {  
        if(!$v['isComplex']){  
            $temp= whereFilter($v);  
            $value=  processFilterQuery($temp);  
        }  
        else  
        {  
            performFiltering($v['predicates']);  
        }  
    }  
    return $value;  
}  
  
  
function processFilterQuery($temp){  
    global $filterQuery;  
    if($filterQuery != null){  
        $filterQuery =$filterQuery." AND ". $temp;  
    }  
    else{  
        $filterQuery = $temp;  
    }  
  
    return $filterQuery;  
}  
  
  
  
In the following code example, we are mapping the operators of both string and number type columns. Refer to the below code example as.    
 
  
  
function whereFilter($filter){  
    $field = $filter['field'];  
    $operator = $filter['operator'];  
    $value = $filter['value'];  
    $ignorecase = $filter['ignoreCase'];  
    $iscomplex = $filter['isComplex'];  
      
   //Map operators    
    $fltrOp= MapOperator($operator,$value);  
      
    return $field. " ".$fltrOp;  
}  
  
function MapOperator($op,$value){  
    $operater;  
    switch ($op)  
    {  
        case "greaterthan":  
            $operater = ">";  
            break;  
.. 
  
        /* String Operators */  
        case "startswith":  
            return "LIKE '" .$value."%'";  
.. 
    }  
    return $operater." '".$value."'";  //returning operators with values  
}  
  
 
To perform paging operation, we should process the $skip, $take query operation and return the result data.     
 
  
header("Content-type:application/json");  
$link = mysqli_connect('localhost', 'root', '', 'mysql');  
  
/* check connection */  
if (!$link) {  
    printf("Connect failed: %s\n", mysqli_connect_error());  
    exit();  
}  
  
$skip = $_GET['$skip'];  
$take = $_GET['$top'];  
$sort = $_GET['$orderby'];  
$query = "SELECT * FROM help_category";  
  
if($sort !=null)  
{  
    $query  $query." ORDER BY " .$sort //perform sort operation  
}  
if($take!=null){  
    $query = $query." limit ".$skip.",".$take //perform skip and take operation  
}  
  
$result = mysqli_query($link,$query);  
  
$json=array();  
  
while ($row = mysqli_fetch_array($result)) {  
    array_push($json,array(  
                           'help_category_id' => $row{'help_category_id'},  
                           'name' => $row{'name'},  
                           'parent_category_id' => $row{'parent_category_id'},  
                            
                          ));  
}  
  
$countquery = mysqli_query($link,"SELECT * FROM help_category");  
$count=mysqli_num_rows($countquery);  //return total records count  
$response=array("result"=>$json,"count"=>(int)$count);  
echo json_encode($response);  
  
  
if(function_exists($_GET['action'])) {  
$_GET['action']();  
}  
  
  
   
  
Searching:  
  
/* Searching operation */  
if($search !=null){  
    $fields = $search[0]['fields'];  
    $key = $search[0]['key'];  
      
    $columncount = count($fields);  
    $searchQuery;  
    for($i = 0; $i <= $columncount-1; $i++){  
        $svalue = $fields[$i];  
  
          
        if($i>0){  
            $searchQuery =$searchQuery." OR ". $svalue." LIKE '%" .$key. "%'";  
        }  
        else{  
            $searchQuery = $svalue. " LIKE '%" .$key. "%'";  
        }  
    }  
      
    $query = $query. " WHERE " .$searchQuery ;  
    $sql= $query;  
}  
 
To perform the sorting operation in remote data, we need to handle it in server side. Refer to the below code example.    
  
  
  
$sort = $_GET['$orderby'];  
$query = "SELECT * FROM help_category";  
  
if($sort !=null)  
{  
    $query  $query." ORDER BY " .$sort //perform sort operation  
}  
if($take!=null){  
    $query = $query." limit ".$skip.",".$take //perform skip and take operation  
}  
  
$result = mysqli_query($link,$query);  
  
. . . .   
  
$response=array("result"=>$json,"count"=>(int)$count);  
echo json_encode($response);  
}  
  
  
 
For curd operations, please refer to the below link.   
 
 
Regards,  
 
Jayaprakash K. 

 


lukasz borzecki
Replied On February 27, 2017 12:22 PM

Thanks very much, I didnt notice there is an UrlAdaptor. So i did have the commands in pure text :) Again thanks.

Jayaprakash Kamaraj [Syncfusion]
Replied On February 28, 2017 03:49 AM

Hi Lukasz,  
 
We are happy that the provided suggestion helped you.  
Please get back to us if you need any further assistance.   
 
Regards,  
 
Jayaprakash K. 


mohit
Replied On July 4, 2018 09:15 AM

Hi Jayaprakash,

I am using the same code that you have provided in the previous thread. Thanks for the Support.

the code is not working fine for me as params parameter is empty.

   $json_param = file_get_contents("php://input");
    $params = json_decode($json_param,true);

I Checked in google console also.

What would be the reason according to you.?

Please find below the source that I am using .

Main Index file

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<script
  src="https://code.jquery.com/jquery-3.3.1.js"
  integrity="sha256-2Kok7MbOyxpgUVvAk/HJ2jigOSYS2auK4Pfzbm7uH60="
  crossorigin="anonymous">
</script>

  <link rel='nofollow' href="http://cdn.syncfusion.com/15.1.0.41/js/web/flat-azure/ej.web.all.min.css" rel="stylesheet" />
     <script src="http://cdn.syncfusion.com/js/assets/external/jquery-1.10.2.min.js"></script>
     <script src="http://cdn.syncfusion.com/js/assets/external/jquery.easing.1.3.min.js"></script>
     <script src="http://cdn.syncfusion.com/js/assets/external/jsrender.min.js"></script>
     <script src="https://cdn.syncfusion.com/15.1.0.41/js/web/ej.web.all.min.js"></script>
<title>Finished goods</title>
<style>
    .cols-sample-area {
        margin: 0 auto;
        float: none;
    }
</style>
</head>
<body>
<?php
require_once '../EJ/AutoLoad.php';
?>
<div class='cols-sample-area'>
<?php
include("config.php");
    $sql="SELECT`ID`, `ShipCustomerName`,`ShipAddress1`,`ShipAddress2` FROM `customershiptobilltotbl` WHERE 1";
$l= array();
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
$l[] = $row;
}
$Json = json_encode($l);

 $JsonDecode = json_decode($Json);


mysql_close($link);


$col0= new EJ\Grid\Column();
$col0->field('ID')->headerText('ID')->textAlign('right')->isPrimaryKey(true)->width(50);


    $col1 = new EJ\Grid\Column();
    $col1->field('ShipCustomerName')->headerText('ShipCustomerName')->textAlign('right')->width(100);
    $col2 = new EJ\Grid\Column();
    $col2->field('ShipAddress1')->headerText('ShipAddress1')->width(70);
    $col3 = new EJ\Grid\Column();
    $col3->field('ShipAddress2')->headerText('ShipAddress2')->textAlign('right')->width(70);
    /*
    $col4 = new EJ\Grid\Column();
    $col4->field('ShipCountry')->headerText('ShipCountry')->width(70);
    $col5 = new EJ\Grid\Column();
    $col5->field('Freight')->headerText('Freight')->textAlign('right')->format('{0:C}')->width(70);
*/
    $gridColumns = array($col0,$col1,$col2,$col3);
    $grid =  new EJ\Grid('Grid');
    $edit =new EJ\Grid\EditSetting();

$toolbarItems = array("add","edit","delete","update","cancel");

$toolbar= new EJ\Grid\ToolbarSetting();
$filter =new EJ\Grid\FilterSetting();
$dataManager  = new EJ\DataManager();
$dataManager->url('http://localhost:8083/latest/test/server.php/?action=get_Data')->adaptor('UrlAdaptor');
$dataManager->updateUrl('http://localhost:8083/latest/test/server.php/?action=update_Data')->adaptor('UrlAdaptor');

    $column=new EJ\Grid\Column();

    echo $grid->dataSource($dataManager)->allowPaging(true)->allowFiltering(true)->filterSettings($filter->filterType("menu"))->allowGrouping(true)->columns($gridColumns)->editSettings($edit->allowEditing(true)->allowDeleting(true)->allowAdding(true))->toolbarSettings($toolbar->showToolbar(true)->toolbarItems($toolbarItems))->allowSorting(true)->render();
    ?>
  </div>
</body>
</html>

Main server File is attached.


Your suggestions are most welcome.

Thanks ,
Mohit




Attachment: server_861943dd.zip

Farveen Sulthana Thameeztheen Basha [Syncfusion]
Replied On July 5, 2018 12:24 PM

Hi Mohit, 

Thanks for contacting Syncfusion Support. 

We have checked your query and we are unable to reproduce your reported problem at our end. Please refer to the sample Link:- 


We need some additional information to find the cause of the issue. Please share us the following details. 

1. Screenshot of the issue you have faced. 
2. Provide the Db file of the sample by using Export option. 

The provided information will be helpful to provide you response as early as possible. 

Regards, 
Farveen sulthana T 


Bakhtiyor
Replied On August 11, 2018 07:32 AM

First of all, I would like to appreciate your support and great work. 

I just noticed, that in your sample you hadn't taken into account the "condition" field which could take one of the values (and, or). Do you have this library in a more complete form, please?|

Thank you,
Bakhtiyor

Seeni Sakthi Kumar Seeni Raj [Syncfusion]
Replied On August 13, 2018 07:26 AM

Hi Bakhtiyor,  
 
Condition property can be only useful in the case of the Excel Filtering. For other filter types, we could use only the “AND” as the default condition. If you are using an Excel Filter type, you can use the following code example to handle the Condition property in the server end.  
 
Please refer to the code example:- 
 
if($filter !=null){ 
            if(!$filter[0]['isComplex']){ 
                $where= whereFilter($filter[0]); 
            } 
            else{ 
                $where = performFiltering($filter[0]['predicates'],$filter['condition']); 
            } 
            $searchQuery = isset($searchQuery) ? $searchQuery : NULL; 
            if( $searchQuery != null){ 
                $query = $query. " AND (" .$where. ")"; 
            } 
            else{ 
                $query = $query. " WHERE " .$where; 
            } 
            $sql= $query; 
        }   
function performFiltering($filter,$cond){ 
            $temp; 
            $value; 
     foreach ($filter as $v) { 
        if(!$v['isComplex']){ 
            $temp= whereFilter($v); 
            $value=  processFilterQuery($temp,$cond); 
        } 
        else 
        { 
            performFiltering($v['predicates'],$cond); 
        } 
     } 
function processFilterQuery($temp,$cond){ 
            global $filterQuery; 
            $con = isset($cond) ? $cond : NULL ; 
            $cond = isset($cond)== "and" ? " AND " : " OR "; 
            if($filterQuery != null){ 
                $filterQuery =$filterQuery.$cond.$temp; 
            } 
            else{ 
                $filterQuery = $temp; 
            } 
 
            return $filterQuery; 
    } 
   
 
 
Refer to the sample Link:- 
 
Regards,  
Seeni Sakthi Kumar S. 


Gustavo
Replied On August 22, 2018 09:23 AM

I downloaded the code for this topic.
Multi-ordering does not work (allowMultiSorting: true,)

Do you have this library in a more complete form, please?

this is my code

<script type="text/javascript">
        $(function () {
            var data = ej.DataManager({
                url: "<?php echo URL_ROOT ?>/gastos/gastos_grilla_datos", adaptor: "UrlAdaptor"
            });

            $("#Grid").ejGrid({
                dataSource: data,
                allowPaging: true,   
                //pageSettings: { pageSize: 10, pageSizeList: [10,25,50,100] },                
                pageSettings: { enableQueryString: true, pageSize: 10, pageSizeList: [10,25,50,100,1000] },
                locale: "es-ES", 
                allowGrouping : true,
                allowSorting: true,
                allowMultiSorting: true,
                allowFiltering: true,
                //filterSettings:{filterType:"menu"},
                filterSettings: { filterType: "excel" }, //no funciona del lado del servidor
                allowScrolling: true,
                scrollSettings: { width: "100%", height: "100%",enableTouchScroll: true  },                
                allowSearching: true,
                isResponsive: true,
                toolbarSettings: { showToolbar: true, toolbarItems: ["search"]},    
                //toolbarSettings: { showToolbar: true, toolbarItems: [ej.Grid.ToolBarItems.PrintGrid] },
                allowTextWrap: true,//alto de las celdas se auto-ajusta para que entre el texto
                showColumnChooser: true,//permite seleccionar que columnas mostrar
                allowResizeToFit: true,
                showSummary: true,                
                summaryRows: [{
                  title: "Importe Total",
                  summaryColumns: [{
                      summaryType: ej.Grid.SummaryType.Sum,
                      displayColumn: "importe",
                      dataMember: "importe",
                      format: "{0:C2}"
                  }], showTotalSummary: false
                }],
                columns: [
                        <?php if ($VIEW) 
                        {
                        ?>
                        { headerText: "", width: 30, template: "<a rel='nofollow' href='<?php echo URL_ROOT ?>/gastos/consultar/{{:id_gasto}}' data-toggle='tooltip' data-placement='bottom' data-original-title='Consultar'><i class='fa fa-fw fa-binoculars'></i></a>", textAlign : "center" },
                        <?php
                        }
                        ?>
                        <?php if ($UPDATE) 
                        {
                        ?>
                        { headerText: "", width: 30, template: "<a rel='nofollow' href='<?php echo URL_ROOT ?>/gastos/modificar/{{:id_gasto}}' data-toggle='tooltip' data-placement='bottom' data-original-title='Modificar'><i class='fa fa-fw fa-pencil-square'></i></a>", textAlign : "center" },
                        <?php
                        }
                        ?>
                        <?php if ($DELETE) 
                        {
                        ?>
                        { headerText: "", width: 30, template: "<a rel='nofollow' href='<?php echo URL_ROOT ?>/gastos/eliminar/{{:id_gasto}}' data-toggle='tooltip' data-placement='bottom' data-original-title='Eliminar'><i class='fa fa-fw fa-minus-square'></i></a>", textAlign : "center" },
                        <?php
                        }
                        ?>
                        { field: "id_gasto", headerText: 'Cód.', width: 80,type:"number"},
                        { field: "fecha", headerText: 'Fecha', width: 80,type:"date",format: "{0:dd/MM/yyyy}"},
                        { field: "categoria",  headerText: "Categoría", width: 90, type: "string"},                        
                        { field: "subcategoria",  headerText: "Sub-Categoría", width: 90, type: "string"},                        
                        { field: "pago_forma", headerText: 'Forma de Pago', width: 80, type: "string"},
                        { field: "etiqueta", headerText: 'Etiqueta', width: 80, type: "string"},                        
                        { field: "descripcion", headerText: 'Descripción', width: 80, type: "string"},                                                
                        { field: "importe", headerText: 'Importe',  textAlign: ej.TextAlign.Right, width: 80,type:"decimal", format: "{0:C2}"},
                        
                                                
                ]

            });

        });


    </script>



Seeni Sakthi Kumar Seeni Raj [Syncfusion]
Replied On August 24, 2018 08:04 AM

Hi Gustavo,  
 
We have handled the MultiSorting on serverside by using sorted Column count. Based on sorted columncount from the FieldName, we have looped and perform sorting based on the Direction and fieldName. 
 
Please refer to the code example:- 
 
<?php 
     
    $col1 = new EJ\Grid\Column(); 
    $col1->field("help_topic_id")->headerText("OrderID")->isPrimaryKey(true)->textAlign("right")->width(100); 
    $col2 = new EJ\Grid\Column(); 
    $col2->field("name")->headerText("CustomerID")->width(70); 
    $col3 = new EJ\Grid\Column(); 
    $col3->field("help_category_id")->headerText("EmployeeID")->textAlign("right")->width(70); 
    $toolbarItems = array("add","edit","delete","update","cancel"); 
    $edit =new EJ\Grid\EditSetting(); 
    $page = new EJ\Grid\PageSetting(); 
    $toolbar= new EJ\Grid\ToolbarSetting(); 
    echo $grid -> dataSource($dataManager)->columns($gridColumns)->allowPaging(true)->allowMultiSorting(true)->editSettings($edit->allowEditing(true)->allowDeleting(true)->allowAdding(true))->toolbarSettings($toolbar->showToolbar(true)->toolbarItems($toolbarItems))->childGrid($child)->allowSorting(true)->allowFiltering(true)->render(); 
    ?> 
 
Serverside:- 
 
$filterQuery = NULL; 
$skip = $params['skip']; 
$take = $params['take']; 
 
$search = isset($params['search']) ? $params['search'] : NULL ; 
$filter = isset($params['where']) ? $params['where'] : NULL ; 
$sort = isset($params['sorted']) ? $params['sorted'] : NuLL ; 
$query =$sql = "SELECT * FROM help_topic"; 
 
/* Sorting operation */ 
if($sort !=null) 
{ 
       $fields = $sort[0]['fields']; 
       $columncount = count($fields); 
       $sortQuery; 
       for($i = 0; $i <= $columncount-1; $i++){ 
          if($sort[i]['direction'] == "descending"){ 
             $sortQuery  =  $sortQuery." ORDER BY " .$sort[i]['name']. " desc"; 
         } 
       else{ 
       $sortQuery =  $sortQuery." ORDER BY " .$sort[i]['name'];  //perform sort operation 
       } 
   } 
   $query = $sortQuery; 
   $sql= $query; 
} 
 
if($take!=null){ 
    $query = $query." limit ".$skip.",".$take;  //perform skip and take operation 
} 
 
$result = mysqli_query($link,$query); 
 
$json=array(); 
 
while ($row = mysqli_fetch_array($result)) { 
    array_push($json,array( 
                           'help_topic_id' => $row{'help_topic_id'}, 
                           'name' => $row{'name'}, 
                           'help_category_id' => $row{'help_category_id'}, 
                            
                          )); 
} 
 
$countquery = mysqli_query($link,$sql); 
$count=mysqli_num_rows($countquery);  //return total records count 
 
$response=array("result"=>$json,"count"=>(int)$count); 
} 
 
 
 
 
Regards,  
Seeni Sakthi Kumar S. 


Gustavo
Replied On August 27, 2018 03:23 PM

works well with the following changes

//para multi-sorting
        if($sort !=null) 
        { 
           //$fields = $sort[0]['fields']; 
           $fields = $sort; 
           $columncount = count($fields); 
           $sortQuery=""; 
           for($i = 0; $i <= $columncount-1; $i++){ 
              if($sort[$i]['direction'] == "descending"){ 
                  if ($i==0)
                  {
                    $sortQuery  =  $sortQuery." ORDER BY " .$sort[$i]['name']. " desc";     
                  }
                  else
                  {
                    $sortQuery  =  $sortQuery."," .$sort[$i]['name']. " desc";      
                  }                                   
              } 
              else{ 
                if ($i==0)
                {
                    $sortQuery =  $sortQuery." ORDER BY " .$sort[$i]['name'];  //perform sort operation   
                }
                else
                {
                    $sortQuery =  $sortQuery."," .$sort[$i]['name'];  //perform sort operation     
                }
                
              } 
            }
            
           $query .= $sortQuery; 
           $sql= $query; 
        }


Thank you very much!

Seeni Sakthi Kumar Seeni Raj [Syncfusion]
Replied On August 28, 2018 01:18 AM

Hi Gustavo,  
 
Thanks for the update.  
 
We are happy to hear that your requirement has been achieved and you are good to go. Please get back to us, if you require further assistance.  
 
Regards,  
Seeni Sakthi Kumar S. 
  


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;