Amit Amit - 6 months ago 21
SQL Question

How to use left outer join in django?

I have Two Models

class Business(models.Model):
BID = models.IntegerField(primary_key=True, default=datetime.now().strftime("%d%y%H%S%m%M%f"))
BusinessName = models.CharField(max_length=150)
ContactPerson = models.CharField(max_length=50)

class BusinessComment(models.Model):
BID = models.ForeignKey(Business, blank=True, null=True)
Rating = models.IntegerField(blank=True, null=True)
Comment = models.TextField(blank=True, null=True)

class BusinessHours(models.Model):
BID = models.ForeignKey(Business, blank=True, null=True)
Day = models.IntegerField(blank=True, null=True)
StartHour = models.CharField(max_length=8, blank=True, null=True)
EndHour = models.CharField(max_length=8, blank=True, null=True)


I want access in single object below values from both models.this concept is left outer join but can I achieve in single object that have all values as below.

BID,BusinessName,ContactPerson,AVG(Rating),Count(Number of Comment) (Condition is Business.BID = BusinessComment.BID)


Business Table Data

BID BusinessName ContactPerson
1 First First
2 Second Second


BusinessHours Table Data

id BID Rating Comment
1 1 3 Comment1
2 1 5 Comment2
3 2 4 Comment3
4 2 5 Comment4


Then result Should be in Object As:

BID Businame ContactPerson Rating Comment
1 First First 4 2
2 Second Second 4.5 2


I have tried many times but I'm Unable to do that.P

EDIT :

today = datetime.datetime.today().weekday() + 1


BusinessHours Table Data

id BID Day Stathours EndHour
145 1 1 12:00am 12:00am
146 1 2 Closed Closed
147 1 3 12:00am 12:00am
148 1 4 Closed Closed
149 1 5 12:00am 12:00am
150 1 6 12:00am 12:00am
151 1 7 12:00am 12:00am
152 2 1 12:00am 12:00am
153 2 2 12:00am 12:00am
154 2 3 12:00am 12:00am
155 2 4 12:00am 12:00am
156 2 5 12:00am 12:00am
157 2 6 12:00am 12:00am
158 2 7 12:00am 12:00am


Now I want to filter data from BusinessHours using
filter(BusinessHours.Day = today)
with annotate.So that output will be as below.

BID Businame ContactPerson Rating Comment StartHour EndHours
1 First First 4 2 12:00am 12:00pm
2 Second Second 4.5 2 12:00am 12:00pm


StartHour and EndHour is give value of Current Day.

AKS AKS
Answer

You can use annotation with aggregation:

from django.db.models import Count, Avg

qs = Business.objcts.annotate(comment_count=Count('businesscomment'), 
                              avg_rating=Avg('businesscomment__rating'))

And, then in the queryset you can access the comment_count or avg_rating:

# I am using these fields in values_list for example
qs.values_list('BID', 'BusinessName', 'ContactPerson', 'comment_count', 'avg_rating')