Squeel and rails ... dynamic where clause
Using Squeel, in a rails app, I have a conditions hash:
{'trans' => 'manual'}
which I end up planning on going into an array ... so I can also have an operator assignment.
[[field,operator,value][field,operator,value]]
I want to use the Model method, which at the moment I am omitting the operator, and I am just trying to do this to get this to work ... however what I have below does not work.
def self.with_conditions(conditions)
joins{car}.where do
conditions.map {|key,value| (key==value) }.inject(:&)
end
end
I also tried this:
def self.with_conditions(conditions)
joins{car}.where do
query = nil
conditions.each do |key, value|
q = (key == value)
if query
query &= q
else
query = q
end
end
query
end
end
So how can I get this to work with the == parameter, and then how do I end up getting this to work with the dynamic operator? thank
In the console, my SQL is not readable in any of my conditions ... for example:
in the console:
> Timeslip.with_conditions({'car.year'=>'1991'})
SELECT "timeslips".* FROM "timeslips" INNER JOIN "cars" ON "cars"."id" = "timeslips"."car_id"
source to share
You need to programmatically build your Squeel query. For example:
def self.with_conditions(conditions)
conditions.map do |col, str|
Squeel::Nodes::Predicate.new(Squeel::Nodes::Stub.new(col), :matches, str) # (email.matches "user@example.com")
end.inject do |t, expr|
t & expr # joins each expression from the .map above with & - to be converted to AND in the sql
end.tap do |block|
return where{(block)} # pass the constructed expression to Squeel
end
end
In my model, User::User
I can run
User::User.with_conditions({email: "user@example.com", first_name: "Deefour"}).to_sql
and i will get
SELECT "user_users".* FROM "user_users" WHERE (("user_users"."email" LIKE 'user@example.com' AND "user_users"."first_name" LIKE 'Deefour'))
source to share
I don't know if it helps, but I did it like this using this helper method:
def query_for_matches(key, value)
stub = Squeel::Nodes::Stub.new(key)
Squeel::Nodes::Predicate.new(stub, :matches, "%#{value}%")
end
You have a hash of parameters from a request for something:
dynamic_params = {'username' => 'some_name', 'email' => 'email@example.com'}
Then I use this with chaining where
in a loop:
query = SomeModel #could be User, etc
dynamic_params.each_pair {|key,value| query = query.where(query_for_matches(key, value)) }
Then you can transfer query
to your view or whatever. I've only been on the rails for a couple of weeks, so not sure if this is the best practice, but it works.
source to share