5

How can I filter uuid as text with Django?
For example, I wanted to do like below. 

class Group(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)

Group.objects.filter(id__startswith='000')

In this case, I received django.core.exceptions.FieldError: Related Field got invalid lookup: startswith.

Do I need to use extra?

naohide_a
  • 1,116
  • 2
  • 13
  • 30
  • I'm assuming `id` is a `UUIDField`, but please add your model (and any other relevant code) here so we can be sure and give an accurate answer :) – Luca Bezerra Apr 04 '19 at 02:03
  • @LucaBezerra thanks, you're right. `id` is a `UUIDField`. I added some lines. – naohide_a Apr 04 '19 at 02:07
  • Can you try `__icontains` instead of `__startswith`, just to check? I know it doesn't have the exact behavior you're looking for, but I just want to check if it works. Some places say that `UUID` is not searchable. Other places say that Postgres handles UUIDFields differently than other DBs - apparently it stores the uuid without the dashes. – Luca Bezerra Apr 04 '19 at 02:17
  • Out of curiosity, why would you want to do that? What is the significance of UUIDs that start with `000`? – Selcuk Apr 04 '19 at 02:19
  • @LucaBezerra - The only way to query a UUID like a string is to cast it to a string. See https://stackoverflow.com/a/42772518/1389057 - Remember, casting will mean any indexes on the column will be ignored and will be sequentially scanned. – Trent Apr 04 '19 at 02:19
  • Possible duplicate of [How do i cast char to integer while querying in django ORM?](https://stackoverflow.com/questions/28101580/how-do-i-cast-char-to-integer-while-querying-in-django-orm) – Trent Apr 04 '19 at 02:20
  • @LucaBezerraI tried with `__icontains`. showed similar error `django.core.exceptions.FieldError: Related Field got invalid lookup: icontains`. – naohide_a Apr 04 '19 at 03:02
  • @Selcuk I'd like to filter because the table has a lot of records and heavy, and using offset would take a long time. – naohide_a Apr 04 '19 at 03:05
  • I am able to filter the **`UUIDField`** field with string input (using `Python3.6` , `Django 2.2` and `SQLite3`) – JPG Apr 04 '19 at 05:13
  • @JPG I'm using `Postgres 9.6.11`. I think Postgres is more strict. – naohide_a Apr 04 '19 at 05:25
  • Now I tried with **`Postgres 9.6.11`** and its also working fine – JPG Apr 04 '19 at 06:03
  • @JPG oh, really? thanks for checking. I'm using `Django 2.0.2` and `psycopg2-binary 2.7.5`. So might be little bit old. – naohide_a Apr 04 '19 at 06:26

2 Answers2

1
class Cast(Func):
    function = "CAST"
    template = "%(function)s(%(expressions)s AS varchar)"

Group.objects.annotate(sid=Cast(id)).filter(sid__startswith='000')

Maybe something like that ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nsuidara
  • 11
  • 1
  • This works perfectly! At least on the PostgreSQL backend, the UUID is returned in a human-readable format (e.g. `24348a35-9a20-439a-8b70-57574fb8fcf9`). – Micah Yeager Dec 09 '21 at 15:12
0

As I said in the comments, I'm not sure if UUIDFields are searchable (at least not in the exact way you want). One alternative (although maybe not the most efficient) would be to have a property or an annotated field in your model that contains the string representation of the UUID.

Luca Bezerra
  • 1,160
  • 1
  • 12
  • 23
  • The reality is, anything suggested is going to be hacky and wrong. If the OP needs to wildcard search these values, they should convert it to text, and then instead of default=uuid.uuid4, they insert the string representation of the UUID. Then indexes will work with zero loss in functionality. The only difference will be size - UUIDs are 16bytes being a 128-bit data type, whereas text has overhead, so a UUID will be a minimum of 33 bytes. – Trent Apr 04 '19 at 02:23
  • @Trent My knowledge doesn't allow me to go as far as saying that there is absolutely no 'non-hacky' way of doing this, but I agree with you regarding the only options I can think of. – Luca Bezerra Apr 04 '19 at 02:24