Reputation: 51
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
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