How to order by a django property, or alternative solutions

Ok, so I'm new to django and not sure if I'm approaching this correctly, but here goes:

I have a class of incidents, and source of incidents, where you can have multiple sources for a single incident.

class Incident(models.Model):
iid = models.IntegerField(primary_key=True)
person = models.ForeignKey('Person', on_delete=models.SET_NULL, null=True)

@property
def first_reporteddate(self):
return self.source_set.aggregate(first=Min('datereported'))['first']


class Source(models.Model):

sid = models.IntegerField(primary_key=True)
incident = models.ForeignKey('Incident', on_delete=models.SET_NULL, null=True)
url = models.TextField(validators=[URLValidator()])
datereported= models.DateTimeField(null=True, blank=True)


When a new incident is created, I want to require that a source is also created. I want to give users the option to sort the Incident model by either the ID, the person, or the earliest incident source datereported.

Is it possible to have a model sorted by a property (in this case first_reporteddate), or does it need to be a field? How should I go about structuring this process?

1 Answer
1

Is it possible to have a model sorted by a property (in this case first_reporteddate), or does it need to be a field? How should I go about structuring this process?

first_reporteddate

You can not sort on a property at the database level. You an do this in Python, but this will typically not be very efficient.

What you can however do, is construct an annotation. For example:

from django.db.models import Min

Incident.objects.annotate(
first_reporteddate = Min('source__datereported')
).order_by(
'first_reporteddate'
)

So here we let the database annotate every Incident (well those not filtered out in case we .filter(..)), and then we order by this annotation.

Incident

.filter(..)

In case the logic of the property is too complex to get handled at the database level (in case the logic is quite complicated, or requires webservices, filesystems, this can be hard, or even impossible). You can sort in Python itself. But (a) this is typically inefficient, and (b) then the result is a list and thus no longer a queryset you can further filter. For example:

sorted(Incident.objects.all(), key=lambda x: x.first_reporteddate)

Here the result is a list that contains all the Incident instances, which is typically inefficient if you are for example interested in the first, the first three, etc. items. Furthermore in case first_reporteddate returns None, this will raise an error, although you can write extra logic to circumvent this.

Incident

first_reporteddate

None

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.