public async Task <List <Shout> > GetByUserIdAsync(int userId, int lastId = 0, short limit = 10) { await base._dBContext.OpenDBConnectionAsync(); using (base._dBContext.DbConnection) { return((List <Shout>) await base._dBContext.DbConnection.QueryAsync <Shout>( $@"{DapperHelperQueries.GetShoutQueryWithoutWhere()} WHERE Shout.UserId = { userId } AND {DapperHelperQueries.PaginatedQueryDESC( "Shout", lastId, limit )} " )); } }
public async Task <Shout> GetByIdAsync(int shoutId) { await base._dBContext.OpenDBConnectionAsync(); using (base._dBContext.DbConnection) { return(await base._dBContext.DbConnection.QueryFirstOrDefaultAsync <Shout>( $@"{DapperHelperQueries.GetShoutQueryWithoutWhere()} WHERE Id = { shoutId } " )); } }
public async Task <List <Shout> > GetFollowingShoutsByUserIdAsync(int userId, int lastId = 0, short limit = 10) { throw new NotImplementedException(); await base._dBContext.OpenDBConnectionAsync(); using (base._dBContext.DbConnection) { return((List <Shout>) await base._dBContext.DbConnection.QueryAsync <Shout>( $@"{DapperHelperQueries.GetShoutQueryWithoutWhere()} WHERE {DapperHelperQueries.PaginatedQueryDESC( "Shout", lastId, limit )} " )); } }
public async Task <List <Shout> > GetAsync(ShoutsFilter shoutsFilter, int lastId = 0, short limit = 10) { string query = DapperHelperQueries.GetShoutQueryWithoutWhere(); string hashtagClause = ""; if (shoutsFilter?.Hashtags.Length > 0) { throw new NotImplementedException(); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append(@"IN( SELECT ShoutId FROM HashtagShout INNER JOIN Hashtag ON HashtagShout.HashtagId = Hashtag.Id WHERE Hashtag.Content IN( "); for (int i = 0; i < shoutsFilter.Hashtags.Length; ++i) { // TODO: Turn this into a parameterized query. stringBuilder.Append(shoutsFilter.Hashtags); if (i < shoutsFilter.Hashtags.Length - 1) { stringBuilder.Append(", "); } } stringBuilder.Append(") ) "); hashtagClause = stringBuilder.ToString(); } query += $"WHERE {DapperHelperQueries.PaginatedQuery( "Shout", lastId )} "; if (shoutsFilter.FilteredBy == FilterType.MostComments) { query += $@"AND Id IN( SELECT ShoutId FROM Comment {( shoutsFilter.Hashtags.Length > 0 ? $"WHERE ShoutId {hashtagClause}" : "" )} GROUP BY ShoutId ORDER BY COUNT(ShoutId) DESC LIMIT {limit} ) "; } else { query += $"{( shoutsFilter.Hashtags.Length > 0 ? $"AND Id {hashtagClause}" : "" )}"; query += " ORDER BY " + shoutsFilter.FilteredBy.Switch(new Dictionary <FilterType, Func <string> >() { { FilterType.Top, () => "Shout.LikesCount, Shout.CreateDate DESC" }, { FilterType.Last, () => "Shout.CreateDate DESC" } }, () => "ORDER BY Shout.LikesCount DESC" ); query += $" LIMIT {limit}"; } await base._dBContext.OpenDBConnectionAsync(); using (base._dBContext.DbConnection) { return((List <Shout>) await base._dBContext.DbConnection.QueryAsync <Shout>(query)); } }