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, )