The DataSay we'd like to create an autocomplete field for the list of Ubuntu releases, stored on our Postgres server:
Note that the
Modeldata structure (required by the combobox, and which mirrors our database table) conflates the three fields (
version) into a single
releasefield, with a
convertfunction to specify the layout of the data that the user will see.
The BackendThe widget is fed by our Python Flask WSGI backend, with which it communicates using JSON:
Making It a Little SmarterTo access the database, the backend code uses little_pger, a small module I wrote, which acts as a very thin (but Pythonic!) layer above Psycopg2 and SQL. I use it as a replacement for an ORM (as I don't really like them) and thus call it a "pseudo-ORM". In this context, it does us a nice favor by solving the problem of searching through all the fields of the table, by AND-matching, in any order, the user-supplied tokens (e.g. "lynx 10.04 lucid" should match). For this, the
whereparameter of the
little_pger.selectfunction offers a nice syntactic flexibility, as those examples show:
So in our context, the trick is to use the last pattern (i.e.
set-based), with the concatenated (
||) fields we are interested in searching, which could successfully match a search for "lynx 04 lucid" with this SQL query, for example:
A more complete version of this tribute's code is available on GitHub.