Django Queryset filter

Question

My django table contains a field that contains comma-separated strings.

class RawData(TimeStampModelMixin):
       id = models.UUIDField(primary_key=True)
       media = models.TextField()

media column will hold values like 'Multimedia, TV', 'Multimedia, TV Station'. I need to filter Rawdata table rows such that media column contains 'TV' or 'Multimedia'. __icontains doesn't provide the accurate result. If I do

Rawdata.objects.filter(media__contains='TV')

then it will return both 'Multimedia, TV', 'Multimedia, TV Station' rows. But I need only the 'Multimedia, TV' row.

Is there any way to achieve this via queryset API?

Any reason not to normalize the datamodel, e.g. create a MediaType table relationship or something alike? — Hedde van der Heide, Sep 22 '22 at 12:39
Yes, unfortunately, normalization has been ruled out. We are looking for a solution within the current denormal state. — Saiful Islam, Sep 22 '22 at 14:01
`Rawdata.objects.filter(media__icontains='TV')` this should work — Hemal Patel, Sep 23 '22 at 05:47

NixonSparrow · Answer 1 · 2022-09-22T16:02:42.110

As Hedde suggested, better create model like MediaType and set proper relationship:

class MediaType(models.Model):
       name = models.TextField()

class RawData(TimeStampModelMixin):
       id = models.UUIDField(primary_key=True)
       media = models.ManyToManyField(MediaType)

Then you will be sure, that seeking for a specific type(s) you will not be mistaken with some similar types.

EDIT: because of above solution is not possible for this particular situation, there is an alternative with Q object (symbol | means or):

from django.db.models import Q
Rawdata.objects.filter(Q(media__contains='TV,') | Q(media__endswith='TV'))

As I understand it correctly, every situation that 'TV' is inside media field, it has to be in the end of the string or followed directly by a comma.

Thanks. But at this stage normalizing is out of the question. Know of any other way? — Saiful Islam, Sep 22 '22 at 14:02

score 0 · Answer 2 · answered Sep 22 '22 at 15:49

0

This may not be the best answer. but seeing the exact need you have maybe can do the job with the data you have.

Rawdata.objects.filter(
    media__startswith='Multimedia',
    media__endswith='TV'
)

answered Sep 22 '22 at 15:49

Johnny Herrera

126
4

Django Queryset filter

2 Answers2