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);
}
