public IEnumerable<dynamic> GetUserQuestions(int userid)
{
dynamic table = new Post();
object[] queryargs = { userid };
var questions = table.All(where: "owneruserid = @0 and posttypeid = 1", args: queryargs);
return questions;
}
public IEnumerable<dynamic> GetUserPosts(int userid)
{
dynamic table = new Post();
object[] queryargs = { userid };
var posts = table.All(where: "owneruserid = @0", args: queryargs);
return posts;
}
public IEnumerable<dynamic> GetUserAnswers(int userid)
{
dynamic table = new Post();
object[] queryargs = { userid };
var answers = table.All(where: "owneruserid = @0 and posttypeid = 2", args: queryargs);
return answers;
}
public IEnumerable<dynamic> GetRecentPosts()
{
dynamic table = new Post();
var recentposts = table.Query(@"select p.postid, p.answercount,p.viewcount,p.title,p.tags,u.userid,u.displayname,u.reputation
from posts p inner join users u on p.owneruserid = u.userid
where p.posttypeid = 1
order by p.creationdate desc
limit 20");
return recentposts;
}
//public IQueryable<Post> GetPostsByTag(string tagname)
public dynamic GetTagCount(string tagname)
{
dynamic table = new Post();
tagname = "'" + tagname + "'";
object[] queryargs = { tagname };
//var tags = table.Scalar(@"select count(0) as tagcount from posts WHERE tags like '%@0%", queryargs);
//var tags = table.Scalar(@"select count(0) as tagcount from posts WHERE to_tsvector('english',tags) @@ plainto_tsquery('english', @0)", queryargs);
var tags = table.Scalar(@"select count(0) as tagcount from posts WHERE tagsvector @@ plainto_tsquery('english', @0)", queryargs);
//counter = sqlConnection.Query<int>("SELECT Count(0) as PostCount FROM dbo.Posts WHERE FREETEXT(tags, @tagname)", new { tagname = tagname }).Single();
return tags;
}
public IEnumerable<dynamic> GetHotPosts()
{
//The dataset isn't recent, so we get to fake some dates!
DateTime WhenItAllBegins = new DateTime(2011, 3, 25, 0, 0, 0);
dynamic table = new Post();
var hotposts = table.Query(@"With hotposts as (
select p.postid, p.answercount,p.viewcount,p.title,p.tags, p.owneruserid
from posts p
where p.posttypeid = 1 and p.creationdate >= '03-31-2011'
order by p.viewcount desc
limit 20)
Select post.postid, post.answercount,post.viewcount,post.title,post.tags,u.userid,u.displayname,u.reputation
FROM hotposts as post
inner join users u on post.owneruserid = u.userid");
return hotposts ;
}