CODEWITHSUNDEEP
djangodjango-queryset
tannerli
tannerli

Reputation: 594

Filter django queryset over a concatenated extra field

I have the following (shortened) Django-models:

class File(models.Model):
    name = models.TextField()
    directory = models.ForeignKey(Directory)

class Directory(models.Model):
    path = models.TextField()

The user normally has the files displayed to him as path + '/' + name, f.e. '/bin' + '/' + 'bash' => '/bin/bash'. I am no trying to provide a search field to look for files.

If i work with Q()-Objects I can search for matches in either path or name, but it will fail if the user searches for '/bin/bash'.

So far I've come up with:

files = File.objects.extra(select={'fullpath': 'path || "/" || name'},
    tables=['directories'], order_by= ['fullpath']).distinct()

Unfortunately this does some sort of joining ALL directories with ALL filenames first, not only the ones that actually exist. Further, I cannot add another filter

.filter(fullpath__icontains=query)

Because I cannot reference the extra-field.

If at all possible, I'd want to stay with the django OR-Mapper and not perform raw SQL on the db.

Thanks for all suggestions

Upvotes: 2

Views: 1809

Answers (1)

CrazyGeek
CrazyGeek

Reputation: 3437

You may use DB function concat in extra as i have done this to concat date in my case.

Event.objects.all().order_by('-start_time').extra(select={'date':'concat(year(start_time),\"/\",month(start_time),\"/\",day(start_time))','time':'concat(hour(start_time),\":\",minute(start_time))','program_no':'program_id'}).values('id','program_no','event_type__name','venue__city','venue__state','venue__website','series_id','date','time')

Hope this will help you.

Thanks.

Upvotes: 2

Related Questions