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