public async Task <ArticleDataResultPagedItemsList> GetLatestArticleItemsPagedAsync(int pageNumber, int pageSize, IEnumerable <string> roles)
{
var articleDataResultPagedItemsList = new ArticleDataResultPagedItemsList(pageNumber, pageSize);
using (NpgsqlConnection connection = new NpgsqlConnection(base.connectionString))
{
string sql = @"SELECT
COUNT(1) OVER() AS ""ResultsCount"",
a.""Id"",
a.""Title"",
a.""CoverFileId""
FROM ""Articles"" a
LEFT JOIN ""ArticleRoles"" ar ON ar.""ArticleId"" = a.""Id""
LEFT JOIN ""AspNetRoles"" r ON r.""Id"" = ar.""RoleId""
WHERE r.""Name"" IS NULL OR r.""Name"" = ANY(:Roles)
ORDER BY ""Timestamp"" DESC
LIMIT :PageSize
OFFSET(:PageNumber - 1) * :PageSize;
";
using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
{
command.Parameters.AddWithValue("PageNumber", pageNumber);
command.Parameters.AddWithValue("PageSize", pageSize);
var rolesParam = new NpgsqlParameter("Roles", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Text);
rolesParam.Value = roles;
command.Parameters.Add(rolesParam);
await connection.OpenAsync();
using (NpgsqlDataReader dataReader = await command.ExecuteReaderAsync())
{
while (await dataReader.ReadAsync())
{
if (articleDataResultPagedItemsList.TotalResultsCount == 0)
{
articleDataResultPagedItemsList.TotalResultsCount = Convert.ToInt32(dataReader["ResultsCount"]);
}
var item = new ArticleDataResult(dataReader);
articleDataResultPagedItemsList.AddItem(item);
}
}
}
}
return(articleDataResultPagedItemsList);
}
public async Task <IList <ArticleDataResult> > FindArticlesAsync(string searchPattern, IEnumerable <string> roles)
{
var result = new List <ArticleDataResult>();
using (NpgsqlConnection connection = new NpgsqlConnection(base.connectionString))
{
string sql = @"WITH RECURSIVE ""RecursiveCategories"" AS (
SELECT
""Id"",
""ParentId"",
""Name""
FROM ""Categories""
UNION
SELECT
c.""Id"",
c.""ParentId"",
c.""Name""
FROM ""Categories"" c
JOIN ""RecursiveCategories"" rc ON rc.""Id"" = c.""ParentId""
)
SELECT
DISTINCT
a.""Id"",
a.""Title"",
a.""Timestamp""
FROM ""RecursiveCategories"" rc
JOIN ""ArticleCategories"" ac ON ac.""CategoryId"" = rc.""Id""
JOIN ""Articles"" a ON a.""Id"" = ac.""ArticleId""
LEFT JOIN ""ArticleRoles"" ar ON ar.""ArticleId"" = a.""Id""
LEFT JOIN ""AspNetRoles"" r ON r.""Id"" = ar.""RoleId""
WHERE (
(a.""Title"" ILIKE('%' || :SearchPattern || '%'))
OR (a.""Body"" ILIKE('%' || :SearchPattern || '%'))
OR (rc.""Name"" ILIKE('%' || :SearchPattern || '%'))
)
AND (r.""Name"" IS NULL OR r.""Name"" = ANY(:Roles))
ORDER BY a.""Timestamp"", a.""Title""
";
using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
{
command.Parameters.AddWithValue("SearchPattern", searchPattern);
var rolesParam = new NpgsqlParameter("Roles", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Text);
rolesParam.Value = roles;
command.Parameters.Add(rolesParam);
await connection.OpenAsync();
using (NpgsqlDataReader dataReader = await command.ExecuteReaderAsync())
{
while (await dataReader.ReadAsync())
{
var item = new ArticleDataResult
{
Id = (Guid)dataReader["Id"],
Title = (string)dataReader["Title"]
};
result.Add(item);
}
}
}
}
return(result);
}
