tortoise / tortoise/tortoise-orm

Issue with space in PostgreSQL Full Text Search filter

Open
#813 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Python
Stars
5.6k
Forks
516
Avg merge
2d 21h
Merged PRs (30d)
9

Description

Describe the bug
It's awesome that this library added full text search but I seem to be having a problem when I do a filter with a space.
The issue is created when using the __search filter on any model when the value contains a space.

To Reproduce
Using a basic account model the below filter will generate SQL similar to below. The search will work until a space is added.

Account.filter(name__search="test name")
SELECT "accounts"."name" FROM "accounts" WHERE (TO_TSVECTOR("accounts"."name") @@ TO_TSQUERY('test name') OR "accounts"."name"='test name')

This will throw the following error as its not valid in PostgreSQL.

tortoise.exceptions.OperationalError: syntax error in tsquery: "test name"

Expected behaviour
I believe when a query runs it should add in either a & or | between words to make the query valid. I have tested the query with a & added between words and it works correctly.

References
https://stackoverflow.com/questions/46936417/unable-to-fetch-a-word-containing-spaces-using-to-tsvector-and-to-tsquery
https://stackoverflow.com/questions/16465466/postgres-search-query-error-if-space-used/16466057

The docs are slightly limited and I know its a new feature so just let me know if I am just using it wrong.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing Account.filter(name__search="test name") and trace the __search filter's PostgreSQL SQL generation. Compare the generated TO_TSQUERY input with the working query that places an operator between words. Done means a spaced search produces valid PostgreSQL SQL without the reported syntax error.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, python
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.