Filtering and Sorting

Filtering and sorting the items contained in an SQLContainer is, by design, always performed in the database. In practice this means that whenever the filtering or sorting rules are modified, at least some amount of database communication will take place (the minimum is to fetch the updated row count using the new filtering/sorting rules).

Filtering

Filtering is performed using the filtering API in Vaadin, which allows for very complex filtering to be easily applied. More information about the filtering API can be found in “Filterable Containers”.

In addition to the filters provided by Vaadin, SQLContainer also implements the Like filter as well as the Between filter. Both of these map to the equally named WHERE-operators in SQL. The filters can also be applied on items that reside in memory, for example, new items that have not yet been stored in the database or rows that have been loaded and updated, but not yet stored.

The following is an example of the types of complex filtering that are possible with the new filtering API. We want to find all people named Paul Johnson that are either younger than 18 years or older than 65 years and all Johnsons whose first name starts with the letter “A”:

mySQLContainer.addContainerFilter(
    new Or(new And(new Equal("NAME", "Paul"),
                   new Or(new Less("AGE", 18),
                          new Greater("AGE", 65))),
           new Like("NAME", "A%")));
mySQLContainer.addContainerFilter(
    new Equal("LASTNAME", "Johnson"));

This will produce the following WHERE clause:

WHERE (("NAME" = "Paul" AND ("AGE" < 18 OR "AGE" > 65)) OR "NAME" LIKE "A%") AND "LASTNAME" = "Johnson"

Sorting

Sorting can be performed using standard Vaadin, that is, using the sort method from the Container.Sortable interface. The propertyId parameter refers to column names.

public void sort(Object[] propertyId, boolean[] ascending)

In addition to the standard method, it is also possible to directly add an OrderBy to the container via the addOrderBy() method. This enables the developer to insert sorters one by one without providing the whole array of them at once.

All sorting rules can be cleared by calling the sort method with null or an empty array as the first argument.