CODEWITHSUNDEEP
sqlruby-on-railsruby
James Carny
James Carny

Reputation: 51

Building custom filter method in Rails/SQL

I am trying to create a filter for an Article class that uses a SQL query built on the fly based on the parameters submitted from an HTML form. Since I have multiple many-to-many relationships, I can't use Article.where ( I don't think anyways ). While the following code works, I am unsure as to whether this is the most effective way to execute this query and just how secure it is. I do make a point to guard against SQL injection by using the ? keyword in the sql string ( a la Rails convention), but wanted to make sure that would be enough. Any advice on how I can make this more elegant?

def self.filter(hash)
    hash.delete_if {|k,v| v == ""}
    hash[:writer_type] = (hash[:writer_type]) if hash[:writer_type] != nil
    sql_base = "select distinct articles.* from articles
       join tags
       on tags.article_id = articles.id
       join categories
       on tags.category_id = categories.id
       left outer join itineraries
       on itineraries.article_id = articles.id
       left outer join cities
       on itineraries.city_id = cities.id
       join users
       on users.id = articles.user_id"

    condition_array = []
    key_array = []
    hash.each_key {|key| key_array << key}
    key_array.each_with_index do |key, i|
      operator = "and"
      operator = "where" if i == 0
      case key
      when :writer
        sql_base << "\n#{operator} users.username like ?"
        condition_array << hash[:writer]
      when :writer_type
         sql_base << "\n#{operator} users.status in (?)"
        condition_array << hash[:writer_type]
      when :city
        sql_base << "\n#{operator} cities.name like ?" 
        condition_array << hash[:city]
      when :category
        sql_base << "\n#{operator} categories.name like ?"
        condition_array << hash[:category]
      end
    end
    sql_array = [sql_base,condition_array].flatten
    articles = Article.find_by_sql(sql_array)
    articles
  end

Upvotes: 0

Views: 519

Answers (1)

Alex.Bullard
Alex.Bullard

Reputation: 5563

Sure, you should be able to do something like this:

q = Articles.join(:tags #, etc)

if condition
  q = q.joins(:user).where("users.status in ?", hash[:writer_type])
else
  q = q.joins(:cities).where("cities.name LIKE ?", hash[:city])
end

q

This works because ActiveRecord::Relation only executes the query on first access. So until you call to_a or enumerate through the records you can continue to chain onto the object.

If you are doing complicated queries you might want to look at squeel It would let you rewrite your conditions like

q.where { users.status >> my { hash[:writer_type] } }

or

q.where { cities.name =~ my { hash[:city] } }

Upvotes: 1

Related Questions