def get_sql_with_params(xform, query=None, fields=None, sort=None, start=None,
end=None, start_index=None, limit=None, count=None,
json_only: bool = True):
records = _get_instances(xform, start, end)
params = []
sort = _get_sort_fields(sort)
sql = ""
known_integers = [
get_name_from_survey_element(e)
for e in xform.get_survey_elements_of_type('integer')]
where, where_params = get_where_clause(query, known_integers)
if fields and isinstance(fields, six.string_types):
fields = json.loads(fields)
if fields:
field_list = [u"json->%s" for _i in fields]
sql = u"SELECT %s FROM logger_instance" % u",".join(field_list)
sql_where = u""
if where_params:
sql_where = u" AND " + u" AND ".join(where)
sql += u" WHERE xform_id = %s " + sql_where \
+ u" AND deleted_at IS NULL"
params = [xform.pk] + where_params
else:
if json_only:
records = records.values_list('json', flat=True)
if query and isinstance(query, list):
for qry in query:
w, wp = get_where_clause(qry, known_integers)
records = records.extra(where=w, params=wp)
else:
if where_params:
records = records.extra(where=where, params=where_params)
# apply sorting
if not count and sort:
if ParsedInstance._has_json_fields(sort):
if not fields:
# we have to do a sql query for json field order
sql, params = records.query.sql_with_params()
params = list(params) + json_order_by_params(
sort, none_json_fields=NONE_JSON_FIELDS)
sql = u"%s %s" % (sql, json_order_by(
sort,
none_json_fields=NONE_JSON_FIELDS,
model_name="logger_instance"))
elif not fields:
records = records.order_by(*sort)
records, sql, params = _start_index_limit(
records, sql, fields, params, sort, start_index, limit
)
return sql, params, records
def get_sql_with_params(xform, query=None, fields=None, sort=None, start=None,
end=None, start_index=None, limit=None, count=None):
records = _get_instances(xform, start, end)
params = []
sort = _get_sort_fields(sort)
sql = ""
known_integers = [
get_name_from_survey_element(e)
for e in xform.get_survey_elements_of_type('integer')]
where, where_params = get_where_clause(query, known_integers)
if fields and isinstance(fields, six.string_types):
fields = json.loads(fields)
if fields:
field_list = [u"json->%s" for _i in fields]
sql = u"SELECT %s FROM logger_instance" % u",".join(field_list)
sql_where = u""
if where_params:
sql_where = u" AND " + u" AND ".join(where)
sql += u" WHERE xform_id = %s " + sql_where \
+ u" AND deleted_at IS NULL"
params = [xform.pk] + where_params
else:
records = records.values_list('json', flat=True)
if where_params:
records = records.extra(where=where, params=where_params)
# apply sorting
if not count and sort:
if ParsedInstance._has_json_fields(sort):
if not fields:
# we have to do a sql query for json field order
sql, params = records.query.sql_with_params()
params = list(params) + json_order_by_params(sort)
sql = u"%s %s" % (sql, json_order_by(sort))
elif not fields:
records = records.order_by(*sort)
records, sql, params = _start_index_limit(
records, sql, fields, params, sort, start_index, limit
)
return sql, params, records
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 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, )
