0

I'm using PostgreSQL's Full Text Search in Django.

My data is stored in a tree structure, like so:

- note 100            #This is a tree
    - note 341
        - note 422

- note 101            #This is another tree
    - note 218
    - note 106

In the database, each note is just an individual row with a link to its parent:

id  | note_body   | parent_id
-----------------------------
341 | "foo"       | 100
422 | "bar"       | 341
...

This makes it possible to retrieve a single tree (i.e. several individual notes) at once.

My question is: How can I use full text where each document is a whole tree, rather than a single note?

In this example, searching for "foo" or "bar" should return note 100, which is the root of the tree that contains that word.

I would like to do this using Django's full text search API.

gatlanticus
  • 1,158
  • 2
  • 14
  • 28
  • I think you have two separate problems, (1) find the notes that match certain text, for which you can perfectly use full text search and (2) once you have your matching notes, find the root of each note, for this I believe you could use something like [django-mptt](https://github.com/django-mptt/django-mptt) – ivissani Jan 13 '20 at 01:48
  • Actually, that is what I'm already doing, except using django-treebeard's MP trees (the example uses foreign keys for simplicity). However, I think it's quite inefficient to do it that way because it requires many hits to the database to find the root of each note. Full text search is based on documents. Since I'm searching at the level of trees, I think each document should be one tree. – gatlanticus Jan 13 '20 at 01:59

0 Answers0