ParamQuery Grid Documentation

ParamQuery Grid provides a comprehensive set of features for data management and UI interaction:

• Data Handling: Supports 100,000+ records, virtual scrolling/rendering for unlimited rows/columns, and various data source formats (HTML, Array, XML, JSON).
• Editing & Interaction: Inline editing (batch, row, custom editors, multiline), validations, undo/redo history, autofill, and drag-to-fill.
• Excel-like Functionality: Copy/paste to/from Excel, frozen rows/columns, column/row grouping, and fixed summary rows.
• Data Manipulation: Local and remote sorting (Integer, real numbers, Strings, dates, etc.), local and remote filtering with header filtering row interface, and paging.
• UI Customization: Resizable/reorderable columns via drag-and-drop, hide/show columns, theme support, and i18n.
• Exporting: Export data to Excel (.xlsx), HTML, JSON, and CSV formats.
• Advanced Layouts: Nesting of grids and row details.
• Integration: Works with any server-side framework (ASP.NET, MVC3, JSP, JSF, PHP, etc.) and supports state management.

The render option in the grid configuration allows you to inject custom HTML elements (like search bars or dropdowns) into the grid's top section. You can access the grid's container via the this context within the render function and use jQuery to append elements to the .pq-grid-top area.

This is commonly used to create custom toolbars for filtering, as shown in the example below.

obj.render = function (evt, obj) {
    // Create a custom toolbar div
    var $toolbar = $("<div class='pq-grid-toolbar pq-grid-toolbar-search'></div>").appendTo($(".pq-grid-top", this));

    $("<span>Filter</span>").appendTo($toolbar);

    // Add a text input for the filter value
    $("<input type='text' class='pq-filter-txt'/>").appendTo($toolbar)
        .change(function (evt) {            
            pqFilter.search();        
        });

    // Add a select dropdown for the column index
    $("<select id='pq-filter-select-column'>
        <option value='ShipCountry'>Ship Country</option>
        <option value='CustomerName'>Customer Name</option>
    </select>").appendTo($toolbar)
        .change(function () {
            pqFilter.search();
        });
};

To implement server-side filtering, configure the dataModel with filterIndx and filterValue properties. You must then implement the getUrl function to include these parameters in the outgoing request.

1. Set filterIndx to the dataIndx of the column being filtered.
2. Set filterValue to the search string.
3. In getUrl, check if filterIndx and filterValue are present and append them to the data object sent to the server.
4. Trigger a refresh using $grid.pqGrid("refreshDataAndView") after updating the dataModel values.

var dataModel = {
    location: "remote",
    filterIndx: "",
    filterValue: "",
    getUrl: function () {
        var data = {
            sortIndx: this.sortIndx,
            sortDir: this.sortDir
        };
        if (this.filterIndx && this.filterValue ) {
            data['filterIndx'] = this.filterIndx;
            data['filterValue'] = this.filterValue;
        }
        return { url: "remote.php", data: data };
    }
};

// To trigger the filter:
// 1. Update dataModel via option
// DM.filterIndx = dataIndx;
// DM.filterValue = txt;
// 2. Refresh the grid
// $grid.pqGrid("refreshDataAndView");

To implement filtering with remote data, you must configure the dataModel to include filterIndx and filterValue properties. When these properties are updated, you call $grid.pqGrid("refreshDataAndView") to trigger a new data request.

In a remote setup (location: "remote"), the getUrl function in the dataModel should be used to append the filterIndx and filterValue to the request parameters sent to the server.

Key DataModel properties for filtering:

• filterIndx: The dataIndx of the column to filter on.
• filterValue: The string value used for the filter.
• location: Set to "remote" to enable server-side processing.
• getUrl: A function that returns an object containing the URL and the data object (including filter parameters) to be sent to the server.

// 1. Define the dataModel with filtering properties
var dataModel = {
    location: "remote",
    dataType: "JSON",
    method: "POST",
    filterIndx: "",
    filterValue: "",
    getUrl: function () {
        var data = {};
        if (this.filterIndx && this.filterValue) {
            data['filterIndx'] = this.filterIndx;
            data['filterValue'] = this.filterValue;
        }
        return { url: "remote.php", data: data };
    },
    getData: function (response) {
        return { data: response };
    }
};

// 2. Trigger the filter update
// Assuming 'pqFilter.search' is your custom logic to update the model
var txt = $("input.pq-filter-txt").val().toUpperCase();
var dataIndx = $"select#pq-filter-select-column".val();
var DM = $grid.pqGrid("option", "dataModel");

DM.filterIndx = dataIndx;
DM.filterValue = txt;
$grid.pqGrid("refreshDataAndView");

To implement remote sorting and filtering, configure the dataModel with location: "remote" and sorting: "remote". You must provide a getUrl function within the dataModel that constructs a request containing the current sorting and filtering parameters.

When a user performs a filter or sort, the grid updates the dataModel properties. Your getUrl function should extract these properties to build the query string for your server-side endpoint.

Key dataModel properties for filtering:

• filterIndx: The dataIndx of the column being filtered.
• filterValue: The string value used for the filter.

Key dataModel properties for sorting:

• sortIndx: The dataIndx of the column being sorted.
• sortDir: The direction of the sort ("up" or "down").

var dataModel = {
    location: "remote",
    sorting: "remote",
    dataType: "JSON",
    method: "GET",
    filterIndx: "",
    filterValue: "",
    sortIndx: "OrderID",
    sortDir: "up",
    getUrl: function () {
        var data = {
            sortIndx: this.sortIndx,
            sortDir: this.sortDir
        };
        if (this.filterIndx && this.filterValue) {
            data['filterIndx'] = this.filterIndx;
            data['filterValue'] = this.filterValue;
        }
        return { url: "remote.php", data: data };
    },
    getData: function (response) {
        return { data: response.data };
    }
};

var $grid = $("#grid_id").pqGrid({
    dataModel: dataModel,
    colModel: colModel
});

For server-side operations, set location, sorting, and paging to "remote" within the dataModel.

Key dataModel properties for remote operations:

• location: Set to "remote" to enable server-side data fetching.
• sorting: Set to "remote" to handle sorting on the server.
• paging: Set to "remote" to handle paging on the server.
• curPage: The current page number.
• rPP: Rows per page.
• rPPOptions: An array of available rows-per-page options (e.g., [1, 10, 20, 50]).
• sortIndx: The column index currently being sorted.
• sortDir: The direction of sorting ("up" or "down").
• filterIndx: The column index used for filtering.
• filterValue: The value used for filtering.
• getUrl: A function that returns an object containing the url and the data object to be sent to the server. This function should include pq_curpage, pq_rpp, sortIndx, sortDir, and optionally filterIndx and filterValue.
• getData: A function that processes the JSON response from the server and returns an object containing curPage, totalRecords, and data.

var dataModel = {
    location: "remote",
    sorting: "remote",
    paging: "remote",
    dataType: "JSON",
    method: "GET",
    curPage: 1,
    rPP: 20,
    sortIndx: "OrderID",
    sortDir: "up",
    rPPOptions: [1, 10, 20, 30, 40, 50, 100, 500, 1000],
    filterIndx: "",
    filterValue: "",
    getUrl: function () {
        var data = {
            pq_curpage: this.curPage,
            pq_rpp: this.rPP,
            sortIndx: this.sortIndx,
            sortDir: this.sortDir
        };
        if (this.filterIndx && this.filterValue ) {
            data['filterIndx']=this.filterIndx;
            data['filterValue']=this.filterValue;
        }
        return { url: "remote.php", data: data };
    },
    getData: function ( dataJSON ) {
        return { curPage: dataJSON.curPage, totalRecords: dataJSON.totalRecords, data: dataJSON.data };
    }
};

To load data from a remote server (such as a PHP backend), configure the dataModel with location: "remote". You must specify the dataType (e.g., "JSON"), the HTTP method (e.g., "GET" or "POST"), and provide a getUrl function that returns the endpoint URL. Additionally, use the getData function to transform the server response into the format expected by the grid.

Key dataModel properties for remote integration:

• location: Set to "remote" to enable AJAX loading.
• dataType: The format of the incoming data (e.g., "JSON").
• method: The HTTP request method.
• getUrl: A function returning an object { url: 'your_endpoint.php' }.
• getData: A function that receives the response and returns the data object (e.g., { data: response }).

var colM = [
    { title: "Order ID", width: 100, dataIndx: "OrderID" },
    { title: "Customer Name", width: 130, dataIndx: "CustomerName" }
];

var dataModel = {
    location: "remote",
    dataType: "JSON",
    method: "GET",
    getUrl : function () {
        return { url: 'remote.php'};
    },
    getData: function ( response ) {
        return { data: response };
    }
};

var grid1 = $"div#grid_php".pqGrid({
    width: 900, 
    height: 400,
    dataModel: dataModel,
    colModel: colM,
    title: "Shipping Orders"
});

To perform sorting on the server side (remote sorting), configure the dataModel with sorting: "remote". When a user clicks a column header to sort, the grid will trigger the getUrl function. You must implement a server-side script (e.g., remote.php) that receives the sorting parameters and returns the sorted dataset in the format specified by getData.

Key dataModel properties for remote sorting:

• sorting: "remote": Enables server-side sorting logic.
• location: "remote": Indicates data is fetched from a remote source.
• getUrl: A function that returns the URL and the data payload to be sent to the server. It receives a ui object and provides access to this.sortIndx (the index of the column being sorted) and this.sortDir (the direction of the sort).
• getData: A function to wrap the server response. For a standard JSON response, it typically returns { data: data }.

var dataModel = {
    location: "remote",            
    sorting: "remote",
    dataType: "JSON",
    method: "POST",
    sortIndx: "OrderID",            
    getUrl: function(ui){
        return {
            url: "remote.php",
            data: {
                sortIndx: this.sortIndx,
                sortDir: this.sortDir
            }
        };
    },
    getData: function ( data ) {                
        return { data: data };                
    }
};

var grid1 = $("div#grid_php").pqGrid({
    width: 900, 
    height: 400,
    dataModel: dataModel,
    colModel: colM,  
    bottomVisible: false,
    title: "Shipping Orders"
});