CODEWITHSUNDEEP
pythondjangoorm
rustyshackleford
rustyshackleford

Reputation: 731

Querying a Django model and comparing its fields for similarities or near duplicates

I have a model that records changes to other models in my database.

I would like to query for changes that only add a newline character -- \n.

My changes model looks like this:

class Change(models.Model):
    object_type = models.ForeignKey(
        ContentType,
        related_name="object_change_set",
        on_delete=models.CASCADE,
    )
    object_id = models.CharField(max_length=255, db_index=True)
    object = GenericForeignKey("object_type", "object_id")
    old_fields = models.JSONField(encoder=JSONEncoder, null=True, blank=True)  
    new_fields = models.JSONField(encoder=JSONEncoder, null=True, blank=True)

Essentially, I want to find instances of Change where the difference between a value for a key in old_fields and new_fields is \n.

Here's what new_fields look like:

{
    "body": "Had a great time on Wednesday.",
    "created": "2022-06-15T19:49:06.784Z",
}

And similarly, old_fields:

{
    "body": "Had a great time on Wednesday.\n",
    "created": "2022-06-15T19:49:06.784Z",
}

Note that new_fields and old_fields are both JSONFields.

Ultimately, I want to remove the Change instances that only add \n to the body.

I realize that I could do this by iterating over the queryset and looking for these discrepancies, but I'm wondering if there's a more elegant solution.

Upvotes: 0

Views: 60

Answers (1)

yyyyyyyan
yyyyyyyan

Reputation: 420

You can use the __startswith (along with an F() expression) and __endswith filters to check if the old_fields body starts with the new_fields body and ends with a :

from django.db.models import F

queryset = Change.objects.filter(
    old_fields__body__startswith=F("new_fields__body"), old_fields__body__endswith="\n"
)

This would, however, still match if the old_fields body had any string between the end of the new_fields body and the ending line break (e.g. "Had a great time on Wednesday.aaaaaaaaaaaaaaaaaa\n"). To fix that, you can also annotate the queryset with a Length function and check if the old_fields body's length is equal to Length("new_fields__body") + 1:

from django.db.models import F
from django.db.models.functions import Length

queryset = Change.objects.annotate(old_body_len=Length("old_fields__body")).filter(
    old_fields__body__startswith=F("new_fields__body"),
    old_fields__body__endswith="\n",
    old_body_len=Length("new_fields__body") + 1,
)

This should work!

Upvotes: 1

Related Questions