SQL Comparison Operators about model HOT 13 CLOSED

At this moment the .where method accept only a Hash. (https://github.com/lotus/model/blob/master/lib/lotus/model/adapters/sql/query.rb#L78-L117)

@jodosha do you have something in mind of how the API for these operator should be?

from model.

@jodosha what do you think about adopting https://github.com/rails/arel?

from model.

@joneslee85 In which context? Repository, Adapter?

from model.

@jodosha you can use sequel's where block condition. You can use it like this:

def self.where_age_greater_than(gt)
    query do
       where{ age > gt }
    end
end

I made an test implementation for this. coenert/model@6cdfd861a1ebe6783410690effb5faebc1dd3c51

from model.

@coenert nice! This way we don't need to use arel.

from model.

@jodosha In Repository, I really like their powerful query builder.

from model.

@joneslee85 ehm, i think the only way to adopt ARel is to use it as an sql query builder in our sql_adapter, ARel is just made to build queries without knowing the db. But sequel already provides most of the functions ARel has. @jodosha what's your opinion about using sequel's(or ARel's) api to build our queries(like my example)? The problem of this is that you need to wrap their documentation and you don't always know if there are little not documented features added that you don't want.

from model.

@coenert @carloslopes @joneslee85 Let's clarify one thing first: query API is not part of a repository. A repository is a client of that API, which is implemented by the adapters.

ARel: I really like it, but we can achieve the same results using Sequel for relational databases.

Now, I'm aware of the power of the lambda based API of Sequel. That wasn't introduced it in SqlAdapter::Query in the first release because I was worried of the overhead that the procs may introduce to resolve each single condition.

On the other hand we can have SQL literals, but they are dangerous for injections, and the author encourages to use of lambdas. Another con is couple repository implementation with specific SQL fragments, whereas lambdas are Ruby, so they are abstracted.

The way to follow are lambdas. @coenert Would you like to take care of this and create a PR? I noticed that you didn't tested the condition that triggers ArgumentError. Speaking of that, I ran some benchmarks. It turned out that the implementation that performs better on average is:

def where(condition = nil, &blk)
  condition or blk or raise ArgumentError.new('You need to specify an condition.')
  # ...
end

One unresolved issue is about database functions: how to express them with that API?
Sequel offers a solution:

dataset.update(:updated_at => Sequel.function(:NOW))
dataset.update(:updated_at => Sequel.lit('NOW()'))

But I see it cumbersome to use in situations like this: where('DATE(created_at) < NOW()').
What do you think about of the following proposal?

where{ function(:DATE, created_at) < function(:NOW) }

from model.

@jodosha yes, i wil create a PR when i have time.
ArgumentError: nice!, i will implement it that way.
The problem with those functions is that you have to add functionality to Sequel's DSL, maybe we can build are own DSL for this wrapping sequel's dsl, and add functions to that dsl?

from model.

@jodosha I agree to use Sequel that is already a dependency to solve this is the better way.

@coenert solution is simple and effective.

👍

from model.

@jodosha The only way to add the function method is adding an method to ::Sequel::SQL::VirtualRow.

class ::Sequel::SQL::VirtualRow
    def function(name, *args)
        return Sequel.function(name, *args)
    end
end

With this you can use function in you where queries:

where{ function(:DATE, Date.today) < function(:NOW) }

Do you agree with this implementation?

from model.

@coenert In my experience, reopening libraries is always source of maintenance pain.
Let's focus first on that lambda syntax, that will solve a lot of problems and we'll figure out how to support functions. Thanks for investigating and taking care of this. 👍

from model.

@jodosha I can imagine your concerns. I think there are 2 ways of implementing functions in the lambda.

• Subclassing VirtualRow, add functions and translate VirtualRow to sequel where conditions
• Complete custom implementation of VirtualRow

For the long term i prefer option 2 because if you use Sequels implementation you are adding functions(and bugs) you don't know about, and because we always have the attributes to our disposal we don't have to implement method_missing.

from model.