Add custom filters on CTE after join about django-cte HOT 9 CLOSED

Hmm, maybe I was looking at the wrong part of the query. I got distracted by the FROM clause and never got down to the WHERE clause. Could you do what you want by filtering on an annotation? You can reference a CTE column in an annotation like so

Then should be able to filter on that annotation by adding a filter clause. Here's an adapted version of that test to demonstrate what I mean.

    def test_filter_on_cte_annotation(self):
        totals = With(
            Order.objects
            .filter(region__parent="sun")
            .values("region_id")
            .annotate(total=Sum("amount"))
        )
        orders = (
            totals
            .join(Order, region=totals.col.region_id)
            .with_cte(totals)
            .annotate(region_total=totals.col.total)
            .filter(region_total=33)  # <--------------- note filter on annotation
            .order_by("amount")
        )

        data = [(o.amount, o.region_id, o.region_total) for o in orders]
        self.assertEqual(data, [
            (10, 'mercury', 33),
            (11, 'mercury', 33),
            (12, 'mercury', 33),
        ])

from django-cte.

The way it's typically done is to add the filter directly the CTE query. In your case it would be

cte = With(
    models.Trade.objects.annotate(
        drawdown = ...
    ).filter(entry_time__gt=value)
)

Here's a bit more advanced example of a CTE that references a value from the outer query in a filter

There are other examples in the tests that may help as well. Please reopen this ticket and post your non-working query if this does not resolve your issue. Preferably, rewrite your query using the test models so I can reproduce the issue.

from django-cte.

@millerdev no, i know that i can add filter on CTE, during the creation of the CTE. I am asking if i can add filters AFTER the:

cte.join(models.Strategy, id=cte.col.strategy_id)
    .with_cte(cte) 

as i explained above, i create the CTE, then i add the CTE to the join and then pass that querySet to django-fitler that apply its filters. if i create the CTE after the filters i will get an error because cte.col.drawdown does not exists if i do not join the CTE.

from django-cte.

I don't think this is possible, but it's possible I am still misunderstanding your intent. Can you post a (non-working) example of what you are trying to do?

from django-cte.

Hi @millerdev

I could be wrong but i think this is what @damianoporta is asking for.
In sql, i can create a cte that selects data from a table and applies a row number window function on each subset (as defined by the partition). Then i can select from the cte and filter out only the rows that have row number = 1.

For example lets say i have testresults that are linked to testcases. I can group the results by testcase, order them by execution date and add a row number to each result. Then i can select the most recent testresult for each testcase. I hope the example is clear.

How would we implement this example using django-cte?

from django-cte.

@adhamselman please post some example SQL that you want djang-cte to produce.

from django-cte.

@millerdev from https://stackoverflow.com/a/3800572

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rk
      FROM PURCHASES p)
SELECT s.*
  FROM summary s
 WHERE s.rk = 1

from django-cte.

Thanks, that is very helpful.

I don't think the Django ORM can produce a query with a FROM clause that does not include a model table, so not sure this is going to be possible currently without doing something hacky like joining to a model/table that has only one row. I think this would require a new feature for django-cte, and possibly django itself. I'll have to think about this some more (and can't prioritize it immediately). Pull requests are welcome!

from django-cte.

@millerdev

Brilliant, thank you sir!

from django-cte.