CODEWITHSUNDEEP
djangopostgresql
rrauenza
rrauenza

Reputation: 6963

Django: Sort and filter rows by specific many to one value

In the provided schema I would like to sort Records by a specific Attribute of the record. I'd like to do this in native Django.

Example:

Query all Records (regardless of Attribute.color), but sort by Attribute.value where Attribute.color is 'red'. Obviously Records missing a 'red' Attribute can't be sorted, so they could be just interpreted as NULL or sent to the end.

Each Record is guaranteed to have one or zero of an Attribute of a particular color (enforced by unique_together). Given this is a one to many relationship, a Record can have Attributes of more than` one color.

class Record(Model):
    pass

class Attribute(Model):
    color = CharField() # **See note below
    value = IntegerField()
    record = ForeignKey(Record)

    class Meta:
       unique_together = (('color', 'record'),)

I will also need to filter Records by Attribute.value and Attribute.color as well.

I'm open to changing the schema, but the schema above seems to be the simplest to represent what I need to model.

How can I:

** I've simplified it above -- in reality the color field would be a ForeignKey to an AttributeDefinition, but I think that's not important right now.

Upvotes: 0

Views: 61

Answers (1)

Jahongir Rahmonov
Jahongir Rahmonov

Reputation: 13723

I think something like this would work:

record_ids = Attribute.objects.filter(color='red', value=10).values_list('record', flat=True)

and 

record_ids = Attribute.objects.filter(color='red').order_by('value').values_list('record', flat=True)

That will give you IDs of records. Then, you can do this:

records = Record.objects.filter(id__in=record_ids)

Hope this helps!

Upvotes: 1

Related Questions