CODEWITHSUNDEEP
djangodjango-modelsdjango-querysetdjango-rest-framework
HungryArthur
HungryArthur

Reputation: 1087

Filter Child rows in a Django Queryset

In a queryset I want to return a collection (by pk) and all Sales objects that have a status='ACTIVE'.

I have tried: 

Collection.objects.filter(Sales__status="ACTIVE")

But I get back the collection object with all Sales objects if one of the Sales objects matches the criteria rather than just the ones that have a status of "ACTIVE"

So I have 2 models (and a reference model)

class Collection(models.Model):
    ID = models.AutoField(primary_key=True)
    collection_title = models.CharField(max_length=255)

class Sales(models.Model):
    ID = models.AutoField(primary_key=True)
    sales_title = models.CharField(max_length=255)

class CollectionSales(models.Model):
    COLLECTIONSALES_STATUS_LIST = (
        ('ACTIVE', 'Active'),
        ('REJECT', 'Reject'),
    )
    collection = models.ForeignKey('Collection')
    sales = models.ForeignKey('Sales')
    status = models.CharField(max_length=10, choices=COLLECTIONSALES_STATUS_LIST, default="ACTIVE")
    class Meta:
        db_table = "myschema_collection_sales"
        managed=False

EDIT: I am trying to fit this into the Rest-Framework so that the output would be: 

{
"count": 2, 
"next": null, 
"previous": null, 
"results": [
    {
        "ID": "1", 
        "collection_title": "My Collection 1", 
        "Sales": [
              {
                 "ID": 12,
                 "sales_title": "my sales title 12",
                 "status": "ACTIVE"
              }
         ]
    }
    ,
    {
        "ID": "2", 
        "collection_title": "My Collection 2", 
        "Sales": [
              {
                 "ID": 4,
                 "sales_title": "my sales title 4",
                 "status": "ACTIVE"
              },
              {
                 "ID": 5,
                 "sales_title": "my sales title 5",
                 "status": "ACTIVE"
              }
         ]
    }
]
}

Upvotes: 0

Views: 2077

Answers (1)

RickyA
RickyA

Reputation: 16029

You have to build the datastructure yourself. What you want is essentialy two queries and you cant fetch that in one call.

data = {"collection":None, "sales":[]}
for coll in Collection.objects.filter(sales__status="ACTIVE"):
   data["collection"] = coll
   for collsales in coll.collectionsales_set.filter(status="ACTIVE")
      data["sales"].append(collsales.collection)

Upvotes: 1

Related Questions