tortoise / tortoise/tortoise-orm

Tortoise orm F expression

Open
#1,561 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Python
Stars
5.6k
Forks
516
Avg merge
2d 21h
Merged PRs (30d)
9

Description

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....

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the Product.filter().annotate() query with tortoise.expressions.F and compare the ORM-generated SQL with the working raw SQL shown in the issue. Trace how F("products.id") is resolved and quoted, then verify that the generated query references the products table correctly and no longer raises the reported PostgreSQL error.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, python
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.