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.
try if this works
filters=[["current_invoice_end","between",["2020-05-15","2020-05-31"]]]
1 Like
Awesome, I’ll keep testing, but so far it seems to work.
I added a pull request to add this to the documentation: https://github.com/frappe/frappe_io/pull/312
1 Like
Since we are talking about OR
Is it possible to implement
(where … (column_a = ‘a’ OR column_b = ‘a’) and …)
using frappe.db.get_list
Frappe filter expressions support IN rather than OR
IN is more compact than a compound expression of OR clauses.
Here’s an IN filter expression example How to filter Tasks on both Open OR Overdue Tasks - #7 by clarkej
1 Like
It seemed like doing
frappe.db.get_list('Subscription', filters={
'current_invoice_end': ['<', end_period],
'current_invoice_end': ['>', start_period]
})
did give me an OR, but I didn’t test it extensively.
How about OR for two different columns?
jasonh’s filter example queries two different fields -
frappe@ubuntu1804lts:~/frappe-bench$ bench mariadb
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 101543
Server version: 10.2.31-MariaDB-1:10.2.31+maria~bionic-log mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [1bd3e0294da19198]> desc tabSubscription;
+----------------------------------+---------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------------+---------------+------+-----+----------+-------+
| name | varchar(140) | NO | PRI | NULL | |
| creation | datetime(6) | YES | | NULL | |
| modified | datetime(6) | YES | MUL | NULL | |
| modified_by | varchar(140) | YES | | NULL | |
| owner | varchar(140) | YES | | NULL | |
| docstatus | int(1) | NO | | 0 | |
| parent | varchar(140) | YES | MUL | NULL | |
| parentfield | varchar(140) | YES | | NULL | |
| parenttype | varchar(140) | YES | | NULL | |
| idx | int(8) | NO | | 0 | |
| additional_discount_percentage | decimal(18,6) | NO | | 0.000000 | |
| days_until_due | int(11) | NO | | 0 | |
| tax_template | varchar(140) | YES | | NULL | |
| _comments | text | YES | | NULL | |
| start | date | YES | | NULL | |
| status | varchar(140) | YES | | NULL | |
| _liked_by | text | YES | | NULL | |
| cancel_at_period_end | int(1) | NO | | 0 | |
| additional_discount_amount | decimal(18,6) | NO | | 0.000000 | |
| _assign | text | YES | | NULL | |
| trial_period_start | date | YES | | NULL | |
| generate_invoice_at_period_start | int(1) | NO | | 0 | |
| _user_tags | text | YES | | NULL | |
| customer | varchar(140) | YES | | NULL | |
| apply_additional_discount | varchar(140) | YES | | NULL | |
| trial_period_end | date | YES | | NULL | |
| current_invoice_end | date | YES | | NULL | |
| current_invoice_start | date | YES | | NULL | |
| cancelation_date | date | YES | | NULL | |
+----------------------------------+---------------+------+-----+----------+-------+
29 rows in set (0.00 sec)