Dynamically create query - Rails 5

Question

If I manually write a query, it will be like

User.where("name LIKE(?) OR desc LIKE(?)",'abc','abc')
    .where("name LIKE(?) OR desc LIKE(?)",'123','123')

However, I need to dynamically generate that query. I am getting data like

def generate_query(the_query)

   query,keywords = the_query

   # Here 
   # query = "name LIKE(?) OR desc LIKE(?)"
   # keywords = [['abc','abc'],['123','123']]

   keywords.each do |keyword|
       users = User.where(query,*keyword) <-- not sure how to dynamically add more 'where' conditions.    
    end

end

I am using Rails 5. Hope it is clear. Any help appreciated :)

mu is too short · Accepted Answer · 2019-03-24 07:34:00Z

2

Something like this:

q = User.where(a)
        .where(b)
        .where(c)

is equivalent to:

q = User
q = q.where(a)
q = q.where(b)
q = q.where(c)

So you could write:

users = User
keywords.each do |keyword|
  users = users.where(query, *keyword)
end

But any time you see that sort of feedback pattern (i.e. apply an operation to the operation's result or f(f( ... f(x)))) you should start thinking about Enumerable#inject (AKA Enumerable#reduce):

users = keywords.inject(User) { |users, k| users.where(query, *k) }

That said, your query has two placeholders but keywords is just a flat array so you won't have enough values in:

users.where(query, *k)

to replace the placeholders. I think you'd be better off using a named placeholder here:

query    = 'name like :k or desc like :k'
keywords = %w[abc 123]
users    = keywords.inject(User) { |users, k| users.where(query, k: k) }

You'd probably also want to include some pattern matching for your LIKE so:

query = "name like '%' || :k || '%' or desc like '%' || :k || '%'"
users = keywords.inject(User) { |users, k| users.where(query, k: k)

where || is the standard SQL string concatenation operator (which AFAIK not all databases understand) and % in a LIKE pattern matches any sequence of characters. Or you could add the pattern matching in Ruby and avoid having to worry about the different ways that databases handle string concatenation:

query = 'name like :k or desc like :k'
users = keywords.inject(User) { |users, k| users.where(query, k: "%#{k}%")

Furthermore, this:

User.where("name LIKE(?) OR desc LIKE(?)",'abc','abc')
    .where("name LIKE(?) OR desc LIKE(?)",'123','123')

produces a WHERE clause like:

where (name like 'abc' or desc like 'abc')
  and (name like '123' or desc like '123')

so you're matching all the keywords, not any of them. This may or may not be your intent.

edited Mar 24, 2019 at 7:34

answered Mar 17, 2018 at 19:14

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

learner2017 Over a year ago

Thanks for reply. My array example was wrong. I fixed that.

mu is too short Over a year ago

Okay, if keywords is an array-of-arrays then where(query, *k) will be fine. I'd still lean towards keyword placeholders to avoid the repetition. You'd also want to use *k.map { |k| "%#{k}%" } to add the pattern matching characters.

Collectives™ on Stack Overflow

Dynamically create query - Rails 5

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related