def generate_query_string(cls, data_view, start_index, limit,
last_submission_time, all_data, sort):
additional_columns = [GEOLOCATION] \
if data_view.instances_with_geopoints else []
if has_attachments_fields(data_view):
additional_columns += [ATTACHMENTS]
if all_data:
sql = u"SELECT json FROM logger_instance"
columns = None
elif last_submission_time:
sql = u"SELECT json->%s FROM logger_instance"
columns = [SUBMISSION_TIME]
else:
# get the columns needed
columns = data_view.columns + DEFAULT_COLUMNS + additional_columns
field_list = [u"json->%s" for i in columns]
sql = u"SELECT %s FROM logger_instance" % u",".join(field_list)
where, where_params = cls._get_where_clause(
data_view, data_view.get_known_integers(),
data_view.get_known_dates())
sql_where = ""
if where:
sql_where = u" AND " + u" AND ".join(where)
sql += u" WHERE xform_id = %s " + sql_where \
+ u" AND deleted_at IS NULL"
params = [data_view.xform.pk] + where_params
if sort is not None:
sort = ['id'] if sort is None\
else sort_from_mongo_sort_str(sort)
sql = u"{} {}".format(sql, json_order_by(sort))
params = params + json_order_by_params(sort)
elif last_submission_time is False:
sql += ' ORDER BY id'
if start_index is not None:
sql += u" OFFSET %s"
params += [start_index]
if limit is not None:
sql += u" LIMIT %s"
params += [limit]
if last_submission_time:
sql += u" ORDER BY date_created DESC"
sql += u" LIMIT 1"
return (
sql,
columns,
params,
)
def generate_query_string(cls, data_view, start_index, limit,
last_submission_time, all_data, sort,
filter_query=None):
additional_columns = [GEOLOCATION] \
if data_view.instances_with_geopoints else []
if has_attachments_fields(data_view):
additional_columns += [ATTACHMENTS]
sql = u"SELECT json FROM logger_instance"
if all_data or data_view.matches_parent:
columns = None
elif last_submission_time:
columns = [SUBMISSION_TIME]
else:
# get the columns needed
columns = data_view.columns + DEFAULT_COLUMNS + additional_columns
# field_list = [u"json->%s" for i in columns]
# sql = u"SELECT %s FROM logger_instance" % u",".join(field_list)
where, where_params = cls._get_where_clause(
data_view,
data_view.get_known_integers(),
data_view.get_known_dates(),
data_view.get_known_decimals())
if filter_query:
add_where, add_where_params = \
get_where_clause(filter_query, data_view.get_known_integers(),
data_view.get_known_decimals())
if add_where:
where = where + add_where
where_params = where_params + add_where_params
sql_where = ""
if where:
sql_where = u" AND " + u" AND ".join(where)
if data_view.xform.is_merged_dataset:
sql += u" WHERE xform_id IN %s " + sql_where \
+ u" AND deleted_at IS NULL"
params = [tuple(list(
data_view.xform.mergedxform.xforms.values_list('pk', flat=True)
))] + where_params
else:
sql += u" WHERE xform_id = %s " + sql_where \
+ u" AND deleted_at IS NULL"
params = [data_view.xform.pk] + where_params
if sort is not None:
sort = ['id'] if sort is None\
else sort_from_mongo_sort_str(sort)
sql = u"{} {}".format(sql, json_order_by(sort))
params = params + json_order_by_params(sort)
elif last_submission_time is False:
sql += ' ORDER BY id'
if start_index is not None:
sql += u" OFFSET %s"
params += [start_index]
if limit is not None:
sql += u" LIMIT %s"
params += [limit]
if last_submission_time:
sql += u" ORDER BY date_created DESC"
sql += u" LIMIT 1"
return (sql, columns, params, )
def _get_sort_fields(sort):
sort = ['id'] if sort is None else sort_from_mongo_sort_str(sort)
return [i for i in _parse_sort_fields(sort)]
def _get_sort_fields(sort):
sort = ['id'] if sort is None else sort_from_mongo_sort_str(sort)
return [i for i in _parse_sort_fields(sort)]
