Topic: Doing something like a "named_scope" on a non db value?

Let's say I have People and Payments (payments belong_to people, people has_many payments), and I want to return a scope of People with a sum of Payments.amount > 0...

So I'd like to do something like Person.paid.another_named_scope (where paid gets any Person with a sum of payments > 0)...Is this possible?

Re: Doing something like a "named_scope" on a non db value?

It's a good question and one I wasn't sure about (and I'm still not 100% sure.) I just had a few experiments but I don't think it's possible. To get a sum from any field, whether it's in the parent or child models, you have to group the results in sql. You can't both query the grouped, summed results and still expect the regular fields to be returned at the same time. So I can only get either the sum of all payments, or the parent record's id, title, what have you, but not both in the same query.

I also tried to use a virtual column as the basis for the conditions (without using sum), just to see what would happen, but that failed too;

SELECT tasks.name as task_name FROM `projects` LEFT OUTER JOIN `tasks` ON tasks.project_id = projects.id WHERE (task_name = "test")

Fails with an "unknown column task_name". So I think this is making things too complicated for sql. If anyone knows a way to make this work though I'd love to hear it too.

Re: Doing something like a "named_scope" on a non db value?

Well I thought this might be the solution:

named_scope :test_sum, { 
  :joins=>["INNER JOIN payments ON payments.person_id = people.id"],
  :group=> 'people.id',
  :select=>'SUM(payments.amount) AS sum_amount',
  :conditions=>["sum_amount > 1000"]

    }


But I get a SQL error (using SQLite)...apparently it doesn't like using the SUM-aggregation as a condition?

ActiveRecord::StatementInvalid: SQLite3::SQLException: misuse of aggregate:

Re: Doing something like a "named_scope" on a non db value?

I tried a few more times and I couldn't get it to work. The "where" clause in mysql just wouldn't recognize an aliased sum or count column. So I dug through the mysql manual a bit and I came up with this;

  named_scope :test_sum_alias,
              :joins=>["INNER JOIN tasks ON tasks.project_id = projects.id"],
              :group=> 'projects.id',
              :select=>'*, SUM(tasks.id) AS sum_amount',
              :having => "sum_amount > 5"

I just replaced "condiditions" with "having", which is apparently needed for sum columns. Now it works perfectly ^^ From what I've read the same syntax applies for SQLite.

Note for testing purposes I summed the ids from the Tasks model which obviously doesn't have any real use. Also you have to put a "*," in your select before the SUM so you still get your record attributes as well as the sum value.

This was the explanation I found;

When selecting count, sum, or some other aggregate function, the value isn

Last edited by marsvin (2009-03-28 12:05:56)

Re: Doing something like a "named_scope" on a non db value?

I figured it was that, having remembered some use for HAVING awhile ago...but it doesn't work for me on SQLite. Apparently ActiveRecord doesn't think it's a valid key.

    named_scope :testb, { 
      :joins=>["INNER JOIN events ON events.entity_id = entities.id"],
      :group=> 'entities.id',
      :select=>'*, SUM(events.amount) AS sum_amount',
      :having=>"sum_amount > 10000000"
      }

>> Entity.testb
ArgumentError: Unknown key(s): having
    from /Library/Ruby/Gems/1.8/gems/activesupport-2.2.2/lib/active_support/core_ext/hash/keys.rb:47:in `assert_valid_keys'
    from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/base.rb:1968:in `with_scope'
    from (__DELEGATION__):2:in `__send__'
    from (__DELEGATION__):2:in `with_scope'
    from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/named_scope.rb:169:in `method_missing'
    from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/named_scope.rb:177:in `load_found'
    from /Library/Ruby/Gems/1.8/gems/activerecord-2.2.2/lib/active_record/named_scope.rb:161:in `proxy_found'
    from (__DELEGATION__):2:in `inspect'
    from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:298:in `output_value'
    from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:151:in `eval_input'
    from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:259:in `signal_status'
    from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:147:in `eval_input'
    from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:146:in `eval_input'
    from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:70:in `start'
    from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:69:in `catch'
    from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:69:in `start'
    from /usr/bin/irb:13>>

Re: Doing something like a "named_scope" on a non db value?

Are you using sqlite or sqlite3? I just converted my app to use sqlite3 instead of MySQL and it works perfectly with the same named_scope I pasted above.

Re: Doing something like a "named_scope" on a non db value?

sqlite3. The problem is not using 2.3, which supports SQLite's "HAVING". Workaround described here:
http://railsforum.com/viewtopic.php?id=28722

Re: Doing something like a "named_scope" on a non db value?

haha yeah I just saw your other message and Duplex' answer ^^ Good to know what the problem was anyway!

Posts [ 8 ]

Pages 1