search

dimagi / django-cte

Common Table Expressions (CTE) for Django
Other
334 stars 46 forks source link

CTEColumn lookup #23

Closed damianoporta closed 4 years ago

damianoporta commented 4 years ago

Hello, in my query i have

.annotate(
                drawdown=Max(cte.col.drawdown),
                total_trades=Count(cte.col.strategy_id),
                pips=Sum(cte.col.profit),
            )

this code works as expected but when i try to use the lookup __lt doing (i added profit_count):

    .annotate(
                drawdown=Max(cte.col.drawdown),
                total_trades=Count(cte.col.strategy_id),
                pips=Sum(cte.col.profit),
                profit_count=Count(Case(
                    When(profit__lt=0, then=1),
                    output_field=IntegerField(),
                )),
            )

i do not know what is the correct way to call __lt in this case. How should i use the lookups?

millerdev commented 4 years ago

Maybe add a is_profit column in the CTE that's based on the Case expression? Then the annotation an be changed like so

.annotate(
                drawdown=Max(cte.col.drawdown),
                total_trades=Count(cte.col.strategy_id),
                pips=Sum(cte.col.profit),
                profit_count=Count(cte.col.is_profit),  # I think you want to use Sum here instead of Count
            )

It will be easier for me to give advice if you can give a working example. Please re-open and provide a full example if the above does not solve your issue.