append timestamp string for index python

Question

I am using Google App Engine, Standard Environment, NDB Datastore, Python 2.7. There is a limit of 200 indexes per project.

To reduce the number of indexes I am planning to do this:

I have three fields, the report_type, current_center_urlsafe_key and timestamp_entered in a model. I need to find all the entries in the Datastore that has a specific values for current_center_urlsafe_key and report_type. I need to sort these values based on the timestamp_entered (ascending and descending).

This would consume a separate composite-index and I would like to avoid it. To achieve this query, I plan to add a separate entity for every write by combining all three values like this:

center_urlsafe_key_report_type_timestamp    = report_type + "***" + current_center_urlsafe_key + str(current_timestamp_ms)

Then I plan to do a query like this:

            current_timestamp_ms = int(round(time.time() * 1000))
            current_date = date.today()

            date_six_months_back = common_increment_dateobj_by_months(self,current_date, -6)
            six_month_back_timestamp = (date_six_months_back - date(1970, 1, 1)).total_seconds() * 1000             
            center_urlsafe_key_report_type_timestamp    = report_type_selected + "***" + current_center_urlsafe_key + str(six_month_back_timestamp)

            download_reports_forward = download_report_request_model.query(ndb.GenericProperty('center_urlsafe_key_report_type_timestamp') >= center_urlsafe_key_report_type_timestamp).order(ndb.GenericProperty('center_urlsafe_key_report_type_timestamp'))
            download_reports_backward = download_report_request_model.query(ndb.GenericProperty('center_urlsafe_key_report_type_timestamp') >= center_urlsafe_key_report_type_timestamp).order(ndb.GenericProperty('-center_urlsafe_key_report_type_timestamp'))

My question is, if I add a timestamp as a string and add a prefix of report_type+"****"+current_center_urlsafe_key, will the NDB Datastore inequality filter provide the desired results?

Siva Siva · Accepted Answer · 2019-10-07T05:19:38

There is a problem with the strategy. You need to have both ">=" and "<=" filters applied to ensure you are not fetching records from other prefix values. As an example, say your data is as follows

a-123-20191001
a-123-20190901 
a-123-20190801
b-123-20191001
b-123-20190901
b-123-20190801

Now, if you do "key >= a-123-20190801", you would get all the data since 2019/08/01 for the prefix "a-123" but you will also end up with all data starting with "b-" since "b-*" >= "a-123-20190801". But if you do "key >= a-123-20190801 and key <= a-123-20191001" then your data will belong only to that prefix.

append timestamp string for index python

1 Answers