1

I'm trying to get subscriptions ending in a given month using the DB API documented here.

I can get before a certain date with:

end_period = datetime.date(2020, 12, 31)
frappe.db.get_list('Subscription', filters={
    'current_invoice_end': ['<', end_period]
})

But how would I specify before end_period and after start_period?

When I tried 

frappe.db.get_list('Subscription', filters={
    'current_invoice_end': ['<', end_period],
    'current_invoice_end': ['>', start_period]
})

It treated it as "OR" and listed things outside of the range.

cross-posted at discuss.erpnext.com

laydros
  • 51
  • 1
  • 5

2 Answers2

2

You can quickly search for "between" in the erpnext src to check implementations. It has been the only reliable source for me. 

"holiday_date": ["between", (self.start_date, self.end_date)],

The solution you posted wont work because Python wont allow two keys with the same name on the dict.

Another solution that will return a list could be 

holidays = frappe.db.sql_list('''select holiday_date, rate from `tabHoliday`
        where
            parent=%(holiday_list)s
            and holiday_date >= %(start_date)s
            and holiday_date <= %(end_date)s''', {
                "holiday_list": holiday_list,
                "start_date": self.start_date,
                "end_date": self.end_date
            })
Sebastian Gomez
  • 131
  • 1
  • 2
1

You can also pass filters as a list:

frappe.db.get_list('Subscription', filters=[
    ['current_invoice_end', '<', end_period],
    ['current_invoice_end', '>', start_period]
])

I would avoid using direct SQL for this!

Rushabh Mehta
  • 1,463
  • 16
  • 15