Flask ORM deal with empty values -> treat as a lowest value or replace by 0

user_6422477 0
Viewed 24

I have script to join to tables Prospect and Campaign base on campaign_id. Next I added a few neccesary columns to my output also window function rank().

Prospect.query.join(
        Campaign, Campaign.id == Prospect.campaign_id
    ).add_columns(
        Campaign.id,
        Campaign.number,
        Campaign.number * Prospect.number_1,
        sqlalchemy.func.rank().over(
            order_by=[(Offers_budget.number * Prospect.number_1).desc(), Prospect.number_1.desc()],
            partition_by=Campaign.id),
    ).all()

I have trouble with my results. In columns number I have null and then query represent it as a bigest value. I would like to replace null by 0. Or I would like to function rank represent null as a lowest value. Is this possible to do this in query or do I need to create logic outside of query?

flask orm python
1 Answers

Result was using coalesce. This function returns the first of it's arguments that are not null. SO as a second argument I provide 0. It looks that:

Prospect.query.join(
        Campaign, Campaign.id == Prospect.campaign_id
    ).add_columns(
        Campaign.id,
        Campaign.number,
        Campaign.number * Prospect.number_1,
        sqlalchemy.func.rank().over(
            order_by=[(coalesce(Campaign.number, 0) * Prospect.number_1).desc(), Prospect.number_1.desc()],
            partition_by=Campaign.id),
    ).all()
user_64224770
Related