aboutsummaryrefslogtreecommitdiff
path: root/src/java/com/juick/http/www/Blogs.java
diff options
context:
space:
mode:
authorGravatar Ugnich Anton2013-01-09 16:59:09 +0700
committerGravatar Ugnich Anton2013-01-09 16:59:09 +0700
commit6a6b574cd010f01b4d26907707a28e70d3e384c6 (patch)
treeb999c1318f1afa29689c262033762c2688e5e908 /src/java/com/juick/http/www/Blogs.java
parent93b44885f6d306d0cb405c4ee9c436ee3ea4c921 (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.java51
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;
+ }
}