public IActionResult GetFollowers([FromRoute] int id) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } List <FollowerModel> users = new List <FollowerModel>(); try { _comm.CommandText = @"SELECT NewUsers.*, DATEDIFF(MINUTE, FollowDate, GETDATE()) AS FollowMins, CASE WHEN UserID IN (SELECT FollowedID FROM [Follow] WHERE FollowingID = @userID) THEN 1 ELSE 0 END AS FollowingUser FROM (SELECT TOP 100 PERCENT UserID, Username, COUNT(FollowedID) AS FollowerNum FROM [User] LEFT JOIN [Follow] ON (FollowedID = UserID) GROUP BY Username, UserID ORDER BY FollowerNum DESC) AS NewUsers LEFT JOIN [Follow] ON (FollowingID = NewUsers.UserID) WHERE FollowedID = @userID ORDER BY FollowDate DESC;" ; _comm.Parameters.AddWithValue("@userID", id); _comm.CommandType = CommandType.Text; _conn.Open(); SqlDataReader reader = _comm.ExecuteReader(); while (reader.Read()) { FollowerModel user = new FollowerModel() { UserID = Convert.ToInt32(reader[0]), Username = reader[1].ToString(), FollowerNum = Convert.ToInt32(reader[2]), FollowMins = Convert.ToInt32(reader[3]), FollowingUser = Convert.ToBoolean(reader[4]) }; users.Add(user); } return(Ok(users)); } catch (Exception) { return(BadRequest(ModelState)); } finally { if (_conn != null) { _conn.Close(); } } }
public IActionResult Search([FromRoute] int id, [FromRoute] string search) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } List <FollowerModel> users = new List <FollowerModel>(); try { _comm.CommandText = String.Format( @"SELECT UserID, Username, COUNT(FollowedID) AS FollowerNum, MAX(CASE WHEN FollowingID = @userID THEN 1 ELSE 0 END) AS FollowingUser FROM [User] LEFT JOIN [Follow] ON (FollowedID = UserID) WHERE Username LIKE '%{0}%' GROUP BY UserID, Username ORDER BY FollowerNum DESC, LEN(Username);" , search); _comm.Parameters.AddWithValue("@userID", id); _comm.CommandType = CommandType.Text; _conn.Open(); SqlDataReader reader = _comm.ExecuteReader(); while (reader.Read()) { FollowerModel user = new FollowerModel() { UserID = Convert.ToInt32(reader[0]), Username = reader[1].ToString(), FollowerNum = Convert.ToInt32(reader[2]), FollowingUser = Convert.ToBoolean(reader[3]) }; if (user.UserID != id) { users.Add(user); } } return(Ok(users)); } catch (Exception) { return(BadRequest(ModelState)); } finally { if (_conn != null) { _conn.Close(); } } }