SQLContainer and ComboBox filtering

Vaadin 6 / 7 / 8 Framework Data Binding
1

I’m using SQLContainer as a datasource for a ComboBox. I construct the container with a FreeformQuery to which I set my own FreeformStatementDelegate implementation.

I want to use prefix filtering in the combo box, so I set the filtering mode to FILTERINGMODE_STARTSWITH. When user types into the combo box, the Select-class builds a SimpleStringFilter upon the invocation of paintContent(), and this filter is set to my FreeformStatementDelegate via the setFilters()-method. Then, in my FreeformStatementDelegate implementation I use the filter to construct the SQL-query, basicly add a “WHERE column_name LIKE ‘prefix_string%’” clause to the query.

This all works fine, except I don’t like the fact that a database query is executed every time the filtering is changed, i.e. every time user types or deletes a character in the combo box.

The result set in the combo box is fairly small, less than 200 items. It would be optimal if I could use in-memory filtering to narrow down the results, since I know the all the rows are already in the combo box and I only need to filter them by matching a string prefix rule. I don’t want to fetch data from database which is already in the combo box! I want to perform an query to the database only when the filter doesn’t apply to the items that are already in the container. Any ideas how to achieve this?

2

Hi,

The SQLContainer filtering is designed for larger datasets and therefore the filtering is always delegated to the database. In fact, it would probably require quite extensive changes to SQLContainer if one would implement in-memory filtering on top of the database query based filtering. It’s doable alright but as far as I know has not been scheduled for the near future. Patches are welcome ;)

If the result set in your combo box is always about that small and does not change often, you could just populate another container with the items obtained from an unfiltered SQLContainer and use that in your combo box. Not optimal either but would cut down on the amount of db queries.

–
Tepi

3

When I look at the source code for
SQLContainer.java
, I see what looks like in-memory filtering. I don’t see any database-based filtering. I’m I mis-understanding?

From
SQLContainer.java
 of Vaadin 6.7.1.

    private boolean itemPassesFilters(RowItem item) {
        for (Filter filter : filters) {
            if (!filter.passesFilter(item.getId(), item)) {
                return false;
            }
        }
        return true;
    }
4

I’m not an expert with SQLContainer, but: most filtering is performed in the database (see e.g.
DefaultSQLGenerator
 and
QueryBuilder
). However, in some cases such as adding new items, in-memory filtering with the same filter instances is performed - usually as an optimization.