Django models architecture for full-text search in PostgreSQL

Question

I'm building a web-site for a social interaction on a particular topic, based on threads, think of gmail, only public. There will also be some static info in vocabularies, as well as blog, how-tos, knowledge base, etc. It is django+postgres.

One of the most important requirements is a full-text search over all information, regardless of the type of a model. If the exact search phrase appears in the blog, and its twisted sister in messages, than a snippet from the blog entry should appear first in the search results and be followed by a snippet from the message. So, i need a table with all the texts indexed, and the links to _any_other_table_ in the db.

My first idea is to create a separate model with "loose reference", e.g.:

class Content(models.Model):
    obj_id= CharField() # An id of the object of a given model.
    model= CharField(choices=("Message", "BlogEntry", "HowTo", "EntityProfile",))
    content_type= CharField(choices=("subject", "body", "description", "tags",))
    body= TextField()

But it feels kind of wrong... This promises an unnecessary hassle around integrity of references when creating and re-linking instances.

So, the question is - is there any elegant solution that django would provide? What might be the most efficient architecture to solve the problem?

I am not asking for a direct answer, but rather a hint.

Thanks in advance!

Are you looking for this docs.djangoproject.com/en/dev/ref/contrib/contenttypes ? Also don't use CharFields for object ids. In addition your choices is wrong, it must be a tuple of 2-tuples. — rantanplan
– rantanplan, Commented Nov 20, 2012 at 0:45
yeah yeah i know, just simplified it all. thanks for the hint, checking it out now! — funkifunki
– funkifunki, Commented Nov 20, 2012 at 0:50

simon · Accepted Answer · 2013-01-24 08:01:14Z

3

I've had a lot of success working with this snippet, which uses PostgreSQL's tsearch2. I've tweaked it for various different purposes in various different ways, but basically it works very well, and is very easy to implement.

answered Jan 24, 2013 at 8:01

simon

16.5k5 gold badges47 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

funkifunki Over a year ago

that's a valuable info, thanks a lot! for the current project i have already set up haystack + elasticsearch, but will definitely use this snippet when appropriate!

Sнаđошƒаӽ Over a year ago

Any such snippet for sqlite3? Please take a look at my OP and help.

funkifunki · Accepted Answer · 2012-11-20 12:38:10Z

1

Thanks a lot for the hints!

while contenttypes is an ideal for this kind of task, it is really tempting to be DB independent

from what i've read so far, Solr is a reliable search engine, but i think, i'll go and try ElasticSearch via Haystack

thanks again!

answered Nov 20, 2012 at 12:38

funkifunki

1,1692 gold badges13 silver badges24 bronze badges

1 Comment

rantanplan Over a year ago

I've used both Solr and ElasticSearch. I agree ElasticSearch is the way to go.

Collectives™ on Stack Overflow

Django models architecture for full-text search in PostgreSQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related