Squeel and rails... dynamic where clause

Question

Using Squeel, in a rails app, I have a hash of conditions:

{'trans' => 'manual'}

which i eventually plan on moving into an array... so i can also have an operator assignment.

[[field,operator,value][field,operator,value]]

I want to use a Model method, which for now i omit the operator and am I just trying == 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 do i get this to work with the ==, and then how would i eventually get this to work with a dynamic operator as well? thanks

In console, my SQL doesn't read in any of my conditions... for example:

in console:

> Timeslip.with_conditions({'car.year'=>'1991'})

SELECT "timeslips".* FROM "timeslips" INNER JOIN "cars" ON "cars"."id" = "timeslips"."car_id"

score 3 · Answer 1 · answered Feb 01 '13 at 20:21

3

You need to programmatically build the 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

On my User::User model 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'))

answered Feb 01 '13 at 20:21

deefour

34,974
7
97
90

So this doesn't really seem less complicated than just using Arel? Do you agree? Any tips on using a dynamic operator, to replace :matches as well? – Joel Grannas Feb 02 '13 at 14:14
`:matches` is just a symbol corresponding to a Squeel method. You can pass that in with the `conditions` - maybe passing in an Array of Hashes like `[{ col: :first_name, query: "Deefour", method: :matches }]` – deefour Feb 02 '13 at 19:59
Ok, so i have this almost working, but i need to be able to use an association name which would be dynamic as well. How would i go about passing in the association name to the above code? I think this is gonna work for me! thanks for your help! – Joel Grannas Feb 10 '13 at 13:14
1

I will try to spend some time on this, but it looks like I will have to review the docs a bit more. – deefour Feb 22 '13 at 19:25
I am getting closer.... `def self.conditions(tables, conditions) table_name = Squeel::Nodes::Stub.new(tables[0].to_sym) joins(table_name) .where { table_name.send(conditions[0]).eq(conditions[1]) } end` for now i am just using direct calls to the array, but this code is a 50% working solution. I'm still having trouble getting it all to work though with the dynamic variables – Joel Grannas Feb 26 '13 at 18:20

score 0 · Answer 2 · answered Jan 03 '14 at 12:12

I don't know if this helps, but I did it this way 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 of something:

  dynamic_params = {'username' => 'some_name', 'email' => 'email@example.com'}

Then I use this with a chain of where's in a loop:

  query = SomeModel #could be User, etc
  dynamic_params.each_pair {|key,value| query = query.where(query_for_matches(key, value)) }

You can then pass query to your view or whatever. I've only been on rails for a couple of weeks so I'm not sure if this is a best practice but it works.

Squeel and rails... dynamic where clause

2 Answers2

Linked