CODEWITHSUNDEEP
django
Lucas03
Lucas03

Reputation: 2347

how to get latest foreign key value in models.py

I have a little problem with getting latest foreign key value in my django app. Here are my two models:

class Stock(models.Model):
    ...

class Dividend(models.Model):
    date = models.DateField('pay date')
    stock = models.ForeignKey(Stock, related_name="dividends")
    class Meta:
        ordering = ["date"]

I would like to get latest dividend from stock object. So basically this - stock.dividends.latest('date'). However, everytime I call stock.dividends.latest('date'), it fires up sql query to get latest dividend. I have latest() method in for cycle for every stock I have. I would like to avoid these sql queries. May I somehow define new method in class Stock that would get latest dividend within sql query for stock object?
I cannot change default ordering from "date" to "-date".
Using select_related('dividends') loads dividends objects with stock, but latest probably uses order_by and it requires sql query anyway. :(

EDIT1: To make more clear what I want, here is an example. Let's say I have 100 symbols in shares.keys():

for stock in Stock.objects.filter(symbol__in=shares.keys()): # 1 sql query
    latest_dividend = stock.dividends.latest('date')                    # 100 sql queries
    ... #do something with latest dividend

Well and in some cases I might have 500 symbols in shares.keys(). That is why I need to avoid making sql queries on getting latest dividend for stock.

Upvotes: 1

Views: 2788

Answers (5)

Iman Kermani
Iman Kermani

Reputation: 949

As long as I understood you can do it this way:

stock.dividends.last()

as implementation in Django is like this:

    def first(self):
    """Return the first object of a query or None if no match is found."""
    for obj in (self if self.ordered else self.order_by('pk'))[:1]:
        return obj

Also, you can use .latest(*fields, field_name=None) too.

Upvotes: 0

Nguyễn Trí Tuân
Nguyễn Trí Tuân

Reputation: 21

I have the same problem with you, so I tested many Django queries. Finally, I found out that we can use this:

Stock.objects.all().annotate(latest_date=Max('dividends__date')).filter(dividends__date=F('latest_date')).values('dividends')

Upvotes: 2

likeon
likeon

Reputation: 791

I'm not sure my solution is the best, but here it is (works only with PostgreSQL):

stocks = list(Stock.objects.filter(**something))
dividends = Dividend.objects.filter(
    stock__in=stocks,
).order_by(
    'stock_id',
    '-date'
).distinct(
    'stock_id',
)

dividends_dict = {d.stock_id: d for d in dividends}

for stock in stocks:
    stock.latest_dividend = dividends_dict.get(stock.id)

Upvotes: 1

Lucas03
Lucas03

Reputation: 2347

likeon: Thanks for your answer. But I think I can avoid initializing that large dictionary (I have 5000 stocks and 280 000 dividends). But your list gave me an idea. Your code requires 2 sql queries. Here is my example (EDIT1). 

for stock in Stock.objects.filter(symbol__in=shares.keys())\
                          .prefetch_related('dividends'): # 2 sql queries
    latest_dividend = list(stock.dividends.all())[-1]     # 0 sql queries
    ... #do something with latest_dividend

My code also requires 2 sql queries, but I do not have to reorder it and create list from stocks and all 280 000 dividends (I only create dict from current stock dividends every cycle). May be creating one dict is quicker than creating len(shares.keys()) dicts, not sure.

I thought there would be easier solution (avoid creating list/dictionary from dividends), but this is good enough for now. Thanks for answers!

Upvotes: 0

user4409008
user4409008

Reputation:

I'm a little confused by your question, I'm assuming you are trying to access the dividends from your stock object in order to limit your queries to the database. I believe that is the least number queries of possible.

 stock_options = stock.objects.get(pk=your_query)
 order_options = stock.dividend_set.order_by('-date')[:5]

Upvotes: 0

Related Questions