Django models queries use join
Try
logs = PrizeVerificationCodeSMSLog.objects.filter(winner__lucky_draw_id=id).order_by('-created_on')
winners = logs.select_related("winner")[:10]
This generates following query
SELECT "prizeverificationcodesmslog"."id", "prizeverificationcodesmslog"."winner_id",
"prizeverificationcodesmslog"."mobile_number", "prizeverificationcodesmslog"."created_on",
"winner"."id", "winner"."lucky_draw_id", "winner"."participation_id",
"winner"."prize_id", "winner"."mobile_number"
FROM "prizeverificationcodesmslog"
INNER JOIN "winner" ON ("prizeverificationcodesmslog"."winner_id" = "winner"."id")
WHERE "winner"."lucky_draw_id" = 1
ORDER BY "prizeverificationcodesmslog"."created_on"
DESC LIMIT 10;
I am not sure what are your requirements but you may want to agregate by Max PrizeVerificationCodeSMSLog
see https://docs.djangoproject.com/en/1.5/topics/db/aggregation/
Comments
-
Mithril almost 2 years
I want to make query works as follow sql:
sql_str = ''' select * from luckydraw_winner W inner join luckydraw_prizeverificationcodesmslog L on W.id =L.winner_id where W.lucky_draw_id = %s limit 10 '''
models:
class Winner(models.Model): lucky_draw = models.ForeignKey(LuckyDraw) participation = models.ForeignKey(Participation) prize = models.ForeignKey(Prize) mobile_number = models.CharField(max_length=15, null=True, default = None) class PrizeVerificationCodeSMSLog(models.Model): winner = models.ForeignKey(Winner) mobile_number = models.CharField(max_length=15, db_index=True) created_on = models.DateTimeField(auto_now_add=True)
because
mobile_number
isn't always filled inWinner
model,what I want is a winner who has mobile number or who get the sms.So must joinPrizeVerificationCodeSMSLog
to make my purpose.
Only get winner is simple:winners = models.Winner.objects.filter(lucky_draw_id=id).order_by('-created_on')[:10]
But I have no idea what filter can be added to join
PrizeVerificationCodeSMSLog
.
I have finally understood how to retrieve data I want in django.
If you want to get model
A
restricted by another modelB
which has a Foreign Key toA
, do not try to usefilter()
. BecauseA
don't knowB
,butB
knowA
!Just retrieveA
baseB
.