public async Task <IEnumerable <SubverseSet> > GetUserSets(string userName) { var q = new DapperQuery(); q.Select = $"SELECT * FROM {SqlFormatter.Table("SubverseSet", "subSet")}"; q.Where = "subSet.\"UserName\" = @UserName"; q.Parameters.Add("UserName", userName); if (!userName.IsEqual(UserName)) { q.Append(x => x.Where, $"subSet.\"IsPublic\" = {SqlFormatter.BooleanLiteral(true)}"); } using (var db = new VoatDataContext()) { var set = await db.Connection.QueryAsync <SubverseSet>(q.ToString(), q.Parameters); return(set); } }
private DapperQuery BaseDomainObjectSearch(DomainType domainType, SearchOptions options) { var q = new DapperQuery(); var hasPhrase = !String.IsNullOrEmpty(options.Phrase); switch (domainType) { case DomainType.Subverse: q.SelectColumns = "@DomainType as \"Type\", s.\"Name\", s.\"Title\", s.\"Description\", s.\"CreatedBy\" AS \"OwnerName\", s.\"SubscriberCount\", s.\"CreationDate\""; q.Select = $"DISTINCT {"{0}"} FROM {SqlFormatter.Table("Subverse", "s", null, "NOLOCK")}"; if (hasPhrase) { q.Where = "(s.\"Name\" LIKE CONCAT('%', @SearchPhrase, '%') OR s.\"Title\" LIKE CONCAT('%', @SearchPhrase, '%') OR s.\"Description\" LIKE CONCAT('%', @SearchPhrase, '%'))"; } q.Append(x => x.Where, $"s.\"IsAdminDisabled\" = {SqlFormatter.BooleanLiteral(false)} AND s.\"IsPrivate\" = {SqlFormatter.BooleanLiteral(false)}"); break; case DomainType.Set: q.SelectColumns = "@DomainType as \"Type\", s.\"Name\", s.\"Title\", s.\"Description\", s.\"UserName\" AS \"OwnerName\", s.\"SubscriberCount\", s.\"CreationDate\""; q.Select = $"DISTINCT {"{0}"} FROM {SqlFormatter.Table("SubverseSet", "s", null, "NOLOCK")}"; if (hasPhrase) { q.Where = "(s.\"Name\" LIKE CONCAT('%', @SearchPhrase, '%') OR s.\"Title\" LIKE CONCAT('%', @SearchPhrase, '%') OR s.\"Description\" LIKE CONCAT('%', @SearchPhrase, '%'))"; } q.Append(x => x.Where, "(s.\"IsPublic\" = @IsPublic OR @IsPublic IS NULL)"); break; } q.Parameters.Add("DomainType", (int)domainType); q.Parameters.Add("IsPublic", (bool?)true); if (hasPhrase) { q.Parameters.Add("SearchPhrase", options.Phrase); } switch (options.Sort) { case SortAlgorithm.Active: switch (domainType) { case DomainType.Subverse: q.Select += $" INNER JOIN {SqlFormatter.Table("Submission", "sub", null, "NOLOCK")} ON sub.\"Subverse\" = s.\"Name\""; q.SelectColumns += ", MAX(sub.\"CreationDate\") AS \"ThisIsOnlyUsedForSortingByActive\""; q.GroupBy = "s.\"Name\", s.\"Title\", s.\"Description\", s.\"CreatedBy\", s.\"SubscriberCount\", s.\"CreationDate\""; q.OrderBy = "MAX(sub.\"CreationDate\") DESC"; break; case DomainType.Set: q.Select += $" INNER JOIN {SqlFormatter.Table("SubverseSetList", "subList", null, "NOLOCK")} ON subList.\"SubverseSetID\" = s.\"ID\""; q.SelectColumns += ", MAX(subList.\"CreationDate\") AS \"ThisIsOnlyUsedForSortingByActive\""; q.GroupBy = "s.\"Name\", s.\"Title\", s.\"Description\", s.\"UserName\", s.\"SubscriberCount\", s.\"CreationDate\""; q.OrderBy = "MAX(subList.\"CreationDate\") DESC"; break; } break; case SortAlgorithm.New: q.OrderBy = "s.\"CreationDate\" DESC"; break; case SortAlgorithm.Top: default: q.OrderBy = "s.\"SubscriberCount\" DESC"; break; } q.SkipCount = options.Index; q.TakeCount = options.Count; return(q); }