[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!

[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.

[jgarciasParticipant]

Hi Hristo

Yes I’d already seen that example in the demo page, but it doesn’t help me because I do not see the cell value being updated anywhere.
I am a bit in a hurry with this, so I ended up using a HTML table and adding scroll capability with CSS and jQuery to manage the events.

Thanks.

[jgarciasParticipant]

Hi Hristo

Your second suggestion definitely is not something I would like to implement, as it does not reflect the buttons that are ON or OFF. They should always be present in every row.

There is no paging on my grid, for the rows to show are not many, so I use scroll.
I am not clear enough about what should be implemented in the initwidget callback in order to update the cell value. Do you have any example of an update to the cell value from that callback?

Cheers.

[jgarciasParticipant]

Hi Hristo

Ok perhaps I did not explain my issue correctly. I want the user to enter a monthly income in a column with 2 decimal digits, only if the variable currency = 'USD'. If not, the monthly income column should not allow to input decimal places.

I have tried setting the condition for editor.decimalDigits property in initeditor or createeditor events, but with no luck, because the numberinput column always accepts only integer numbers. What can I do for the numberinput column to accept decimal digits conditionally?.

Hope this clarifies my situation.

Cheers.

[jgarciasParticipant]

I must say that I am using:
jQWidgets v9.1.6 (2020-May)

If the currency is other than USD it works fine, else no decimal digits are received, only integer numbers which is not the desired effect.

[jgarciasParticipant]

Hi Hristo

Do not worry. I found out how to format the tooltip in the documentation.

Thanks a lot.

[jgarciasParticipant]

Thank you Hristo.
That really worked for me.

One more detail please… when I hover the mouse pointer, on the chart bars, a tooltip is displayed but it shows the dataField name (mtsDate in my case) and its value. Is there a way to change that name to something more meaningful like ‘Month’/’Week’/’Date’?

Cheers,

Joaquin.

[jgarciasParticipant]

Hi Martin

Beautiful !!
I went for cellendedit event of the grid and that solved my issue.

Many thanks!

[jgarciasParticipant]

Hi Martin

Thank you for your answer.
I decided to go for the cellendedit event and perform my custom validation. Everything works as expected except for one small curious thing: the jqxNotification is showing twice. I have dug in my code to see if I wrote something wrong but I can’t find it.

Here is my code for the validation:

        {
           text: gridHeaders[0], dataField: 'memberName', renderer: function (text, align) {
              return '<div class="gridHeader">' + text + '</div>';
           },
           cellendedit: function (row, datafield, columntype, oldvalue, newvalue) {
              if (newvalue == "") {
                 $('#validationMsg').jqxNotification("open");
                 $('#grid_2').jqxGrid('selectcell', row, datafield);
                 return false;
              }
              else {
                 return true;
              }
           },
           width: 213,
        }

Besides that function there is no other one calling or activating my jqxNotification object.

Any clue of why is this happening?

[jgarciasParticipant]

Sorry, I forgot to add and set one property to the combobox, in order to open it with only one click:

createeditor: function (row, column, editor) {
               editor.jqxComboBox({
                  <strong>autoOpen: true</strong>,
                  searchMode: 'none',
                  animationType: 'none',
                  source: sectionAdapter,
                  valueMember: 'sectionID',
                  displayMember: 'sectionNameEng'
               });
            },

Cheers.

[jgarciasParticipant]

Dear all,

After some time I came across with another project where I decided to use jqWidgets and give it a try to the jqxGrid with Combobox columns that were able to show the description of the options previously saved in a database table, as well as changing those values according to the user will.

Reading this thread, and others similar to this, I realized that my code was not yet complete to accomplish the desired task. Let me elaborate on this for sake of other developers looking for a solution to achieve this functionality:

First of all, I have a data source from another table, defined as follows:

   sectionSrc = {
      type: 'get',
      url: '/section/getSections',
      datatype: 'json',
      datafields: [
         { name: 'sectionID', type: 'int' },
         { name: 'sectionNameEng', type: 'string' }
      ]
   };
   sectionAdapter = new $.jqx.dataAdapter(sectionSrc, { contentType: 'application/json' });

This data source will populate my combobox widget.

Now, I also have the data source for my grid:

   source = {
      type: 'get',
      url: '/question/getQuestions',
      datatype: 'json',
      datafields: [
         { name: 'sectionID', type: 'int', values: { source: sectionAdapter, value: 'sectionID', name: 'sectionNameEng' } },
         { name: 'sectionNameEng', type: 'string' },
         { name: 'fieldID', type: 'int' },
         { name: 'fieldType', type: 'string' },
         { name: 'score', type: 'int' },
      ]
   };
   dataAdapter = new $.jqx.dataAdapter(source, { contentType: 'application/json' });

The data comes from the join of 2 DB tables.
Pay attention to the definition of the first field (sectionID) and see the values attribute added to it. This attribute defines the source and properties that will populate the grid fields ‘sectionID’ and ‘sectionNameEng’.

In order to display the combobox appropriately, in the grid column, we should define the grid as follows:

   $("#grid").jqxGrid({
      altrows: true,
      sortable: true,
      source: dataAdapter,
      columnsresize: true,
      showstatusbar: true,
      selectionmode: 'singlecell',
      editmode: "click",
      editable: true,
      columns: [
         {
            text: 'Section', datafield: 'sectionID', displayfield: 'sectionNameEng', width: '250', columntype: 'combobox',
            createeditor: function (row, column, editor) {
               editor.jqxComboBox({
                  searchMode: 'none',
                  animationType: 'none',
                  source: sectionAdapter,
                  valueMember: 'sectionID',
                  displayMember: 'sectionNameEng'
               });
            },
            // update the editor's value before saving it.
            cellvaluechanging: function (row, column, columntype, oldvalue, newvalue) {
               // return the old value, if the new value is empty.
               if (newvalue == "") return oldvalue;
            }
         },
         { text: 'Question number', dataField: 'questionID' }, ...

The properties selectionmode, editmode and editable and the values assigned to them, play an important role here to allow the combobox to open with just one click.

The sectionID grid column needs the displayfield attribute to be set to another field able to save the text from selected item in the combobox, just to show it on the grid after the combobox gets closed.

It is also important to set the properties searchMode, valueMember and displayMember with the values you see in the code above so that the combobox is displayed correctly.
Proceed likewise for any other combobox column that you wish to add to your jqxGrid widget.

I hope this helps someone out there. Good luck!

Joaquin.

[jgarciasParticipant]

Hi all

I used to have the same problem that SkippingAdi (Adrian) had and I solved it by changing the way I retrieved the data from the database. Before I used to have something like:

SELECT CONVERT(date, myDateColumn) FROM ...

and with this approach jqxGrid used to show something like “Mon May 23 02:00:00 UTC+0200 1988” in my web application regardless of the format that I put in the field in both the source structure and grid column definition.
So, I decided to change that statement for this one:

SELECT CONVERT(varchar, myDateColumn, 105) FROM ...

because I needed to show the date with the Italian format (dd-mm-yyyy).
I did not need to make any calculation with that date, it was only an informational date for the user and that’s why I decided to retrieve my data in this way.

Hope this helps someone else.

[jgarciasParticipant]

Peter

Any advise on this?

[jgarciasParticipant]

Hi Peter

I got rid of the cellvaluechanged function and I have modified the grid columns definition as follows:

      columns: [
         {
            text: 'Drug Id', datafield: 'drug_id', width: 60, columntype: 'textbox',
            cellvaluechanging: function(row, datafield, columntype, oldvalue, newvalue) {
               if (newvalue != oldvalue) {
                  // Check for duplicated values in the grid.
                  var duplicated = false;
                  var rowCount = $('#grid').jqxGrid('getdatainformation').rowscount;
                  var lineCount = 0;
                  for (var i = 0; i < rowCount; i++) {
                     var data = $('#grid').jqxGrid('getrowdata', i);
                     if (data.drug_id == newvalue) {
                        lineCount++;
                        duplicated = (lineCount == 2);
                     }
                  }
                  if (duplicated) {
                     alert('Drug is already included in this document.');
                  }
                  else {
                     jQuery.ajax({
                        url: '/WebService1.asmx/getDrugById',
                        type: 'post',
                        datatype: 'json',
                        contentType: 'application/json',
                        data: '{drug_id:"' + newvalue + '"}',
                        success: function(response) {
                           var datarow = JSON.parse(response.d);
                           if (datarow.Table.length > 0) {
                              // -------------------------------------
                              // Update category and drug name columns
                              // -------------------------------------
                              $("#grid").jqxGrid('setcellvalue', row, "category_name", datarow.Table[0].category_name);
                              $("#grid").jqxGrid('setcellvalue', row, "drug_name", datarow.Table[0].drug_name);
                              drugName = datarow.Table[0].drug_name;
                           }
                           else {
                              alert('Drug code not found.');
                              $("#grid").jqxGrid('begincelledit', row, "drug_id");
                           }
                        }
                     });
                  }
               }
            }
         },
         { text: 'Category', datafield: 'category_name', width: 150, editable: false },
         {
            text: 'Drug name', datafield: 'drug_name', width: 430, columtype: 'textbox', 
            initeditor: function(row, cellvalue, editor, celltext, pressedChar) {
               // -------------------------------------
               // Update the drug name column
               // -------------------------------------
               editor.val(drugName);
               editor.jqxInput({
                  source: jsonDrugs,
                  width: 420,
                  height: 22,
                  displayMember: 'drug_name',
                  placeHolder: 'Enter a drug name',
                  searchMode: 'startswithignorecase',
                  items: 10
               });
            }
         },
         {
            text: 'Quantity', datafield: 'quantity', width: 68, columntype: 'numberinput',
            initeditor: function(row, cellvalue, editor, celltext, pressedChar) {
               editor.jqxNumberInput({
                  inputMode: 'simple',
                  min: 0,
                  spinButtons: false
               });
            },
            cellvaluechanging: function(row, datafield, columntype, oldvalue, newvalue) {
               var drugId = $('#grid').jqxGrid('getcellvalue', row, "drug_id");
               var drugName = $('#grid').jqxGrid('getcellvalue', row, "drug_name");
               if (drugId != '' && drugName != '') {
                  if (newvalue > 0) {
                     // -----------------------------------------------
                     // Add new row to the grid if newvalue is not cero
                     // -----------------------------------------------
                     var newrow = {};
                     newrow['drug_id'] = '';
                     newrow['category_name'] = '';
                     newrow['drug_name'] = '';
                     newrow['quantity'] = '';
                     $("#grid").jqxGrid('addrow', null, newrow);
                     var rc = $("#grid").jqxGrid('getdatainformation').rowscount;
                     $("#grid").jqxGrid('begincelledit', rc-1, "drug_id");
                  }
               }
            }
         }
      ],

This code works fine only if the user passes twice by the drug code column, I mean, firing two times the drug_id column cellvaluechanging event.
There must be something I am missing here to make it work fine.

Can you identify what is wrong with my code?

Thanks

[Author]

Posts