Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

Improve conditions hash to support complex queries

#335

I have recently been trying out merb with datamapper and for the most part I actually prefer activerecord but there is one part of datamapper that I really have enjoyed and that is the enhanced conditions hash syntax...ie. 

# 'gt' means greater-than. We also do 'lt'.
Person.all(:age.gt => 30)
# 'gte' means greather-than-or-equal-to. We also do 'lte'.
Person.all(:age.gte => 30)
Person.all(:name.not => 'bob')
# If the value of a pair is an Array, we do an IN-clause for you.
Person.all(:name.like => 'S%', :id => [1, 2, 3, 4, 5])

or if you prefer (maintaining traditional conditions syntax) 

Person.all(:conditions => { :age.gt => 30, :name.like => "%a%" }) #=> people.`age` > 30 AND people.`name` LIKE "%a%"

I have also implemented an 'any' block that works as follows: 

Person.any(:conditions => { :age.gt => 30, :name.like => "%a%" }) #=> people.`age` > 30 OR people.`name` LIKE "%a%"

I find this syntax to be quite a beautiful alternative to the other ways of specifying more complex conditions and this is something that could be integrated into activerecord relatively easily. Note that this change will maintain complete backward compatibility with the previous conditions syntax and simply adds in this on top of the existing simple hash find structure.

I am aware of this extension: http://www.continuousthinking.co... but it seems like it has been outdated and it seemed a bit heavy to introduce into core.

I already have a roughly complete implementation, but before I finish it up and submit it, I would like some feedback on if this could possibly be accepted into core or if I would be better advised just making this a plugin. In my opinion, this is something that would really be well-suited for being included in activerecord core even if it doesn't make it in just yet.

Reported by Nathan Esquenazi · June 5th, 2008 @ 03:12 PM

State: wontfix
Milestone: none
Assigned to: nobody
Importance: none

Activity

  1. Nathan Esquenazi
    Nathan Esquenazi
    • Title changed from Improve conditions hash to support more complex queries to Improve conditions hash to support complex queries

    June 5th, 2008 @ 10:19 AM

  2. nerdrew
    nerdrew

    Another approach would be: 

    Person.find(:all, :conditions => { :age.gt => 30, :name.like => "%a%", :or => { :place => 'Boca', :occupation => 'Chef'} }) #=> people.`age` > 30 AND people.`name` LIKE "%a%" AND (people.place =  "Boca" OR people.occupation = "Chef")

    This allows the construction of queries with AND's and OR's used.

    June 10th, 2008 @ 10:46 PM

  3. nerdrew
    nerdrew

    I would really like to see something like this in active record.

    June 10th, 2008 @ 10:48 PM

  4. Nathan Esquenazi
    Nathan Esquenazi

    Thanks for your feedback nerdrew. I actually do like that syntax. I was trying to think of a way to be able to combine AND / OR into a single hash. I think you touched on a good way to do that.

    I want to go in baby steps though in hopes of even getting the enhanced AND hash into core and go from there. This is one of those features I want to see on edge rails as being included in rails 2.2.

    I have created the plugin here: http://github.com/xgamerx/condit...

    But again, this is something I would prefer to see baked into activerecord.

    June 10th, 2008 @ 11:04 PM

  5. nerdrew
    nerdrew

    Another thing I'd like to see in active record conditions hashes is the ability to use conditions on included tables. Also I'd like these tables to be automatically included. I understand that it needs to be kept simple in order to be included into edge rails, but while I'm wishing away, I figured I'd add it. 

    
Person.find(:all, :conditions => {:name => 'Bob', :home => {:state => 'CA'}}

#=> SELECT * FROM people left outer join homes on person.id = homes.person_id WHERE person.name = "Bob" AND homes.state = "CA"

    I know the SQL isn't exactly the same as AR output, but it gives the idea.

    June 10th, 2008 @ 11:21 PM

  6. Pratik
    Pratik
    • State changed from new to wontfix
    • Tag set to activerecord, conditions, enhancement

    Please discuss this on the mailing list. There might be some parallel efforts for doing the same. IIRC, ARel does this as well.

    ARel - http://github.com/nkallen/arel/tree

    July 23rd, 2008 @ 01:44 PM

  7. dharmdip
    dharmdip

    August 6th, 2008 @ 12:31 PM

  8. Mihai Alexandru Bîrsan
    Mihai Alexandru Bîrsan

    What @nerdrew said can be easily achieved by looking at associations (like what :include does). Also, the query should only select the model being asked for, unless otherwise specified.

    @@@ruby Person.find(:all, :conditions => {:name => 'Bob', :home => {:state => 'CA'}}

    => SELECT person.* FROM people left outer join homes on person.id = homes.person_id WHERE person.name = "Bob" AND homes.state = "CA"

    

Perhaps :joins => :homes could be specified to take `SELECT *` instead of `SELECT person.*`.

    August 19th, 2008 @ 09:33 PM

  9. nerdrew
    nerdrew

    I've already done something so I can use the conditions as I've described them. I've attached the relevant part of my plugin for this.

    August 20th, 2008 @ 12:45 AM

  10. Mihai Alexandru Bîrsan
    Mihai Alexandru Bîrsan

    @nerdrew I can't see your attachment... Did it go through? :)

    August 20th, 2008 @ 07:24 AM

  11. nerdrew
    nerdrew

    It shows up on the right side above the list of people watching this ticket for me. If it didn't go through I can resend it. ar_conditions.rb

    August 20th, 2008 @ 04:25 PM