1

The Problem

The stack is Django 1.6, PostgreSQL 9.3 and Elasticsearch 1.2. In PostgreSQL, I've got data in a table (most columns removed for brevity):

Activity Name                              Location
=============                              ========
Swimming Lessons - Level 1                 Hothouse Pool
Swimming Lessons - Level 2                 Hothouse Pool
Swimming Lessons - Level 3                 Hothouse Pool
Swimming Lessons - Parent & Tot            Trinity Recreation Center
Swimming Lessons - Level 1                 Trinity Recreation Center
Swimming Lessons - Level 2                 Trinity Recreation Center
Swimming Lessons - Level 3                 Trinity Recreation Center
Swimming Lessons - Level 4                 Trinity Recreation Center
Swimming Lessons - Gold Certification      Hothouse Pool
Karate - White Belt                        Trinity Recreation Center
Karate - Yellow Belt                       Trinity Recreation Center
Karate - Black Belt                        Trinity Recreation Center

I'm looking to get the following sort of results as a user types into an autocomplete query box, so it needs to be fast:

  • Swimming Lessons at Hothouse Pool (4 items)
  • Swimming Lessons at Trinity Recreation Center (5 items)
  • Karate at Trinity Recreation Center (3 items)

If a user types s, I'd want the first 2 items to display.

What I think

I think leveraging Elasticsearch for this makes the most sense, but am open to other solutions given the stack mentioned above. An ES completion suggester would be brilliant (and fast!) for this, but I'm not sure how to get the data grouped by Location and similar Activity Names from PostgreSQL in order to index it. Maybe I'm missing an even better approach?

At this point I'm kind of stuck.

Edit 1:

I think this sort of comes close (see Conditional group by (group similar items) in PostgreSQL), but it relies on the contents of the name column to be identical, not just starting with similar words/characters.

Community
  • 1
  • 1
James Addison
  • 3,086
  • 1
  • 17
  • 16

1 Answers1

0

I haven't tried this, but you might be able to leverage something like pg_trgm or fuzzystrmatch modules for this.

Take a look at the answer given on this one, it might help you: Finding similar strings with PostgreSQL quickly

Community
  • 1
  • 1
bratsche
  • 2,666
  • 20
  • 23
  • I did look at that link before posting the original question, but it doesn't quite fit my needs. I'm looking for a solution that doesn't require 'seeding' with a search string - if there is such a solution. – James Addison Jul 02 '14 at 20:57