Compare string alphabetically django db

user_916945 0
Viewed 3203

I have a following model:

class Page(Model):
      book = ForeignKey(Book, on_delete=CASCADE)
      page = IntegerField()
      start = CharField(max_length=350, db_index=True)
      end = CharField(max_length=350, db_index=True)

How do I query DB in order to get pages that "contain" a given word?

page1 = Page.objects.create(start='beaver', end='brother')
page2 = Page.objects.create(start='boy', end='brother')
  • Page.objects.filter("breast" between start and end) should return page1 and page2.
  • Page.objects.filter("beast" between start and end) should return nothing.
  • Page.objects.filter("block" between start and end) should return the only page1, since block is alphabetically after beaver and before brother.

Search should be case-insensitive.

So, I need to write a query that fetches all rows, where start is alphabetically "smaller" than the given word and end is alphabetically "larger" than the given word.

postgresql django-models django python
3 Answers

One option would be to convert all database values for start and end to upper- or lowercase before saving. Then for searching use the filters __gte and __lte (after converting the search term to upper- or lowercase as well).

It seems to work for me (using Python 3.6, Django 2.2, postresql 10):

# create with lowercase words
Page.objects.create(start='beaver', end='brother')
Page.objects.create(start='boy', end='brother')

# filter using lowercase as well
v = request.GET.get('search_term', '').lower()
qs = Page.objects.filter(start__lte=v, end__gte=v)

Or did I misunderstand your question?

user_92256710

You could convert end and start to decimals.

In your model, use DecimalField instead of CharField.

And then you could use the ascii table to convert your words.

For instance, "love" would translate to : 108 111 118 101

So in database it should have the value : 0.108111118101 (values should be < 1 so that the length of the word doesn't mess with the filtering).

"amour" would translate to : 97 109 111 117 114

Note that the ascii code of "a" has only 2 digits, and all letters must have the same numbers of digits (here 3), so pad it with a 0 if that's the case: 0.097109111117114

Then it's easy to query to see if a decimal is between those or not, using lower than (lt) and greater than (gt)

Notes :

  • You can use the getters and setters of your models to translate the word to their ascii value and vice-versa.

  • Convert words to lowercase otherwise it won't work with the ascii table as 'C' has a different value than 'c' for example

  • Using the ascii table won't work with what's not in the latin alphabet. For instance, ç, é, à, è, ù, will probably break the search, you should consider building your own table, or replacing those letters by their base letter...

Now let's see if "django" is between "amour" and "love":

love   : 0.108111118101
django : 0.100106097110103111 
amour  : 0.097109111117114

Yes it is :)

user_33224000

My answer is only applicable to Postgresql, but here may be one solution:

Django with postgresql has a CICharField model field in django.contrib.postgres.fields. This supports indexing on the case-insensitive string as well. The strings will still be stored with their correct case, but comparison operations will be case insensitive.

from django.contrib.postgres.field import CICharField

class Page(Model):
      book = ForeignKey(Book, on_delete=CASCADE)
      page = IntegerField()
      start = CICharField(max_length=350, db_index=True)
      end = CICharField(max_length=350, db_index=True)

This should just about solve your problem, you'll be able to use gte and lte filters and the comparison will be case-insensitive. It should handle unicode just fine depending on the settings in your database.

t1 = "breast"
t2 = "beast"
t3 = "block"
page1 = Page.objects.create(start='beaver', end='brother')
page2 = Page.objects.create(start='boy', end='brother')
Page.objects.filter(start__lte=t1, end__gte=t1)  # <QuerySet [<Page: Page start=beaver, end=brother>, <Page: Page start=boy, end=brother>]>
Page.objects.filter(start__lte=t2, end__gte=t2)  # <QuerySet []>
Page.objects.filter(start__lte=t3, end__gte=t3)  # <QuerySet [<Page: Page start=beaver, end=brother>]>

t4 = "Ù"  # Between Ø and Ú
t5 = "Ü"  # Not between Ø and Ú
page3 = Page.objects.create(start='Ø', end='Ú')
Page.objects.filter(start__lte=t4, end__gte=t4) # <QuerySet [<Page: Page start=Ø, end=Ú>]>
Page.objects.filter(start__lte=t5, end__gte=t5) # <QuerySet []>

This change will generate a migration that installs the CITextExtension() on the database and alters the columns. You might need to separate that migration file into two migrations, where the first would install the CITextExtension and the second would modify your existing columns.

user_7846480
Related