
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
-
Ben Johnson December 17th, 2008 @ 11:30 AM
- State changed from new to resolved
Yep, I am going to have to remove that for mysql and postgres. I got the same error, I assumed since it worked in SQLite it would work in MySQL. Thanks for letting me know.
-
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.
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.