A Tribute to an Unsung Pattern

I've always been a big fan of the autocomplete UI pattern. Although it was invented way before, I guess it would be fair to say that its web incarnation was made ubiquitous in the wake of the Web 2.0 revolution. It certainly wasn't its most important innovation, but I think this simple idea deserves more praise than it usually receives, because it has a deep impact on the way users interact with an underlying, often unknown data model (the Google search field is certainly the most powerful example). I'd like to pay a small tribute to this modest pattern by showing a possible implementation, using some of my favorite tools: Python, ExtJS and PostgreSQL. Many tutorials already exist for this of course, but I'd like to focus on the particular problem of being maximally tolerant with the user's input (i.e. not impose any structure or order on it), which I solve in a compact way with little_pger, a small Python "pseudo-ORM" module.

The Data

Say we'd like to create an autocomplete field for the list of Ubuntu releases, stored on our Postgres server:

	create table ubuntu (
	id serial primary key,
	adjective text,
	animal text,
	version text
	);
	insert into ubuntu (adjective, animal, version)
	values ('Warty', 'Warthog', '4.10');
	insert into ubuntu (adjective, animal, version)
	values ('Hoary', 'Hedgehog', '5.04');
	insert into ubuntu (adjective, animal, version)
	values ('Breezy', 'Badger', '5.10');
	-- ...for more: https://wiki.ubuntu.com/DevelopmentCodeNames

view raw autocomplete_ubuntu.sql hosted with ❤ by GitHub

The Widget

Our JavaScript client widget will be an instance of the incredibly versatile ExtJS combobox:

	Ext.onReady(function() {
	Ext.define('Ubuntu', {
	extend: 'Ext.data.Model',
	fields: [{
	name: 'release',
	convert: function(v, rec) {
	return Ext.String.format('{0} {1} - {2}',
	rec.raw.adjective,
	rec.raw.animal,
	rec.raw.version);
	}
	}]
	});
	Ext.create('Ext.panel.Panel', {
	renderTo: Ext.getBody(),
	title: 'Ubuntu Releases',
	width: 300,
	bodyPadding: 10,
	layout: 'anchor',
	draggable: true,
	style: 'margin: 20px',
	items: [{
	xtype: 'combo',
	store: Ext.create('Ext.data.Store', {
	model: 'Ubuntu',
	proxy: {
	type: 'ajax',
	url: '/backend/autocomplete',
	reader: {
	type: 'json',
	root: 'data'
	}
	}
	}),
	typeAhead: true,
	displayField: 'release',
	hideLabel: true,
	anchor: '100%',
	minChars: 3,
	listConfig: {
	loadingText: 'Searching...',
	emptyText: 'No matching release found'
	}
	}, {
	xtype: 'component',
	style: 'margin-top:10px',
	html: 'Type at least 3 characters to trigger search'
	}]
	});
	});

view raw autocomplete_combo.js hosted with ❤ by GitHub

Note that the Model data structure (required by the combobox, and which mirrors our database table) conflates the three fields (adjective, animal and version) into a single release field, with a convert function to specify the layout of the data that the user will see.

The Backend

The widget is fed by our Python Flask WSGI backend, with which it communicates using JSON:

	import psycopg2, psycopg2.extras
	import little_pger as db
	from flask import *
	application = Flask('autocomplete-tribute')
	@application.route('/autocomplete', methods=['GET'])
	def autocomplete():
	conn = psycopg2.connect("dbname=autocomplete-tribute user=christian",
	connection_factory=psycopg2.extras.RealDictConnection)
	cursor = conn.cursor()
	where = {}
	if request.args['query']:
	query_tokens = request.args['query'].split()
	fields = ['adjective', 'animal', 'version']
	# MUST be a set in this context!
	where[('||'.join(fields), 'ilike')] = {'%%%s%%' % v for v in query_tokens}
	# else: we want everything!
	return jsonify(success=True,
	data=db.select(cursor, 'ubuntu', where=where, order_by='id'))

view raw autocomplete_backend.py hosted with ❤ by GitHub

Making It a Little Smarter

To 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 where parameter of the little_pger.select function offers a nice syntactic flexibility, as those examples show:

	where = {}
	where['adjective'] = 'Lucid'
	# where adjective = 'Lucid'
	where['adjective'] = ('Warty', 'Dapper')
	# where adjective in ('Warty', 'Dapper')
	# For sequence values, the rules are: a tuple translates
	# to the 'in' operator (as above), a list to a PG array
	# and a set to a conjunction of predicates (see below)
	where[('version::real', '<=')] = 10.04
	# where version::real <= 10.04
	where[('adjective', 'ilike')] = {'%lucid%', '%lynx%'}
	# where adjective ilike '%lucid%' and adjective ilike '%lynx%'

view raw autocomplete_where.py hosted with ❤ by GitHub

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:

	select * from ubuntu where
	adjective || animal || version ilike E'%lynx%' and
	adjective || animal || version ilike E'%04%' and
	adjective || animal || version ilike E'%lucid%'

view raw autocomplete_query.sql hosted with ❤ by GitHub

A more complete version of this tribute's code is available on GitHub.

Tweet