CODEWITHSUNDEEP
pythondjangodjango-querysetdjango-orm
HAYTHAM
HAYTHAM

Reputation: 1

Joining multiple models in order to get an orm query in Django?

I have to return the average length of the most sold tracks in a certain time, using 3 models i manage to form an sql query that looks something like this

select  AVG(t.Milliseconds) as lengthTrack
from invoice_items as il
    join invoices as i on i.invoiceid = il.invoiceid
    join tracks as t on t.TrackId = il.TrackId 
where i.InvoiceDate between '2012-01-01' and '2013-12-31'
order by il.Quantity DESC LIMIT 10

I manage to come up with this as I got really confused about how to join both tables in order to get the average length track of the 10 most sold ones :

start_date = datetime.date(2012, 01, 01)
end_date=datetime.date(2013, 12, 31)

InvoiceItem .objects.filter(ino_date__range=(start_date, end_date),)

model.py

class Invoice(models.Model):
    id                = models.AutoField(primary_key=True, db_column='InvoiceId')
    customerid        = models.ForeignKey(Customer, db_column='CustomerId', on_delete=models.CASCADE)
    invoicedate       = models.DateTimeField(db_column='InvoiceDate')
    billingaddress    = models.TextField(max_length=70, db_column='BillingAddress', blank=True, null=True)
    billingcity       = models.TextField(max_length=40, db_column='BillingCity', blank=True, null=True)
    billingstate      = models.TextField(max_length=40, db_column='BillingState', blank=True, null=True)
    billingcountry    = models.TextField(max_length=40, db_column='BillingCountry', blank=True, null=True)
    billingpostalcode = models.TextField(max_length=10, db_column='BillingPostalCode', blank=True, null=True)
    total             = models.TextField(db_column='Total')

class Track(models.Model):
    id           = models.AutoField(primary_key=True, db_column='TrackId')
    playlist     = models.ManyToManyField(Playlist, through='PlaylistTrack')
    name         = models.TextField(db_column='Name', max_length=200)
    albumid      = models.ForeignKey(Album, db_column='AlbumId', blank=True, null=True, on_delete=models.CASCADE)
    mediatypeid  = models.ForeignKey(MediaType, db_column='MediaTypeId', on_delete=models.CASCADE)
    genreid      = models.ForeignKey(Genre, db_column='GenreId', blank=True, null=True, on_delete=models.CASCADE)
    composer     = models.TextField(db_column='Composer', blank=True, null=True, max_length=220)
    milliseconds = models.IntegerField(db_column='Milliseconds')
    bytes        = models.IntegerField(db_column='Bytes', blank=True, null=True)
    unitprice    = models.DecimalField(db_column='UnitPrice', max_digits=5, decimal_places=2)

class InvoiceItem(models.Model):
    id        = models.AutoField(primary_key=True, db_column='InvoiceLineId')
    invoiceid = models.ForeignKey(Invoice, db_column='InvoiceId', on_delete=models.CASCADE)
    trackid   = models.ForeignKey(Track, db_column='TrackId', on_delete=models.CASCADE)
    unitprice = models.DecimalField(db_column='UnitPrice', max_digits=5, decimal_places=2)
    quantity  = models.IntegerField(db_column='Quantity')

serialerzs.py

class InvoiceSerialer(serializers.HyperlinkedModelSerializer):
    class Meta:
        model = Invoice
        fields = ('id', 'total')


class InvoiceItemSerialer(serializers.HyperlinkedModelSerializer):
    class Meta:
        model = InvoiceItem
        fields = '__all__'


class TrackSerialer(serializers.HyperlinkedModelSerializer):
    class Meta:
        model = Track
        fields = '__all__'

in the views do i need to add the 3 serializers in erializer_class? i also wanna ask if my query right ?

Upvotes: 0

Views: 298

Answers (1)

Cameron McFee
Cameron McFee

Reputation: 396

Getting the most sold between dates is going to be a matter of annotating the tracks with how many times they were sold between those dates.

from django.db.models import Count, Q
top_ten_selling_tracks = Track.objects.annotate(
   sales_count=Count( 
      'invoice_item', # count the number of invoices that point to this track
      filter=Q(invoiceid__invoicedate__range=(start_date, end_date)) # only count them if the associated invoice falls in the appropriate range
   )).order_by(
      '-sales_count' # order them by sales descending, to get the top 10
   )[:10] # take the top ten tracks

The average can be done in python (Django can aggregate over a queryset easily, but with your limit constraint it's probably easier to just

return avg(t.milliseconds for t in top_ten_selling_tracks)

Upvotes: 1

Related Questions