You can handle your query if you add another field in your Kind, a
ListProperty containing both the start date and end date.
Try this:
class FicaTable(db.Model):
""" a table of salary amounts subject to Social Security tax, by
tax year """
eff_date = db.DateTimeProperty()
exp_date = db.DateTimeProperty()
date_range = db.ListProperty(datetime.datetime)
max_tax_base = db.IntegerProperty()
OASDI_RATES = """\
2001 80400
2002 84900
2003 87000
2004 87000
2005 90000
2006 94200
2007 97500
2008 102000
2009 106800
"""
def flush_tax_tables():
""" flush any leftover data from previous runs
"""
query_all_records = FicaTable.all()
rec_count = query_all_records.count()
if rec_count:
print 'Deleting %d records from datastore' % (rec_count)
for rec in query_all_records:
rec.delete()
def parse_date(yyyy_mm_dd):
return datetime.datetime(*time.strptime(yyyy_mm_dd, "%Y-%m-%d")
[0:3])
def load_tax_tables():
for table_data in OASDI_RATES.splitlines():
(year, max_tax_base) = table_data.split()
eff_date = parse_date(year + '-01-01')
exp_date = parse_date(year + '-12-31')
table_vals = {'eff_date' : eff_date,
'exp_date' : exp_date,
'date_range': [eff_date, exp_date],
'max_tax_base': int(max_tax_base)
}
table_rec = FicaTable(**table_vals)
table_rec.save()
query_all_records = FicaTable.all()
rec_count = query_all_records.count()
print 'Loaded %d tax table records' % (rec_count)
from google.appengine.api.datastore_errors import BadFilterError
def query_tax_tables():
""" this fails with the message: "invalid filter:
Only one property per query may have inequality filters ..."
"""
query_template = "WHERE eff_date >= :1 AND exp_date <= :1"
q = FicaTable.gql(query_template, datetime.date(2005,05,15))
print
print "Trying 'traditional' query with two date fields"
try:
rec = q.fetch(1)
except BadFilterError:
print "I told you this wouldn't work"
""" but this works:
"""
print
print "Trying query against date_range"
query_template = "WHERE date_range >= :1 AND date_range <= :1"
query_date = datetime.date(2005,05,15)
q = FicaTable.gql(query_template, query_date)
table_rec = q.get()
date_str = query_date.isoformat()
# datetime.time.strftime("%Y-%m-%d", query_date)
print 'The maximum amount subject to FICA OASD for %s is %d' % \
(date_str, table_rec.max_tax_base)
if __name__ == "__main__":
flush_tax_tables()
load_tax_tables()
query_tax_tables()
Let me know if anyone has any problems understanding or running the
example.
After I'm sure it's accurate and well-enough documented, I should post
it to the App Engine 'cookbook':
http://appengine-cookbook.appspot.com/
Garrett Davis
On Dec 23, 2:23 pm, maverick <zhihong....@gmail.com> wrote:
> Due to the limitation of GAE's datastore "Inequality Filters Are
> Allowed On One Property Only", a simple date range overlapping query
> become a problem.
>
> I have a Kind which contains 2 properties: date_begin, date_end, I
> need to run a query to find out all rows which overlapping the date
> range given (datebegin2, dateend2). in a SQL query it just simple
> "@StartDate1 <= @EndDate2 AND @StartDate1 <= @EndDate1 " in WHERE
> clause, however in GQL, we have a problem.
>
> Does anyone have a solution for this? Thanks !
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Google App Engine" group.
To post to this group, send email to google-appengine@googlegroups.com
To unsubscribe from this group, send email to
google-appengine+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/google-appengine?hl=en
-~----------~----~----~----~------~----~------~--~---
