[Fixed]-How to decrease number (575) of queries in a nested loop?

by

1👍

I believe you can accomplish that with just a query with some joins and sums.

If you provide the schema for your database I can try to elaborate the answer, for now I will assume the following.

create table user (
    id integer primary key,
    active boolean);

create table product (
    id integer primary key, 
    user integer non null, 
    price integer non null,
    foreign key(user) references user(id));

create table product_buyer (
    id integer primary key,
    product integer,
    buyer integer,
    foreign key(product) references product(id),
    foreign key(buyer) references buyer(id));

create table buyer (
    id integer primary key,
    active boolean,
    last_offer integer);

You should get what you want from:

select (
    user.id, 
    sum(case when product.price > buyer.last_offer then 1 end) as expensive, 
    sum(case when product.price = buyer.last_offer then 1 end) as same,
    sum(case when product.price < buyer.last_offer then 1 end) as cheap)
from 
    user join product on user.id=product.user 
    join product_buyer on product.id=product_buyer.product 
    join buyer on product_buyer.buyer=buyer.id 
where user.active=1 and buyer.active=1 
group by user.id;

You can have a look at django docs for conditional expressions for the CASE statement here.

Hope it helps.

Edit:
I tried to translate the query to django (untested) with your model.

Product.objects.filter(
    user=my_user, 
    active=True, 
    buyers__valid=True,
    buyers__offers__valid=True
).annotate(
    max_date=Max("buyers__offers__datetime")
).filter(
    datetime=F("max_date")
).annotate(
    expensive=Case(
        When(buyers__a_price__gt=F("buyers__offers__eur_price"),
             then=Value(1))
    ), 
    same=Case(
        When(buyers__a_price=F("buyers__offers__eur_price"),
             then=Value(1))
    ),
    cheap=Case(
        When(buyers__a_price__lt=F("buyers__offers__eur_price"),
             then=Value(1))
    )
).annotate(
    n_expensive=Sum("expensive"),
    n_same=Sum("same"),
    n_cheap=Sum("cheap")
).values("user", "n_expensive", "n_same", "n_cheap")

I’m not sure if there is a way to write it in a more concise way, this is the furthest I’ll go without actually making a django test app to check it out.
I’ll leave the refinement to you since you ultimately have the test model, however given the SQL above the translation process should only be a matter of going through the django docs.

👤qwattash

Leave a comment