From e2a16f3ae6e4c557a986d6170c1a7420e0ca08e0 Mon Sep 17 00:00:00 2001 From: MrYummy Date: Fri, 2 Jun 2017 19:14:28 +0200 Subject: ordered searching to match SQL clauses, moved role&badge filtering to User.search --- app/controllers/forumthreads_controller.rb | 24 +++-------- app/controllers/users_controller.rb | 38 +++-------------- app/models/forumthread.rb | 67 ++++++++++++++---------------- app/models/user.rb | 16 ++++++- app/views/forumthreads/show.html.erb | 6 +-- 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: " …") %>

<%= 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" %>

-- cgit v1.2.3