KendoUI MultiSelect with server side filtering


Today I got a question where someone wanted to use KendoUI AutoComplete but wanted it to look like a KendoUI MultiSelect. When I wonder why he/she wanted to use an AutoComplete the only reason was because the list of elements was huge. So, why not using Server Side Filtering in a MultiSelect?.

KendoUI Server Side Filtering

When you need to manage a large list of elements the solution doing some filtering in the server and just transfer a few of them.

The advantages of this are multiple:

  1. Save bandwidth sending only few elements
  2. Do not render an HTML element for each element returned
  3. Save memory due to less DOM elements
  4. Save memory due to a smaller KendoUI DataSource
  5. Increase performance do to a smaller DOM

So why not always using Server Side Filtering? Sometimes the fact of going back and forth getting new elements is not worthy for a small list of elements and this increases the complexity in the server that needs to deal with filtering.

Think about the number of elements and how they are going to be displayed in screen, if there is no good reason for having all pre-loaded, try doing it in the server.

KendoUI MultiSelect with server side filtering: MySQL table structure

This example will show a KendoUI MultiSelect that allows to choose countries from a MySQL database.

The structure of the countries table in MySQL is:

mysql> desc countries;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | decimal(10,0) | NO   | PRI | NULL    |       |
| name  | varchar(128)  | NO   |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0,00 sec)

mysql> 

And one sample of the content is:

mysql> select * from countries order by name limit 10;
+----+---------------------+
| id | name                |
+----+---------------------+
|  0 | Afghanistan         |
|  2 | Albania             |
|  3 | Algeria             |
|  4 | American Samoa      |
|  5 | Andorra             |
|  6 | Angola              |
|  7 | Anguilla            |
|  8 | Antarctica          |
|  9 | Antigua and Barbuda |
| 10 | Argentina           |
+----+---------------------+
10 rows in set (0,00 sec)

mysql> 

KendoUI MultiSelect with server side filtering: PHP select code

The PHP code for selecting the elements is:

<?php
// Create connection to the database 
// db server:  127.0.0.1 (localhost)
// db user: so
// db password : password
// db name : so
$con = mysqli_connect("127.0.0.1", "so", "password", "so");

// Initialize the list of countries to return
$countries = [];
// Check that we actually get a filter condition
if ($_GET && $_GET['filter']['filters'][0]) {
    // Get the value
    // NOTE: We are not checking other fields in filter since autocomplete will
    //       always send it as "starts with" and ignore case.
    $filter = $_GET['filter']['filters'][0]['value'];
    $where  = "where name like '" . $filter . "%' ";
}
else {
    // Did not get filter, do not build a where clause
    $where = "";
}
// Query MySQL and limit the list of results to 5
$result = mysqli_query($con, "SELECT name FROM countries " . $where . " order by 'name' limit 5");

// Build the array of results
while ($row = mysqli_fetch_array($result)) {
    array_push($countries, array("name" => $row['name']));
}
mysqli_close($con);

// Return it as JSON
header("Content-type: application/json");
echo json_encode($countries);
?>

KendoUI MultiSelect with server side filtering: JavaScript

And finally the MultiSelect definition:

// DataSource where the read URL is the PHP code above
var ds = new kendo.data.DataSource({
    transport      : {
        read: "select.php"
    },
    // Server Side Filtering
    serverFiltering: true
});

// MultiSelect Widget initialization
$("#items").kendoMultiSelect({
    dataValueField: "name",
    dataTextField : "name",
    dataSource    : ds
});
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s