Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Tortoise orm F expression #1561

Open
NoneNameDeveloper opened this issue Feb 27, 2024 · 0 comments
Open

Tortoise orm F expression #1561

NoneNameDeveloper opened this issue Feb 27, 2024 · 0 comments

Comments

@NoneNameDeveloper
Copy link

I making a hard query to the database using Tortoise ORM. I using tortoise.expressions.F to work with my model data, but there is same field names in my models (e.x. id). F field returning an error:

tortoise.exceptions.OperationalError: column "products.id" does not exist
HINT: Perhaps you meant to reference the column "parameters.product_id" or the column "reviews.product_id".

To Reproduce

Product.filter().all()
            .prefetch_related()
            .limit(limit)
            .offset(offset)sorted_products = (sorted_products
            .annotate(
                reviews_avg=Coalesce(Avg(
            'parameters__order_parameters__order__reviews__rate',
 _filter=(Q(Q(parameters__order_parameters__order__reviews__status="accepted") & Q(parameters__order_parameters__order__reviews__product_id=F("products.id"))))),
                    0,
                )
            )

Expected behavior
SQL raw that works:
SELECT "products"."subcategory_id","products"."card_has_sale","products"."created_datetime","products"."card_sale_price","products"."id","products"."card_price","products"."description","products"."order_id","products"."title",COALESCE(AVG(CASE WHEN "reviews"."status"='accepted' and "reviews".product_id=products.id THEN "reviews"."rate" ELSE NULL END),0) "reviews_avg" FROM "products" LEFT OUTER JOIN "parameters" ON "products"."id"="parameters"."product_id" LEFT OUTER JOIN "order_parameters" ON "parameters"."id"="order_parameters"."parameter_id" LEFT OUTER JOIN "orders" "order_parameters__order" ON "order_parameters__order"."id"="order_parameters"."order_id" LEFT OUTER JOIN "reviews" ON "order_parameters__order"."id"="reviews"."order_id" GROUP BY "products"."subcategory_id","products"."card_has_sale","products"."created_datetime","products"."card_sale_price","products"."id","products"."card_price","products"."description","products"."order_id","products"."title" ORDER BY COALESCE(AVG(CASE WHEN "reviews"."status"='accepted' THEN "reviews"."rate" ELSE NULL END),0) ASC LIMIT 20;

SQL raw that return from orm query:
SELECT "products"."order_id","products"."description","products"."subcategory_id","products"."card_sale_price","products"."title","products"."card_price","products"."card_has_sale","products"."id","products"."created_datetime",COALESCE(AVG(CASE WHEN "reviews"."status"='accepted' AND "reviews"."product_id"="products.id" THEN "reviews"."rate" ELSE NULL END),0) "reviews_avg" FROM "products" LEFT OUTER JOIN "parameters" ON "products"."id"="parameters"."product_id" LEFT OUTER JOIN "order_parameters" ON "parameters"."id"="order_parameters"."parameter_id" LEFT OUTER JOIN "orders" "order_parameters__order" ON "order_parameters__order"."id"="order_parameters"."order_id" LEFT OUTER JOIN "reviews" ON "order_parameters__order"."id"="reviews"."order_id" GROUP BY "products"."order_id","products"."description","products"."subcategory_id","products"."card_sale_price","products"."title","products"."card_price","products"."card_has_sale","products"."id","products"."created_datetime" ORDER BY COALESCE(AVG(CASE WHEN "reviews"."status"='accepted' AND "reviews"."product_id"="products.id" THEN "reviews"."rate" ELSE NULL END),0) ASC LIMIT 20

Additional context
I haven't got ideas....

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant