diff options
author | 2013-01-09 16:59:09 +0700 | |
---|---|---|
committer | 2013-01-09 16:59:09 +0700 | |
commit | 6a6b574cd010f01b4d26907707a28e70d3e384c6 (patch) | |
tree | b999c1318f1afa29689c262033762c2688e5e908 /src/java/com/juick/http/www/Blogs.java | |
parent | 93b44885f6d306d0cb405c4ee9c436ee3ea4c921 (diff) |
Blogs: related tags
Diffstat (limited to 'src/java/com/juick/http/www/Blogs.java')
-rw-r--r-- | src/java/com/juick/http/www/Blogs.java | 51 |
1 files changed, 51 insertions, 0 deletions
diff --git a/src/java/com/juick/http/www/Blogs.java b/src/java/com/juick/http/www/Blogs.java index 4af75cd3..527009a1 100644 --- a/src/java/com/juick/http/www/Blogs.java +++ b/src/java/com/juick/http/www/Blogs.java @@ -42,6 +42,24 @@ import javax.servlet.http.HttpServletResponse; */ public class Blogs { + private int relatedTagsStartMID = 0; + + public Blogs(Connection sql) { + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT MIN(message_id) FROM messages WHERE ts>DATE_ADD(NOW(),INTERVAL -6 MONTH)"); + rs = stmt.executeQuery(); + if (rs.first()) { + relatedTagsStartMID = rs.getInt(1); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + } + protected void doGet(Connection sql, Connection sqlSearch, HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String paramTagStrQuery = request.getParameter("tag"); if (paramTagStrQuery != null) { @@ -218,6 +236,13 @@ public class Blogs { out.println("<div id=\"rcol\" class=\"abs\">"); PageTemplates.pageYandexAd(out, visitor == null ? 2 : 3); // разный ID для залогиненых и нет + if (paramTag > 0 && relatedTagsStartMID > 0) { + String related = getRelatedTags(sql, paramTag, 10); + if (!related.isEmpty()) { + out.println("<h3>Related tags:</h3>"); + out.println("<ul id=\"reltags\">" + related + "</ul>"); + } + } out.println("</div>"); out.println("</div>"); // topwrapper @@ -277,4 +302,30 @@ public class Blogs { return ret; } + + private String getRelatedTags(Connection sql, int tag_id, int cnt) { + String ret = ""; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT tags.name,COUNT(message_id) AS cnt FROM messages_tags INNER JOIN tags ON messages_tags.tag_id=tags.tag_id WHERE messages_tags.message_id>" + relatedTagsStartMID + " AND messages_tags.message_id IN (SELECT message_id FROM messages_tags WHERE tag_id=?) AND messages_tags.tag_id NOT IN (SELECT tag_id FROM tags_ignore) GROUP BY messages_tags.tag_id HAVING cnt>1 ORDER BY cnt DESC LIMIT 1,?"); + stmt.setInt(1, tag_id); + stmt.setInt(2, cnt); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + try { + ret += "<li><a href=\"/tag/" + URLEncoder.encode(rs.getString(1), "UTF-8") + "\">" + Utils.encodeHTML(rs.getString(1)) + "</a></li>"; + } catch (UnsupportedEncodingException e) { + } + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return ret; + } } |