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