QuerySet API

Using Django
1

Hello!

For instance, I have two models (simplified):

class User(models.Model):
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)

class Order(models.Model):
    amount = models.DecimalField(max_digits=19, decimal_places=2)
    user = models.ForeignKey(User, on_delete=models.PROTECT, related_name="orders")

How I can make separated subqueries (with own grouping, ordering, filtering) by QuerySet API in T-SQL way?

SELECT
    u.id,
    u.first_name,
    u.last_name,

    sq1.min_amount,
    sq1.max_amount
FROM
    [User] u

    -- Subquery #1
    LEFT OUTER JOIN (
        SELECT 
            [user_id],
            MIN(amount) min_amount,
            MAX(amount) max_amount
        FROM
            [Order]
        GROUP BY
            [user_id]
    ) sq1 ON u.id = sq1.[user_id]

    -- Subquery #2
    -- LEFT OUTER JOIN (...) sq2

    -- Subquery #3
    -- OUTER APPLY (...) sq3

If I use .annotate(), I don’t get desirable SQL result because grouping runs in main query, not in subquery:

User.objects.values(
    'id',
    'first_name',
    'last_name',
).annotate(
    min_amount=Min('orders__amount'),
    max_amount=Max('orders__amount')
)

If I use Subquery() in .annotate(), subquery runs in SELECT area and it’s not good for the performance, because if I need several fields from one subquery (min_amount and max_amount), sql server runs two queries instead of one.

Is there any solution?

May be I can use .prefetch_related() somehow as a substitution of subquery in T-SQL for each query

Prefetch(
    'orders',
    queryset=Order.objects. #staff with .values(), annotate(Min(), Max()) and etc.
    to_attr='sq1'
)

and then I could use fields from ‘sq1’ in main query User.objects…?

1 Like