ordered searching to match SQL clauses, moved role&badge filtering to User.search

5 files changed, 58 insertions, 93 deletions

diff --git a/app/controllers/forumthreads_controller.rb b/app/controllers/forumthreads_controller.rb
index e21c6d4..8827f89 100644
--- a/app/controllers/forumthreads_controller.rb
+++ b/app/controllers/forumthreads_controller.rb
@@ -3,16 +3,15 @@ class ForumthreadsController < ApplicationController
   before_filter :check_permission, only: [:show, :edit, :update, :destroy]
 
   def index
-    if params[:label] && !Label.where("lower(name) = ?", params[:label].downcase).try(:first) && params[:label].downcase != "no label"
-      flash[:alert] = "'#{params[:label]}' is not a valid label."
-      redirect_to forumthreads_path(params.except(:label, :controller, :action))
-      return
-    end
-    @threads = Forumthread.filter(current_user, params[:title], params[:content], params[:reply], params[:label], User.where("lower(ign) = ?", params[:author].to_s.downcase).try(:first), params[:query], Forum.where(id: params[:id]).try(:first))
+    params[:id] = nil if params[:id] && !Forum.find_by(id: params[:id])
+
+    params.each {|k,v| params[k] = nil if v==""}
+
+    @threads = Forumthread.filter(current_user, params[:title], params[:content], params[:reply], params[:label], User.find_by(ign: params[:author].to_s.strip) || params[:author], params[:query], Forum.find_by(id: params[:id]))
     .page(params[:page]).per(30)
   end
   def show
-    if params[:reverse]
+    if params[:reverse] == "true"
       @replies = @thread.replies.reverse_order.page(params[:page])
     else
       @replies = @thread.replies.page(params[:page])
@@ -88,17 +87,6 @@ class ForumthreadsController < ApplicationController
   def search
   end
 
-  def search_redirect
-    params.each do |key, value|
-      params[key] = nil if params[key] == ""
-    end
-    params[:id] = nil if params[:id] == "Search All Threads"
-    params[:label] = nil if params[:label] && params[:label].downcase == "label"
-    params[:author] = params[:author].tr("@ ", "") if params[:author]
-    params_list = Hash[params.except(:commit, :utf8, :authenticity_token)]
-    redirect_to forumthreads_path(params_list)
-  end
-
   private
 
   def check_permission
diff --git a/app/controllers/users_controller.rb b/app/controllers/users_controller.rb
index 6b31d22..0a308c5 100644
--- a/app/controllers/users_controller.rb
+++ b/app/controllers/users_controller.rb
@@ -4,33 +4,13 @@ class UsersController < ApplicationController
   include MailerHelper
   include ERB::Util
 
-  before_filter :set_user, except: [:index, :new, :create, :lost_password, :reset_password, :suggestions, :search_redirect]
+  before_filter :set_user, except: [:index, :new, :create, :lost_password, :reset_password, :suggestions]
 
   def index
-    if params[:role]
-      if params[:role].downcase == "staff"
-        @users = User.joins(:role).where("roles.value >= ?", Role.get(:mod).to_i)
-      else
-        if role = Role.get(params[:role])
-          @users = User.joins(:role).where(role: role)
-        elsif params[:search] == nil
-          flash[:alert] = "role '#{params[:role]}' does not exist!"
-          redirect_to users_path
-          return
-        end
-      end
-    elsif params[:badge]
-      if badge = Badge.get(params[:badge])
-        @users = User.joins(:badge).where(badge: badge)
-      else
-        flash[:alert] = "badge '#{params[:badge]}' does not exist!"
-        redirect_to users_path
-        return
-      end
-    else
-      @users = User.joins(:role).where.not(id: User.first.id) #Remove first user
-    end
-    @users = User.search(@users, params[:search]) if params[:search]
+    params[:role] = nil if !Role.find_by(name: params[:role])
+    params[:badge] = nil if !Badge.find_by(name: params[:badge])
+
+    @users = User.search(params[:search], params[:role], params[:badge])
     @users = @users.order("roles.value desc", "confirmed desc", :name) unless params[:badge]
     @count = @users.size
     @users = @users.page(params[:page]).per(100)
@@ -340,14 +320,6 @@ class UsersController < ApplicationController
     end
   end
 
-  def search_redirect
-    params.each do |key, value|
-      params[key] = nil if params[key] == ""
-    end
-    params_list = Hash[params.except(:commit, :utf8, :authenticity_token)]
-    redirect_to users_path(params_list)
-  end
-
   private
 
   def validate_token(uuid, email, token)
diff --git a/app/models/forumthread.rb b/app/models/forumthread.rb
index 86823ac..9650e28 100644
--- a/app/models/forumthread.rb
+++ b/app/models/forumthread.rb
@@ -67,45 +67,42 @@ class Forumthread < ActiveRecord::Base
   end
 
   def self.filter (user, title, content, reply, label, author, query, forum)
-  userid = user.try(:id).to_i
-  role = user.try(:role).to_i
-
-  can_read = "COALESCE(forum_role_read.value, 0) <= ? AND COALESCE(forumgroup_role_read.value, 0) <= ?"
-  sticky_can_write = "sticky = true AND (COALESCE(forum_role_write.value, 0) <= ? OR COALESCE(forumgroup_role_write.value, 0) <= ?)"
-
-  threads = forum.try(:forumthreads) || Forumthread
-  threads = threads.where("forumthreads.user_author_id = ? OR (#{can_read}) OR (#{sticky_can_write})", userid, role, role, role, role)
-  .joins("LEFT JOIN threadreplies ON forumthreads.id = threadreplies.forumthread_id")
-  .joins(forum: :forumgroup)
-  .joins("LEFT JOIN roles as forum_role_read ON forums.role_read_id = forum_role_read.id")
-  .joins("LEFT JOIN roles as forum_role_write ON forums.role_write_id = forum_role_write.id")
-  .joins("LEFT JOIN roles as forumgroup_role_read ON forumgroups.role_read_id = forumgroup_role_read.id")
-  .joins("LEFT JOIN roles as forumgroup_role_write ON forumgroups.role_write_id = forumgroup_role_write.id")
-
-  if [content, title, reply, label, author, query].any?
-    label_o = Label.find_by(name: label)
-    if label_o
-      threads = threads.where(label: label_o)
-    elsif label.try(:downcase) == "no label"
-      threads = threads.where(label: nil)
-    end
-
-    threads = threads.where(user_author: author) if author
-
+    order_phrase = query || [title, content, reply].select(&:present?).join(" ")
+    user_id = user.try(:id).to_i
+    role_value = user.try(:role).to_i
+    can_read = "COALESCE(forum_role_read.value, 0) <= ? AND COALESCE(forumgroup_role_read.value, 0) <= ?"
+    # A user can view sticky threads in write-only forums without read permissions.
+    sticky_can_write = "sticky = true AND (COALESCE(forum_role_write.value, 0) <= ? AND COALESCE(forumgroup_role_write.value, 0) <= ?)"
+    match = "MATCH (title, forumthreads.content) AGAINST (?) OR MATCH (threadreplies.content) AGAINST (?)"
+
+    threads = forum.try(:forumthreads) || Forumthread
+
+    threads = threads.select("forumthreads.*", "(MATCH (title, forumthreads.content) AGAINST (#{Forumthread.sanitize(order_phrase)})) AS relevance", "(MATCH (threadreplies.content) AGAINST (#{Forumthread.sanitize(order_phrase)})) AS reply_rel")
+
+    threads = threads.joins(forum: :forumgroup)
+    .joins("LEFT JOIN threadreplies ON forumthreads.id = threadreplies.forumthread_id")
+    .joins("LEFT JOIN roles as forum_role_read ON forums.role_read_id = forum_role_read.id")
+    .joins("LEFT JOIN roles as forum_role_write ON forums.role_write_id = forum_role_write.id")
+    .joins("LEFT JOIN roles as forumgroup_role_read ON forumgroups.role_read_id = forumgroup_role_read.id")
+    .joins("LEFT JOIN roles as forumgroup_role_write ON forumgroups.role_write_id = forumgroup_role_write.id")
+
+    threads = threads.where("forumthreads.user_author_id = ? OR (#{can_read}) OR (#{sticky_can_write})", user_id, role_value, role_value, role_value, role_value)
     if query
-      threads = threads.where("MATCH (title, forumthreads.content) AGAINST (?) OR MATCH (threadreplies.content) AGAINST (?)", query, query)
+      threads = threads.where("#{match}", query[0..99], query[0..99])
     elsif [title, content, reply].any?
-      query = [title, content, reply].select(&:present?).join(" ")
-      threads = threads.where("MATCH (title) AGAINST (?)", title) if title
-      threads = threads.where("MATCH (forumthreads.content) AGAINST (?)", content) if content
-      threads = threads.where("MATCH (threadreplies.content) AGAINST (?)", reply) if reply
-      threads = threads.group("threadreplies.id", "forumthreads.id")
-      threads = threads.order("(MATCH (title, forumthreads.content) AGAINST ('#{query}')) DESC")
+      threads = threads.where("MATCH (title) AGAINST (?)", title[0..99]) if title
+      threads = threads.where("MATCH (forumthreads.content) AGAINST (?)", content[0..99]) if content
+      threads = threads.where("MATCH (threadreplies.content) AGAINST (?)", reply[0..99]) if reply
     end
-  end
+    if label.try(:downcase) == "no label"
+      threads = threads.where(label: nil)
+    elsif l = Label.find_by(name: label) && label
+      threads = threads.where(label: l)
+    end
+    threads = threads.where(user_author: author) if author
 
-  threads = threads.order("sticky desc", "threadreplies.created_at desc", "forumthreads.created_at desc") if threads.order_values.empty?
+    threads = threads.group("forumthreads.id")
 
-    threads
+    order_phrase.presence ? threads.order("GREATEST(relevance, reply_rel) DESC") : threads.order("sticky desc", "threadreplies.created_at DESC", "forumthreads.created_at DESC")
   end
 end
diff --git a/app/models/user.rb b/app/models/user.rb
index a96410a..3098cfc 100644
--- a/app/models/user.rb
+++ b/app/models/user.rb
@@ -175,7 +175,19 @@ class User < ActiveRecord::Base
     self.email_token ||= SecureRandom.hex(16)
   end
   
-  def self.search (users, search)
-    return users.where("users.name like ? OR ign like ?", "%#{User.send(:sanitize_sql_like, search)}%", "%#{User.send(:sanitize_sql_like, search)}%")
+  def self.search (search, role, badge)
+    if role
+      if role.downcase == "staff"
+        users = User.joins(:role).where("roles.value >= ?", Role.get(:mod).to_i)
+      elsif r = Role.get(role)
+        users = User.joins(:role).where(role: r)
+      else
+      end
+    elsif badge && b = Badge.get(badge)
+      users = User.joins(:badge).where(badge: b)
+    else
+      users = User.joins(:role).where.not(id: User.first.id) #Remove first user
+    end
+    return users.where("users.name like ? OR ign like ?", "%#{User.send(:sanitize_sql_like, search.to_s)}%", "%#{User.send(:sanitize_sql_like, search.to_s)}%")
   end
 end
diff --git a/app/views/forumthreads/show.html.erb b/app/views/forumthreads/show.html.erb
index 82f8fb3..b29d29e 100644
--- a/app/views/forumthreads/show.html.erb
+++ b/app/views/forumthreads/show.html.erb
@@ -1,11 +1,7 @@
 <%= link_to @thread.forum.group, forumgroup_path(@thread.forum.group) %> → <%= link_to @thread.forum, @thread.forum %> → <%=truncate(@thread.title, length: 60, omission: " …") %>
 <h1>
   <%= render partial: "labels/label", locals: {label: @thread.label} %><%= title @thread.title %>
-  <% if params[:reverse] %>
-    <%= link_to "Reverse Replies", @thread, class: "btn right blue" %>
-  <% else %>
-    <%= link_to "Reverse Replies", forumthread_path(@thread, reverse: true), class: "btn right blue" %>
-  <% end %>
+  <%= link_to "Reverse Replies", forumthread_path(@thread, reverse: params[:reverse] != "true"), class: "btn right blue" %>
 </h1>
 <div class="item-group thread with-avatar" id="thread-<%= @thread.id %>">
   <div class="header">