How to use regex in django query to get the shortest match?-CodePudding

I have some bunch of entries in a column of a Postgres database that looks like below

Abcd/*
Abcd/dir1/*
Abcd/dir1/dir2/*
Abcd/dir1/dir2/dir3/*

I am using django ORM to access this information like below

given_path = "/some/path"
access_obj = Access.objects.get(dir_id=given_path) #dir_id is the column name

Now given a path, I need to find the earliest match (any path after * is allowed, hence don't search further) and give the result back.

What I mean is say a path is given Abcd/dir1/dir2, since my first entry itself is Abcd/* so I don't need to look any further and return the first row object itself.

Currently I try to fetch using __icontains check like below

access_obj = Access.objects.get(dir_id__icontains=given_path)

But it would return all the matches and then I am unable to figure out how to target the first match (in this case) and query the object.

I am aware django supports regex on queries but I am not sure what to apply here. Any help will be appreciated.

CodePudding user response：

Annotate each matching result with the length of the matching path, you can then order by this length and select the first/shortest result

from django.db.models.functions import Length

access_obj = Access.objects.filter(
    dir_id__icontains=given_path
).annotate(
    l=Length('dir_id')
).order_by(
    'l'
).first()