#39 ✓resolved

Modifier "sum" not working with MySQL

Reported by Silex | December 17th, 2008 @ 11:29 AM

@search.conditions.sum_of_id_gt = 100 translates into being a query modifier (be in a where clause) when for mysql it should be a group by aggregate function. In other words, the generated SQL should contain a GROUP BY foo HAVING SUM(bar) > 100.

  silex@Silex ~ mysql --version
  mysql  Ver 14.14 Distrib 5.1.29-rc, for apple-darwin9.4.0 (i386) using readline 5.1
  silex@Silex ~ script/console 
  Loading development environment (Rails 2.1.2)
  >> Searchlogic::Version::STRING
  => "1.6.2"
  >> User
  => User(id: integer, first_name: string, last_name: string, created_at: datetime, updated_at: datetime)
  >> search = User.new_search
  => #<UserSearch {:limit=>25}>
  >> search.conditions.sum_of_id_gt = 100
  => 100
  >> search.all
  ActiveRecord::StatementInvalid: Mysql::Error: #HY000Invalid use of group function: SELECT * FROM `users` WHERE (SUM(`users`.`id`) > 100) LIMIT 25
  >> User.find_by_sql('SELECT * FROM `users` GROUP BY id HAVING (SUM(`users`.`id`) > 100)
  => []

This is a meaningless example but it's simple enough to illustrate the problem. I'm not sure there is an elegant solution for that problem or even if you plan to address it, I just thought it'd be nice to report it for future people trying that kind of things.

Comments and changes to this ticket

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

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

Provides common named scopes and object based searching.

People watching this ticket