I''ve been playing around with some easier ways of specifying
conditions in active record, and have come up with a little plugin
that some may find useful. Currently active record finder and
calculations methods can take a :conditions option with a string or
array listing some sql conditions, eg:
Animals.find :first, :conditions => "name =
''Tiger''"
My plugin enables simple conditions like this to be written without
any SQL at all, using a hash instead of an array or string:
Animals.find :first, :conditions => {:name => ''Tiger''}
As well as simple equality conditions, other tests such as more than,
less than and contains can also be specified:
Animals.find :first, :conditions => {:name_starts_with =>
''Tig''}
Animals.find :all, :conditions => {:population_more_than => 1_000_000}
Conditions can be combined:
endangered_tigers = Animals.find :all, :conditions => {:name_contains
=> ''Tiger'', :population_less_than => 10_000}
These conditions can also be used in calculations and scoped queries:
Animals.count :conditions => {:population_more_than => 1_000_000}
Animals.with_scope :find => {:conditions => {:name_contains =>
''Tiger''}} do
Animals.find :all
end
Finally, here''s an simple way to use this plugin to add lots of data
slicing and dicing to any list actions you might have:
class AnimalController < ActiveController
def list
animal_conditions = params.dup.reject! {|k, v| [:action, :control
ler].include? k}
@animals = Animal.find :all, :conditions => animal_conditions
end
end
This allows urls such as
/animal/list?name_starts_with=T&population_less_than=1000
Please take a look, the code is available at
svn://rubyforge.org/var/svn/popdog/slice_and_dice/tags/REL-0.1
I''d welcome any comments or ideas on how to improve this (currently
very simple) plugin. I do have some planned extra features of my own,
so watch this space!
Tom
--
email : tom at popdog.net
On Apr 22, 2006, at 7:24 AM, Tom Ward wrote:> I''ve been playing around with some easier ways of specifying > conditions in active record, and have come up with a little plugin > that some may find useful. Currently active record finder and > calculations methods can take a :conditions option with a string or > array listing some sql conditions, eg: > <snip> > I''d welcome any comments or ideas on how to improve this (currently > very simple) plugin. I do have some planned extra features of my own, > so watch this space! > > Tom > --Hey Tom- Just wanted to point you at my plugin called ez_where: http://brainspl.at/articles/2006/01/30/i-have-been-busy http://opensvn.csie.org/ezra/rails/plugins/dev/ez_where/ My plugin solves the same problems that slice and dice does but in a different way. It allows you to use ruby operators instead of sql ops and it allows find to take a block to define :conditions. It also allows for nested sub conditions and stuff like that. Here''s a little peak at what it can do: @articles = Article.find_with_block(:all, :include => :author) do | article, author| article.title =~ "%Foo Title%" author.any do name == ''Ezra'' name == ''Fab'' end end This will do a find :all with: :conditions => ["article.title LIKE ? AND (authors.name = ? OR authors.name = ?)", "%Foo Title%", "Ezra", "Fab"] Basically here is the breakdown of how we map ruby operators to SQL operators: foo == ''bar'' #=> ["foo = ?", ''bar''] foo =~ ''%bar'' #=> ["foo LIKE ?", ''%bar''] foo <=> (1..5) #=> ["foo BETWEEN ? AND ?", 1, 5] id === [1, 2, 3, 5, 8] #=> ["id IN(?)", [1, 2, 3, 5, 8]] <, >, >=, <= et all will just work like you expect. There is also the ability to create the conditions in stages so you can build up a query: cond = Caboose::EZ::Condition.new do foo == ''bar'' baz <=> (1..5) id === [1, 2, 3, 5, 8] end @result = Model.find(:all, :conditions=> cond.to_sql) #=> ["foo = ? AND baz BETWEEN ? AND ? AND id IN (?)", "bar", 1, 5, [1, 2, 3, 5, 8]] There is some seriously fun metaprogramming going on in ez_where that you might want to take a peek at ;-) Cheers- -Ezra PS. the test cases are 700 lines long and serve as the best docs right now. The tests exercise all the features of the plugin. -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060422/52f8b16e/attachment-0001.html
On Sat, 2006-04-22 at 09:12 -0700, Ezra Zygmuntowicz wrote:> > On Apr 22, 2006, at 7:24 AM, Tom Ward wrote: > > > I''ve been playing around with some easier ways of specifying > > conditions in active record, and have come up with a little plugin > > that some may find useful. Currently active record finder and > > calculations methods can take a :conditions option with a string or > > array listing some sql conditions, eg: > > <snip> > > > > I''d welcome any comments or ideas on how to improve this (currently > > very simple) plugin. I do have some planned extra features of my > > own, > > so watch this space! > > > > > > Tom > > -- > > > > Hey Tom- > > > Just wanted to point you at my plugin called ez_where: > > > http://brainspl.at/articles/2006/01/30/i-have-been-busy > http://opensvn.csie.org/ezra/rails/plugins/dev/ez_where/> > There is some seriously fun metaprogramming going on in ez_where that > you might want to take a peek at ;-) > > > Cheers- > -Ezra > > > PS. the test cases are 700 lines long and serve as the best docs right > now. The tests exercise all the features of the plugin.---- You beat me to the punch as I was about the say the same thing...as you know, I very much love ez_where and have made good usage of it. I can''t figure out how to determine with version I have and whether I should update it, nor can I tell when you update the plugin itself. Craig
On Apr 22, 2006, at 2:27 PM, Craig White wrote:> On Sat, 2006-04-22 at 09:12 -0700, Ezra Zygmuntowicz wrote: >> >> On Apr 22, 2006, at 7:24 AM, Tom Ward wrote: >> >>> I''ve been playing around with some easier ways of specifying >>> conditions in active record, and have come up with a little plugin >>> that some may find useful. Currently active record finder and >>> calculations methods can take a :conditions option with a string or >>> array listing some sql conditions, eg: >>> <snip> >>> >>> I''d welcome any comments or ideas on how to improve this (currently >>> very simple) plugin. I do have some planned extra features of my >>> own, >>> so watch this space! >>> >>> >>> Tom >>> -- >> >> >> >> Hey Tom- >> >> >> Just wanted to point you at my plugin called ez_where: >> >> >> http://brainspl.at/articles/2006/01/30/i-have-been-busy >> http://opensvn.csie.org/ezra/rails/plugins/dev/ez_where/ > >> >> There is some seriously fun metaprogramming going on in ez_where that >> you might want to take a peek at ;-) >> >> >> Cheers- >> -Ezra >> >> >> PS. the test cases are 700 lines long and serve as the best docs >> right >> now. The tests exercise all the features of the plugin. > ---- > You beat me to the punch as I was about the say the same thing...as > you know, I very much love ez_where and have made good usage of it. > > I can''t figure out how to determine with version I have and whether I > should update it, nor can I tell when you update the plugin itself. > > CraigCraig- ez_where is at revision 85 right now and hasn''t changed in about a month. I am working on an update for it with a few more niceties that I will roll out soon and I will be sure to let you know. Cheers- Ezra
That''s really cool, Tom. I love how readable your queries are. Duane Johnson (canadaduane) http://blog.inquirylabs.com/ On Apr 22, 2006, at 8:24 AM, Tom Ward wrote:> I''ve been playing around with some easier ways of specifying > conditions in active record, and have come up with a little plugin > that some may find useful. Currently active record finder and > calculations methods can take a :conditions option with a string or > array listing some sql conditions, eg: > > Animals.find :first, :conditions => "name = ''Tiger''" > > My plugin enables simple conditions like this to be written without > any SQL at all, using a hash instead of an array or string: > > Animals.find :first, :conditions => {:name => ''Tiger''} > > As well as simple equality conditions, other tests such as more than, > less than and contains can also be specified: > > Animals.find :first, :conditions => {:name_starts_with => ''Tig''} > Animals.find :all, :conditions => {:population_more_than => 1_000_000} > > Conditions can be combined: > > endangered_tigers = Animals.find :all, :conditions => {:name_contains > => ''Tiger'', :population_less_than => 10_000} > > These conditions can also be used in calculations and scoped queries: > > Animals.count :conditions => {:population_more_than => 1_000_000} > > Animals.with_scope :find => {:conditions => {:name_contains => > ''Tiger''}} do > Animals.find :all > end > > Finally, here''s an simple way to use this plugin to add lots of data > slicing and dicing to any list actions you might have: > > class AnimalController < ActiveController > def list > animal_conditions = params.dup.reject! {|k, v| [:action, :control > ler].include? k} > @animals = Animal.find :all, :conditions => animal_conditions > end > end > > This allows urls such as > /animal/list?name_starts_with=T&population_less_than=1000 > > Please take a look, the code is available at > svn://rubyforge.org/var/svn/popdog/slice_and_dice/tags/REL-0.1 > > I''d welcome any comments or ideas on how to improve this (currently > very simple) plugin. I do have some planned extra features of my own, > so watch this space! > > Tom > -- > email : tom at popdog.net > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails