1

I have a basic has_many relationship, modeled as such:

class Term < ActiveRecord::Base
    has_many :acroynms
end

And

class Acronym < ActiveRecord::Base
    belongs_to :term    
end

I would like to write an arel relation that will pull terms based on the term's name, or an acronym's name. My relation is incorrect, but I'm looking to do something that would look like this:

terms = Term.arel_table
relation = terms[name].matches("%#{params['a']}%")
relation = relation.or(terms[:acronyms.name].matches("%#{params['a']}%"))
@results = Term.where(relation)

I would like to keep it in the relation, because I have many other composed conditions combined into that one relation (that I'm omitting here for brevity).

I'm using the relation, rather than just composing everything together on the final line is that the items that need to composed won't be known until runtime, depending on user input. My thought is using this model I'll be able to set up my relation, however complex it may be, and then just have that one line to set @results, regardless of the user input.

Is there a way to write this all in a relation? Otherwise, I suppose I'll need to resort to using arel's joins, but only join in situations where the user input calls for it, correct?

kevin_j_m
  • 13
  • 3

2 Answers2

1

I'm not entirely sure how to do this using arel, but you could do something like this if you wanted to chain where clauses based on parameters (or other input) the execute once it's built:

query = User.scoped

query = query.where("name like ?", "%#{params[:name]}%") if params[:name].present?
query = query.where("email like ?", "%#{params[:email]}%") if params[:email].present?

@users = query.all

Again, I know this is not exactly what you're going for, but could be a possible alternative.

Note: This won't do what you expect if you run it in console because the print step of the REPL will execute each step, making it look like multiple queries when in a Rails app it will actually perform just one.

CDub
  • 13,146
  • 4
  • 51
  • 68
  • Thanks for the alternative. One of the main reasons I went with arel is because one of my parameters is an array, which should be treated as OR conditions. With arel, I can loop through the parameter array and chain up OR statements. In your paradigm, would there be a method to do that? I'm new to activerecord and arel, so I apologize if this is a basic query that I've missed somewhere in the documentation. – kevin_j_m Nov 11 '13 at 01:23
  • Check this out: http://stackoverflow.com/questions/3291152/ruby-on-rails-3-howto-make-or-condition – CDub Nov 11 '13 at 01:27
  • So, given the above link, you'd build the arel `where` clause based on your array, then you could plug it into `where`, then execute. – CDub Nov 11 '13 at 01:27
  • That definitely helps clean up the readability of my code quite a bit. I still think I have my initial problem though, and I'll edit the question to better reflect this, because I don't think I made it clear. Basically, I want to search for Terms with a name like the parameter OR Terms with a synonym that have a name like the parameter. – kevin_j_m Nov 11 '13 at 04:17
0

To get around using an OR operator, I resolved this by using 3 queries, which is sub-optimal, but functional.

My first query looks for acronyms where the name is like the search parameter. 

acronymQuery = query.includes(:acronyms).where('acronyms.name like ?', wildcard_search).select("terms.id")

The second looks for terms where the name is like the search parameter. 

termQuery = query.where('terms.name like ?', wildcard_search).select("terms.id")

I then combine the unique IDs from each query, and my third query searches for those ids.

acronymQueryResults = acronymQuery.all.map {|row| row.id}
termQueryResults = termQuery.all.map {|row| row.id}
ids = (acronymQueryResults + termQueryResults).uniq
query = Term.where('id' => ids)
kevin_j_m
  • 13
  • 3