Django models queries use join

Question

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 in Winner model,what I want is a winner who has mobile number or who get the sms.So must join PrizeVerificationCodeSMSLog 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 model B which has a Foreign Key to A, do not try to use filter(). Because A don't know B,but B know A!Just retrieve A base B.

You can put name to a relationship when you add a foreign key, then A will know B through that named relationship. Check out ForeignKey.related_name and ForeignKey.related_query_name in the docs. — Raiyan
– Raiyan, Commented Jul 1, 2014 at 13:37

fragles · Accepted Answer · 2014-01-15 09:07:20Z

4

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/

edited Jan 15, 2014 at 9:07

answered Jan 15, 2014 at 9:00

fragles

6806 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mithril Over a year ago

logs.select_related("winner")[:10] not work,it just return 10 PrizeVerificationCodeSMSLog,but I can get winner use logs.winner.It's all right,thank you.

Collectives™ on Stack Overflow

Django models queries use join

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related