public static string RecreateQueryString(Query query) { StringBuilder sb = new StringBuilder("?"); if (query.Filters != null) { string[] filters = query.Filters.Select(f => String.Concat("filter=", f.Column.UrlEncode(), ":", f.Value.ToString().UrlEncode())).ToArray(); sb.Append(String.Join("&", filters)); sb.Append("&"); } if (query.Sorts != null) { string[] sorts = query.Sorts.Select(s => String.Concat("sort=", s.Column.UrlEncode(), s.Direction == QuerySortDirection.Descending ? ":desc" : null)).ToArray(); sb.Append(String.Join("&", sorts)); sb.Append("&"); } if (query.Searches != null) { string[] searches = query.Searches.Select(s => String.Concat("search=", s.UrlEncode())).ToArray(); sb.Append(String.Join("&", searches)); sb.Append("&"); } sb.Append("count="); sb.Append(query.Count); return sb.ToString(); }
public static QueriedIssuesViewModel QueryIssues(Guid userGuid, Query query) { SqlBuilder sql = new SqlBuilder(); Dictionary<string, object> parameters = new Dictionary<string, object>(); if (query.Filters != null) { foreach (var filter in query.Filters) { ProcessFilter(userGuid, filter, sql, parameters); } } else { ProcessFilter(userGuid, new QueryFilterColumn("status:Open"), sql, parameters); } if (query.Sorts != null) { foreach (var sort in query.Sorts) { ProcessSort(sort, sql, parameters); } } else { ProcessSort(new QuerySortColumn("id:desc"), sql, parameters); } if (query.Searches != null) { ProcessSearch(String.Join(" ", query.Searches), sql, parameters); } // Prepare the SQL statements. var countTemplate = sql.AddTemplate(IssueCountSqlQueryTemplate); var issuesTemplate = sql.AddTemplate(IssueSqlQueryTemplate); string rawSql = issuesTemplate.RawSql + " LIMIT " + query.Count; if (query.Page > 1) { rawSql += " OFFSET " + (query.Page - 1) * query.Count; } using (var db = DataService.Connect()) { int total = db.SqlScalar<int>(countTemplate.RawSql, parameters); return new QueriedIssuesViewModel() { Issues = db.Query<IssueViewModel>(rawSql, parameters), Total = total, }; } }