This project is archived and is in readonly mode.

#335 ✓wontfix
Nathan Esquenazi

Improve conditions hash to support complex queries

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

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.

Comments and changes to this ticket

  • Nathan Esquenazi

    Nathan Esquenazi June 5th, 2008 @ 10:19 AM

    • Title changed from “Improve conditions hash to support more complex queries” to “Improve conditions hash to support complex queries”
  • nerdrew

    nerdrew June 10th, 2008 @ 10:46 PM

    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.

  • nerdrew

    nerdrew June 10th, 2008 @ 10:48 PM

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

  • Nathan Esquenazi

    Nathan Esquenazi June 10th, 2008 @ 11:04 PM

    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.

  • nerdrew

    nerdrew June 10th, 2008 @ 11:21 PM

    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.

  • Pratik

    Pratik July 23rd, 2008 @ 01:44 PM

    • 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

  • dharmdip

    dharmdip August 6th, 2008 @ 12:31 PM

    • no changes were found...
  • Mihai Alexandru Bîrsan

    Mihai Alexandru Bîrsan August 19th, 2008 @ 09:33 PM

    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.*`.
  • nerdrew

    nerdrew August 20th, 2008 @ 12:45 AM

    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.

  • Mihai Alexandru Bîrsan

    Mihai Alexandru Bîrsan August 20th, 2008 @ 07:24 AM

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

  • nerdrew

    nerdrew August 20th, 2008 @ 04:25 PM

    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

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

People watching this ticket

Attachments

Tags

Pages