CODEWITHSUNDEEP
pythonsqlsqlalchemy
koopmac
koopmac

Reputation: 966

How to filter a query by columns only if another condition is met

I have a query:

query = Usage.query.filter_by(site_id=self.site_id, invoice_num=self.invoice_num, supply_charge=self.supply_charge)

And want to filter by dates:

.filter(Usage.start_date>four_months_ago)

only if the row getting filtered has the same value:

if subtotal == self.subtotal

Is there a better way of doing this than making a for loop? It seems very inefficient.

for row in query.all():
    if self.start_date > four_months_ago:
        if row.subtotal != self.subtotal:
            del row

Upvotes: 1

Views: 96

Answers (1)

Danila Ganchar
Danila Ganchar

Reputation: 11302

Condition

(subtotal == self.subtotal) & (Usage.start_date > four_months_ago)

looks like

WHERE start_date > 'val1' AND subtotal = val2

otherwise, OR subtotal != val2. You can try to use or_ + and_. Just an example:

subtotal = 1
for user in Usage.query.filter(
    Usage.site_id == 2,
    Usage.invoice_num == 3,
    Usage.supply_charge == 4,
    or_(
        and_(Usage.start_date > for_month_ago, Usage.subtotal == subtotal),
        Usage.subtotal != subtotal,
    )
):
    print(user)

Upvotes: 1

Related Questions