How to show the filterrow without the default filtering functionality?

[jgarciasParticipant]

Hi team!

I have a jqxGrid in a page, where the data comes already paginated from the database, and I want to add a different functionality to the filterrow, because by default the filters are applied to the data shown in the grid only.
My intention is to use the filterrow textboxes to build, somehow, a string with the dataFields and their current filter value and send it to the backend, so that the respective rows can be selected from the database table and show the result in the grid.

Is there any way you can recommend to “hack” the filterrow functionality, so that I can achieve my goal?

[Yavor DashevParticipant]

Hi jgarcias,

Would you like to give a bit more context of your use case in order to be able to give you a bit more concrete solution for you?

In the mean time I can suggest you to take a look at our documentation as it has articles regarding binding the jqxGrid with server side filter/sorting and etc.
Link to the documentation:
https://www.jqwidgets.com/jquery-widgets-documentation/documentation/phpintegration/php-server-side-grid-crud.htm?search=

Also we have a demo on how to override some of the filtering functionality of the jqxGrid:
https://www.jqwidgets.com/jquery-widgets-demo/demos/jqxgrid/index.htm#demos/jqxgrid/filterrowwithcustomitems.htm

Please, do not hesitate to contact us if you have any additional questions.

Best Regards,
Yavor Dashev
jQWidgets team
https://www.jqwidgets.com

[jgarciasParticipant]

Hi Yavor

Thanks for you reply.
I want to build a SQL SELECT statement with a WHERE clause using the values of the filterrow cells (textboxes) as the conditions, so that when a user types some text on any filter cell (blur/change events) a new AJAX request is sent to the server to collect the data rows that meet such search conditions.

Well, that would suppose to get rid of the default functionality or behavior of the filterrow itself, because I am not interested in filtering the grid content but the DB table it represents.
Why? because I like the way the filterrow looks in the grid. It is conveniently positioned to filter the data as it is, that’s why I am interested to keep it there, but for large database tables, filtering the content of a grid, is not suitable especially when using paging.

Ehmm… and no, I do not want to open dialog boxes or modal window to ask for search conditions to the user.

I am currently trying to use an Ever Present Row instead of the filterrow. I am not sure if it is a good idea though.

Cheers,

Joaquin.

[Yavor DashevParticipant]

Hi jqgarcias,

In your specific case I can also suggest you to use the toolbar functionalities of the jqxGrid which I think it’s closest to meeting your requirements.

I have prepared how to implement a toolbar for filtering the grid although you will have to create your query logic and etc.

The code example:

       <style> 
            .rendertoolbarContainer {
                display: flex;
                margin: 5px;
            }
            .rendertoolbarContainer div,
            .rendertoolbarContainer input {
                margin-left: 15px;
                margin-right: 15px;
            }
        </style>
        <script type="text/javascript">
            $( document ).ready( function () {
                // prepare the data
                var data = generatedata( 200 );

                var source =
                {
                    localdata: data,
                    datatype: "array",
                    datafields:
                        [
                            { name: 'firstname', type: 'string' },
                            { name: 'lastname', type: 'string' },
                            { name: 'productname', type: 'string' },
                            { name: 'available', type: 'bool' },
                            { name: 'quantity', type: 'number' },
                            { name: 'price', type: 'number' }
                        ],
                    updaterow: function ( rowid, rowdata ) {
                        // synchronize with the server - send update command
                    }
                };

                var dataAdapter = new $.jqx.dataAdapter( source );

                var source = [
                    "Affogato",
                    "Americano",
                    "Bicerin",
                    "Breve",
                    "Café Bombón",
                    "Café au lait",
                    "Caffé Corretto",
                    "Café Crema"
                
                ]; 
                rendertoolbar=  function (toolbar) {
                    var me = this;
                    var container = $("<div class='rendertoolbarContainer'></div>");
                    var input = $("<input  id='input' />")
                    var dropDown = $("<div id='jqxDropDown'> </div>")
                    var button =  $("<input type='button' value='Make Query' id='jqxButton' />");

                
                    toolbar.append(container);
                    container.append(input);
                    container.append(dropDown);
                    container.append(button);

                }
                var grid = new jqxGrid( "#grid",
                    {
                        width: getWidth( 'Grid' ),
                        source: dataAdapter,
                        filterable: true,
                        sortable: true,
                        showtoolbar: true,
                        rendertoolbar: rendertoolbar,
                        columns: [
                            { text: 'First Name', columntype: 'textbox', datafield: 'firstname', width: 170 },
                            { text: 'Last Name', datafield: 'lastname', columntype: 'textbox', width: 170 },
                            { text: 'Product', datafield: 'productname', width: 170},
                            { text: 'In Stock', datafield: 'available', columntype: 'checkbox', width: 125 },
                            { text: 'Quantity', datafield: 'quantity', width: 85, cellsalign: 'right', cellsformat: 'n2' },
                            { text: 'Price', datafield: 'price', cellsalign: 'right', cellsformat: 'c2' } 
                        ]
                    });

                    $('#jqxDropDown').jqxDropDownList({ source: source, placeHolder: "Select Item", width: 150, height: 30});
                    $("#input").jqxInput({placeHolder: "Enter a Country", height: 30, width: 250, minLength: 1,  source: source });
                    $("#jqxButton").jqxButton({ width: 100, height: 40 });

                    $("#jqxButton").on('click', function () {
                        var inputValue1 =  $("#input").jqxInput('val');
                        var dropDownValue = $('#jqxDropDown').jqxDropDownList('val');
                        //You can build your query with the values of these inputs for example 
                    });
                

            } );
        </script>
    </head>
    <body class='default'>
        <h3>Grid with Search Input</h3>
        <div id="grid"></div>
    </body>
</html>

Let me know if that works for you!

Please, do not hesitate to contact us if you have any additional questions.

Best Regards,
Yavor Dashev
jQWidgets team
https://www.jqwidgets.com

[jgarciasParticipant]

Many thanks Yavor!

Well, I finally built a solution using an ever present row and using it as the filter row on my grid, but after I finish that solution, I read in other posts, that it is possible to do exactly the same thing using the normal jqxGrid filters. However, I had not read enough to understand how it works.

It took me some time to get it working, but in the end it worked as expected.

I must say… I lost my time creating something that you guys had already created, just because I got lost in the documentation and/or did not research enough the first time (it takes time, definitely). Even my question was wrong, because the filtering functionality does not change, I mean, it works alike either for local data (json, array, CSV, etc) as for database tables.
My real post title had to be: “How to make server side filtering and paging work”

I want to share here my code for anybody else who might find this post useful.

First of all, my development environment:
– Operating System: Windows 10.
– WAMP server 3.2.0 (includes Apache server 2.4.41, PHP 7.4.0, MySql 5.7.28)
– jQWidgets v12.1.2 (2021-July)
– Web browsers: Firefox 91.0.2 (64 bit) / Version 92.0.4515.159 (64-bit)
– Laravel 8.0

The Stored Procedure:

CREATE PROCEDURE sp_getPersons(
  pageSize smallint,
  pageNum int,
  filterCondition varchar(500),
  out numRows int
)
BEGIN
   DECLARE cmd, sql1 varchar(5000);
   -- The variable nr is the number of rows in the query.
   DECLARE nr int;
   SET nr = 0;

   SET @cmd = "SELECT p.personID,
	              p.lastName, 
	              p.firstName, 
	              p.gender, 
	              p.dob, 
	              p.distance, 
	              p.provinceID, 
	              p.districtID, 
                      ...,
	       FROM person p
                 JOIN province prov ON (prov.provinceID = p.provinceID)
                 JOIN district d ON (d.districtID = p.districtID)
               WHERE p.deleted <> 'Y'";

   SET pageNum = pageNum * pageSize;
   IF filterCondition IS NULL OR filterCondition = '' THEN
      SELECT COUNT(*) INTO @nr FROM person;
      SET numRows = @nr;
      SET @cmd = concat(@cmd, "ORDER BY p.personID DESC LIMIT ", CONVERT(pageNum, CHAR(6)), ",", CONVERT(pageSize, CHAR(6)));
   ELSE
      SET @sql1 = CONCAT("SELECT COUNT(1) INTO @nr FROM (", @cmd, filterCondition, ") as temp");
      SET @cmd = concat(@cmd, filterCondition, " ORDER BY p.personID DESC LIMIT ", CONVERT(pageNum, CHAR(6)), ",", CONVERT(pageSize, CHAR(6)));
   END IF;
   
   PREPARE stmt FROM @cmd;
   EXECUTE stmt;
   
   IF filterCondition <> '' THEN
      PREPARE stmt2 FROM @sql1;
      EXECUTE stmt2;
      SET numRows = @nr;
      DEALLOCATE PREPARE stmt2;
   END IF;
   
   DEALLOCATE PREPARE stmt;
END

The method in the controller:

   public function getPersons(Request $request) {
      $condition = '';
      $filterscount = $request['filterscount'];
      if ($filterscount > 0) {
         // Build the filter condition from the request object
         for ($i=0; $i < $filterscount; $i++) {
            // get the filter's column.
            $filterdatafield = $request["filterdatafield" . $i];
            // get the filter's value.
            $filtervalue = $request["filtervalue" . $i];
            
            $condition .= " AND p." . $filterdatafield . " LIKE '" . $filtervalue ."%'";
         }
      }

      $data = $this->person->getPersons($request['pagesize'], $request['pagenum'], $condition);
      return Response::json($data);
   }

The Model method that calls the stored procedure:

   /**
    * Retrieves all rows from table 'person' that
    * are not marked as deleted.
    * 
    * @return person
    */
   public function getPersons($pagesize, $pagenum, $condition) {
      // Call the stored procedure with output parameter.
      $persons = DB::select('CALL sp_getPersons(?, ?, ?, @numRows)', [$pagesize, $pagenum, $condition, 0]);

      // Get the out parameter returned by the Stored Procedure.
      $numRows = DB::select('SELECT @numRows AS numRows');
      $data = array('persons'=>$persons, 'totRows'=>$numRows);
      return $data;
   }

It is important to note that the returned value is an array composed by other 2 arrays. This makes it easier to reference in the client side (javascript)

The JavaScript referenced in the view file (blade template):

  //-----------------------------
   // jqxGrid settings
   //-----------------------------

   $.ajaxSetup({
      headers: {'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')}
   });

   // Initial page size.
   var pageSize = 15;

   var source = {
      url: '/person/getPersons',
      datafields: [
         { name: 'personID', type: 'string'},
         { name: 'lastName', type: 'string'},
         { name: 'firstName', type: 'string'},
         { name: 'gender', type: 'string'},
         { name: 'dob', type: 'date'},
         { name: 'distance', type: 'string'},
         { name: 'provinceID', type: 'int'},
         { name: 'districtID', type: 'int'},
         ...,
      ],
      datatype: 'json',
      cache: false,
      root: 'persons',   // It is an array returned by the Model method.
      beforeprocessing: function(data) {
         // Without the totalrecords set, the pager does not work.
         source.totalrecords = data.totRows[0].numRows;
      },
      filter: function () {
         // update the grid and send a new request to the server.
         $("#grid").jqxGrid('updatebounddata');
      }
   };
   dataAdapter = new $.jqx.dataAdapter(source);

   $("#grid").jqxGrid({
      source: dataAdapter,
      theme: 'energyblue',
      width: '98%',
      height: 485,
      altrows: true,
      filterable: true,
      showfilterrow: true,
      pageable: true,
      pagesizeoptions: ['15', '25', '50'],
      pagesize: pageSize,
      virtualmode: true,
      rendergridrows: function() {
         return dataAdapter.records;
      },
      columns: [
         { text: 'id', dataField: 'personID', width: 100, pinned: true, filtercondition: 'starts_with' },
         { text: 'Last name', dataField: 'lastName', width: 120, pinned: true, filtercondition: 'starts_with' },
         { text: 'First name', dataField: 'firstName', width: 120, pinned: true, filtercondition: 'starts_with' },
         { text: 'Sex', dataField: 'gender', width: 45, filterable: false },
         { text: 'Date of birth', dataField: 'dob', width: 100, cellsformat: 'dd-MM-yyyy', filtertype: 'date' },
         { text: 'Distance', dataField: 'distance', width: 85, filterable: false },
         { text: '', dataField: 'provinceID', hidden: true },
         { text: '', dataField: 'districtID', hidden: true },
         ...,
      ],
   });

Hats off to you guys for building jQWidgets!

[Author]

Posts