Django Query For Max Value For Column Which Includes Foreign Key Table Also In Result

Question

I have two related models -

class Auction(models.Model):
  uuid = models.UUIDField(primary_key=True, default=uuid4, editable=False)
  # other properties

and

class MaxBid(models.Model):
    uuid = models.UUIDField(primary_key=True, default=uuid4, editable=False)
    value = models.DecimalField(
        decimal_places=2,
        max_digits=10,
        validators=[MinValueValidator(MINIMUM_TRANSACTION_VALUE)]
    )
    created = models.DateTimeField(default=timezone.now)
    auction = models.ForeignKey(
        'Auction',
        on_delete=models.CASCADE,
        related_name='bids'
    )
    user = models.ForeignKey(
        get_user_model(),
        on_delete=models.CASCADE,
        related_name="bids"
    )

I want to get the maximum bid a user has made in an auction and the auction columns in a SINGLE django SQL query.

This SO question - SQL select only rows with max value on a column - has helped me to see a way of doing it in raw sql -

SELECT * FROM "products_maxbid" as m
JOIN "products_auction" as a on m.auction_id = a.uuid
WHERE m.uuid in (
    SELECT m.uuid FROM "products_maxbid" as m
    INNER JOIN (
        SELECT auction_id, Max(value) as value FROM "products_maxbid"
        WHERE user_id = '2'
        GROUP BY auction_id
) n
on m.auction_id = n.auction_id and m.value = n.value and m.user_id = '2'
)
ORDER BY a.square_id # can apply any orders now to the auction table like normal

I don't know where to begin to achieve this in Django - well, the docs obviously, but nothing stands out in there.

All I've worked out is the subquery inside the INNER JOIN -

bids = MaxBid.objects.filter(user=some_user).values("auction").annotate(max=Max('value')).order_by()

When I try to use this with filter it complains that about there being two columns returned in the same query. So I guess the first question is how do I do an INNER JOIN the way the raw SQL does?

Is it even possible through the ORM? This answer gives me some hope but I can't quite wrap my head around it - Django: Record with max element

Update -

I didn't realise how easy raw SQL queries are so will just them

please provide an error after you filter – Andrey Nelubin Mar 21 '20 at 16:51 — Andrey Nelubin, Mar 21 '20 at 16:51

score 0 · Answer 1 · answered Mar 21 '20 at 16:13

#In django you must create a class and import Model , look here (example)

from django.db import models

class Blog(models.Model):
    name = models.CharField(max_length=100)
    tagline = models.TextField()

    def __str__(self):
        return self.name

class Author(models.Model):
    name = models.CharField(max_length=200)
    email = models.EmailField()

    def __str__(self):
        return self.name

class Entry(models.Model):  # pass info  of your tables
    blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
    headline = models.CharField(max_length=255)
    body_text = models.TextField()
    pub_date = models.DateField()
    mod_date = models.DateField()
    authors = models.ManyToManyField(Author)
    number_of_comments = models.IntegerField()
    number_of_pingbacks = models.IntegerField()
    rating = models.IntegerField()

    def __str__(self):
        return self.headline

# How can I see the raw SQL queries Django is running?

# Make sure your Django DEBUG setting is set to True. Then do this:

>>> from django.db import connection
>>> connection.queries
[{'sql': 'SELECT polls_polls.id, polls_polls.question, polls_polls.pub_date FROM polls_polls','time': '0.002'}]

# it's like "embedded queries"

Django Query For Max Value For Column Which Includes Foreign Key Table Also In Result

1 Answers1