Query AND/OR
3 views
Skip to first unread message
thanatos
Feb 5, 2008, 10:50:00 PM2/5/08
to DataMapper
Speaking to Sam on IRC yesterday he stated that he has yet to find a
syntax he likes for describing the AND/OR for sql. He also stated he
does not wish to have a mess of nested hashes, etc.
So, here is a place for people to throw out ideas on how we might
accomplish this.
My first inclination was to try and figure out a way to use &&, ||,
and (). However, I can't figure out how this might translate as a
passable parameter.
My second idea was to provide a to_s method for the Symbol::Operator
class to make it easy to simply create a string condition. This seems
inelegant at best.
Your thoughts?
syntax he likes for describing the AND/OR for sql. He also stated he
does not wish to have a mess of nested hashes, etc.
So, here is a place for people to throw out ideas on how we might
accomplish this.
My first inclination was to try and figure out a way to use &&, ||,
and (). However, I can't figure out how this might translate as a
passable parameter.
My second idea was to provide a to_s method for the Symbol::Operator
class to make it easy to simply create a string condition. This seems
inelegant at best.
Your thoughts?
thanatos
Feb 5, 2008, 11:02:00 PM2/5/08
to DataMapper
Going back to my &/| idea. Is this possible: override the operator to
behave as a method, taking our Symbol::Operator => value and returns a
string? We might be able to move creating conditions out of Query and
into a true Conditions class...or into Symbol::Operator.
An example:
Foo.all(:conditions => {Field1.eql => value1 | (Field2.like => value2
& Field3.eql => value3)}) =====> SELECT * FROM foo WHERE "foo.field1"
= 'value1' OR ("foo.field2" LIKE 'value2' AND "foo.field3" = 'value3);
Is this even possible? I'm coming from a C++ mentality here, where the
left and right-hand sides can be part of the overridden operator.
behave as a method, taking our Symbol::Operator => value and returns a
string? We might be able to move creating conditions out of Query and
into a true Conditions class...or into Symbol::Operator.
An example:
Foo.all(:conditions => {Field1.eql => value1 | (Field2.like => value2
& Field3.eql => value3)}) =====> SELECT * FROM foo WHERE "foo.field1"
= 'value1' OR ("foo.field2" LIKE 'value2' AND "foo.field3" = 'value3);
Is this even possible? I'm coming from a C++ mentality here, where the
left and right-hand sides can be part of the overridden operator.
Bernerd Schaefer
Feb 5, 2008, 11:24:52 PM2/5/08
to datam...@googlegroups.com
What about this:
Person.all { :name => "John".or("James"), :age.lt => 12 }
# SELECT * FROM people WHERE (name = "John" OR name = "James") AND age < 12
Person.all { :name => "John" }.or { :age.lt => 12 }
# SELECT * FROM people WHERE name = "John" OR age < 12
Person.all { :name => "John" }.or({ :age.gt => 30 }), { :title => "Manager" }
# SELECT * FROM people WHERE (name = "John" OR age > 30) AND title = "Manager"
That is, we add .or to {} and "" (in a smart way, like rspec), and allow the finders to accept an array of hashes to keep ands and ors separate.
As with all of these methods, I can think of many pros and cons...
Message has been deleted
Adam French
Feb 5, 2008, 11:50:48 PM2/5/08
to datam...@googlegroups.com
This might be fugly, but how about:
q = Query.new(:name => 'John', :gender => 'Male').or(:name => 'Jenny', :gender => 'Female')
Person.all(q)
Internally, {} would still get joined together with an understood AND, but you could get even sillier with something like:
Query.new {:name => 'John', :gender => 'Male'}.or({:name => 'Jenny', :gender => 'Female'}.or(:name => 'Pat', :gender => 'Female'))
Person.all(q)
Just some thoughts.
===
~Adam
Sam Smoot
Feb 6, 2008, 12:04:51 AM2/6/08
to DataMapper
I've thought along the same lines, but () isn't an operator, so that's
out. It also feels a little dirty to use & and | when everywhere else
it's && and ||.
The obvious solution is to use ParseTree, which would allow for pure-
ruby... maybe it's time to seriously think about Ambition or something
similar as a first-class finder mechanism in DM?
out. It also feels a little dirty to use & and | when everywhere else
it's && and ||.
The obvious solution is to use ParseTree, which would allow for pure-
ruby... maybe it's time to seriously think about Ambition or something
similar as a first-class finder mechanism in DM?
Sam Smoot
Feb 6, 2008, 12:15:31 AM2/6/08
to DataMapper
Those examples would need to be wrapped in parens, or they'd be
treated as blocks.
Otherwise it's similar to a library by Jay Fields that uses Proc#or.
Also, something to keep in mind: The order of OR conditions can have
big performance implications for query-runtime, so it's important that
order is preserved if the dev goes through the thought of putting the
cheaper conditions first assuming that the generated sql is not going
to shuffle their expressions.
Your examples don't fall victim to that with the #or and Array of Hash
objects, so that's good, but others have suggested syntaxes that do,
so just want to put that out there.
Maybe something like Ambition though? So instead of: Person.all :name
=> 'John' we'd have: Person.all { name == 'John' }
This would let us have stuff like: Person.all { (name == 'John' ||
name == 'Jack') && title == 'Manager' } as well.
Maybe... and this is kinda "out there", but maybe that syntax should
obsolete the existing Hash based finder syntax?
treated as blocks.
Otherwise it's similar to a library by Jay Fields that uses Proc#or.
Also, something to keep in mind: The order of OR conditions can have
big performance implications for query-runtime, so it's important that
order is preserved if the dev goes through the thought of putting the
cheaper conditions first assuming that the generated sql is not going
to shuffle their expressions.
Your examples don't fall victim to that with the #or and Array of Hash
objects, so that's good, but others have suggested syntaxes that do,
so just want to put that out there.
Maybe something like Ambition though? So instead of: Person.all :name
=> 'John' we'd have: Person.all { name == 'John' }
This would let us have stuff like: Person.all { (name == 'John' ||
name == 'Jack') && title == 'Manager' } as well.
Maybe... and this is kinda "out there", but maybe that syntax should
obsolete the existing Hash based finder syntax?
psada...@gmail.com
Feb 6, 2008, 12:19:39 AM2/6/08
to DataMapper
This all seems really silly to me. I like the shortcuts for the really
simple cases {:name => 'Bob'}, and even the slightly complex ones
{:name => 'Bob', :age => 21}. For the complex cases, though, we have a
perfectly good language for building these queries: SQL. This is the
kind of thing it was designed to do, and it makes it really easy. We
can help out with the escaping, I like the syntax like:
:where => ["(name = :name1 OR name = :name2) AND age >= :age",
{:name1 => 'Bob', :name2 => 'Joe', :age => 21}]
But the ONLY way that would make any sense to ruby-ize that, would be
to do it the way you always would in ruby.
(:name == "Bob" || :name == "Joe") && :age >= 21
Anything else just feels extremely hackish to me, and certainly
violates the principle of least surprise. I'm not even a fan of the
`:age.gt` syntax. Its also limiting in that you could only constuct
queries of moderate complexity using this method, for the really
complex ones, you still have to fall back to writing SQL.
If we insist on doing this, then I vote that we just include ambition,
since its already been mostly written, like we did with Validatable.
Paul
simple cases {:name => 'Bob'}, and even the slightly complex ones
{:name => 'Bob', :age => 21}. For the complex cases, though, we have a
perfectly good language for building these queries: SQL. This is the
kind of thing it was designed to do, and it makes it really easy. We
can help out with the escaping, I like the syntax like:
:where => ["(name = :name1 OR name = :name2) AND age >= :age",
{:name1 => 'Bob', :name2 => 'Joe', :age => 21}]
But the ONLY way that would make any sense to ruby-ize that, would be
to do it the way you always would in ruby.
(:name == "Bob" || :name == "Joe") && :age >= 21
Anything else just feels extremely hackish to me, and certainly
violates the principle of least surprise. I'm not even a fan of the
`:age.gt` syntax. Its also limiting in that you could only constuct
queries of moderate complexity using this method, for the really
complex ones, you still have to fall back to writing SQL.
If we insist on doing this, then I vote that we just include ambition,
since its already been mostly written, like we did with Validatable.
Paul
psada...@gmail.com
Feb 6, 2008, 12:23:59 AM2/6/08
to DataMapper
> Maybe... and this is kinda "out there", but maybe that syntax should
> obsolete the existing Hash based finder syntax?
simple cases. eg:
Person.all(:name => 'John')
Person.all(name == 'John')
Paul
Luke Sutton
Feb 6, 2008, 12:24:58 AM2/6/08
to DataMapper
+1
Remove the hash syntax and move to something like Ambition. It looks
nice :)
Is simply using Ambition an option?
Remove the hash syntax and move to something like Ambition. It looks
nice :)
Is simply using Ambition an option?
thanatos
Feb 6, 2008, 9:20:58 AM2/6/08
to DataMapper
It looks like someone is using Ambition to make a DM adapter already:
http://ambition.rubyforge.org/ (bottom of the page).
http://ambition.rubyforge.org/ (bottom of the page).
José Valim
Feb 6, 2008, 11:25:13 AM2/6/08
to DataMapper
Ambition is nice.
Pros:
1. Kicker methods: makes easier to do caching
2. Chain methods: User.select(...).sort_by(...).first(5)
Cons:
1. It should accept more stuff than just Blocks.
The select method, for example, could accept:
+ Proc
User.select { (:name == "Bob" || :name == "Joe") && :age >= 21 }.
+ String (obviously)
User.select "(name = 'Bob' OR name = 'Joe') AND age >= 21"
+ Arrays (as rails)
User.select ["(name = ? OR name = ?) AND age >= ?",'Bob','Joe',21]
+ Hash (with just some of the datamapper magic)
User.select {:name => 'Bob', :name => 'Joe'}
Is important to give options mainly because of performance:
http://groups.google.com/group/sequel-talk/browse_thread/thread/9da17d7c9fbd48ea
In the 4th message there is a benchmark done by Sharon Rosner and
we see that the Proc stuff is really slow.
Another pro is that DataMapper can totally adapt itself into Ambition.
So it can provide a find method with chain and kicker functionality:
User.find(:where => ["(name = ? OR name = ?) AND age >= ?",'Bob','Joe',
21], :order_by => "age DESC").first(5)
This is nice because find, select and sort_by methods would just
change a Hash Object. Then we could do things like this:
class Articles < Datamapper
def recent
self.find(:order_by => 'created_at DESC')
end
def published
self.select(:published => true)
end
end
Articles.published.recent.first(5)
The Hash Object is processed only when the kicker methods are called.
Pros:
1. Kicker methods: makes easier to do caching
2. Chain methods: User.select(...).sort_by(...).first(5)
Cons:
1. It should accept more stuff than just Blocks.
The select method, for example, could accept:
+ Proc
User.select { (:name == "Bob" || :name == "Joe") && :age >= 21 }.
+ String (obviously)
User.select "(name = 'Bob' OR name = 'Joe') AND age >= 21"
+ Arrays (as rails)
User.select ["(name = ? OR name = ?) AND age >= ?",'Bob','Joe',21]
+ Hash (with just some of the datamapper magic)
User.select {:name => 'Bob', :name => 'Joe'}
Is important to give options mainly because of performance:
http://groups.google.com/group/sequel-talk/browse_thread/thread/9da17d7c9fbd48ea
In the 4th message there is a benchmark done by Sharon Rosner and
we see that the Proc stuff is really slow.
Another pro is that DataMapper can totally adapt itself into Ambition.
So it can provide a find method with chain and kicker functionality:
User.find(:where => ["(name = ? OR name = ?) AND age >= ?",'Bob','Joe',
21], :order_by => "age DESC").first(5)
This is nice because find, select and sort_by methods would just
change a Hash Object. Then we could do things like this:
class Articles < Datamapper
def recent
self.find(:order_by => 'created_at DESC')
end
def published
self.select(:published => true)
end
end
Articles.published.recent.first(5)
The Hash Object is processed only when the kicker methods are called.
dkubb
Feb 6, 2008, 3:03:25 PM2/6/08
to DataMapper
On Feb 5, 9:04 pm, Sam Smoot <ssm...@gmail.com> wrote:
> maybe it's time to seriously think about Ambition or something
> similar as a first-class finder mechanism in DM?
+1 for using Ambition as the first-class finder in DM.
> maybe it's time to seriously think about Ambition or something
> similar as a first-class finder mechanism in DM?
As others have mentioned Ambition isn't perfect, but nothing can ever
replace SQL in every case, and I'm OK with that.
As soon as you start talking about trying to be "Ambition like", or
something close to Ambition, I think it would be better to adopt it
and put any effort you were going to put into your custom engine into
improving Ambition instead.
Dan
José Valim
Feb 6, 2008, 5:37:15 PM2/6/08
to DataMapper
Exactly!
And since the ambition-adapter has to be written down, some of
DataMapper's methods can be redesign to tie both tightly!
And since the ambition-adapter has to be written down, some of
DataMapper's methods can be redesign to tie both tightly!
thanatos
Feb 6, 2008, 10:14:11 PM2/6/08
to DataMapper
I posted over in the Ambition google group inquiring who was
responsible for the current state of the DM adapter for ambition.
See here: http://groups.google.com/group/ambition-rb/browse_thread/thread/7c34248646aeaa3b
responsible for the current state of the DM adapter for ambition.
See here: http://groups.google.com/group/ambition-rb/browse_thread/thread/7c34248646aeaa3b
thanatos
Feb 6, 2008, 10:32:09 PM2/6/08
to DataMapper
One issue I perceive with using ambition is that we have a fairly
complicated select process, and the ability to map a model property
name to a different column name in the database. We're really looking
to use the conditions parsing most of all.
complicated select process, and the ability to map a model property
name to a different column name in the database. We're really looking
to use the conditions parsing most of all.
thanatos
Feb 6, 2008, 10:34:09 PM2/6/08
to DataMapper
If I learned anything from my C++ classes it is that chaining these
overloaded operators together should be the easy part. I foresee that
being able to mix different types of conditions will be the difficult
part.
Perhaps, that will be the part left to the Query class that Sam is
building.
On Feb 6, 10:14 pm, thanatos <tpitale...@gmail.com> wrote:
> I posted over in the Ambition google group inquiring who was
> responsible for the current state of the DM adapter for ambition.
>
> See here:http://groups.google.com/group/ambition-rb/browse_thread/thread/7c342...
overloaded operators together should be the easy part. I foresee that
being able to mix different types of conditions will be the difficult
part.
Perhaps, that will be the part left to the Query class that Sam is
building.
On Feb 6, 10:14 pm, thanatos <tpitale...@gmail.com> wrote:
> I posted over in the Ambition google group inquiring who was
> responsible for the current state of the DM adapter for ambition.
>
thanatos
Feb 7, 2008, 12:07:52 AM2/7/08
to DataMapper
Okay, so, I've been going through all of the Ambition and ambitious AR
code. It suddenly struck me as to what makes sense. The magic is in
what Sam said about ParseTree. This was probably evident to all of you
as soon as he said it but, for those that didn't quite get it (like
me):
ParseTree is used by Ambition to turn (u.name == 'Chris' && u.age ==
22) into deeply nested arrays that are very easy to turn around and
parse back up again. An example would be (from Ambition) (m.name ==
'chris') ===> [[:call, [:dvar, :m], :name], :==, [:array, [:str,
"chris"]]]. So, with this we could simply match the == SYMBOL to a
particular method, pass it the left and right sides (:name, "chris")
as arguments and pass that result to anything in the array above it.
Ambition is pretty powerful but, it will need a good bit of work to
integrate it into Sam's Query class. I'm going to try and get started
using Query as a basis as well as a simple DM adapter that I'm making
right now.
code. It suddenly struck me as to what makes sense. The magic is in
what Sam said about ParseTree. This was probably evident to all of you
as soon as he said it but, for those that didn't quite get it (like
me):
ParseTree is used by Ambition to turn (u.name == 'Chris' && u.age ==
22) into deeply nested arrays that are very easy to turn around and
parse back up again. An example would be (from Ambition) (m.name ==
'chris') ===> [[:call, [:dvar, :m], :name], :==, [:array, [:str,
"chris"]]]. So, with this we could simply match the == SYMBOL to a
particular method, pass it the left and right sides (:name, "chris")
as arguments and pass that result to anything in the array above it.
Ambition is pretty powerful but, it will need a good bit of work to
integrate it into Sam's Query class. I'm going to try and get started
using Query as a basis as well as a simple DM adapter that I'm making
right now.
thanatos
Feb 7, 2008, 1:59:54 AM2/7/08
to DataMapper
One possible solution, with much work to be done.
This might have some major flaws, I'm pretty tired. I did the best I
could before I fell asleep. It isn't as perfectly natural as Ambition
but, it's pretty close to the sql (aside from the use of ^ for not,
which I almost used not...this ^ as not came from regexp in my mind)
Here goes:
http://pastie.caboo.se/148570
Laptop's dead, pe@ce.
This might have some major flaws, I'm pretty tired. I did the best I
could before I fell asleep. It isn't as perfectly natural as Ambition
but, it's pretty close to the sql (aside from the use of ^ for not,
which I almost used not...this ^ as not came from regexp in my mind)
Here goes:
http://pastie.caboo.se/148570
Laptop's dead, pe@ce.
thanatos
Feb 7, 2008, 8:47:20 AM2/7/08
to DataMapper
Well, there are a few big faults in this last plan but, I'm working on
them and should be able to get somewhere.
them and should be able to get somewhere.
thanatos
Feb 7, 2008, 9:02:38 AM2/7/08
to DataMapper
The biggest fault is operator precedence, in trying to chain
everything together in one expression...ruby is evaluating different
parts first, instead of simply in order like we would need.
The only solution to that is using () or dot notation
everywhere...which sucks.
So, how's Ambition look? ;-)
everything together in one expression...ruby is evaluating different
parts first, instead of simply in order like we would need.
The only solution to that is using () or dot notation
everywhere...which sucks.
So, how's Ambition look? ;-)
José Valim
Feb 8, 2008, 9:22:19 PM2/8/08
to DataMapper
Wow! You were quick!
I'm going to take a look at it as soon as I put my notebook to work
again!
I'm going to take a look at it as soon as I put my notebook to work
again!
thanatos
Feb 10, 2008, 8:43:43 PM2/10/08
to DataMapper
ReinH and I are working on a proper conditions parser, at the moment.
Reply all
Reply to author
Forward
0 new messages