
counting rows when using group_by statement
Reported by Maciej Litwiniuk | February 9th, 2009 @ 11:59 AM
When using "group by" statement, searchlogic perform wrong count - if all conditions for counting are used in count query, why group clause is not? ie.
search = Price.new_search(session[:search])
search.conditions.adult_is_not = nil
search.group = "tour_id"
will produce following sql queries:
SELECT `prices`.* FROM `prices` WHERE (`prices`.`adult` IS NOT NULL) GROUP BY tour_id ORDER BY `prices`.`adult`
SELECT count(DISTINCT `prices`.id) AS count_id FROM `prices` WHERE (`prices`.`adult` IS NOT NULL)
IMHO counting should also group records... :)
Comments and changes to this ticket
-
Ben Johnson February 17th, 2009 @ 03:30 AM
- State changed from new to open
When you pass group to a count query ActiveRecord returns an array of values, which is not what you are expecting. Am I correct? Beucase I ran some tests allowing the group option and I got an array returned as a value.
-
Maciej Litwiniuk February 17th, 2009 @ 10:27 AM
Well, no :)
ie.
@prices, @prices_count = @search.all, @search.count
@prices_count
will be number of records counted withoutgroup by
statement. I hope now you got my point. Wrong result returned by@search.count
will influence ie.page_links
helper, which will return to much pages -
Ben Johnson February 17th, 2009 @ 11:28 AM
I understand, what I'm saying is that when the group option is passed to a calculation ActiveRecord doesn't return a float or an integer, it returns and array.
-
Ben Johnson February 17th, 2009 @ 11:29 AM
>> Neighborhood.count(:group => "id") => [[641773101, 1], [799328388, 1]]
-
Maciej Litwiniuk February 17th, 2009 @ 12:30 PM
Hmm... maybe :)
I was just analyzing queries in debug mode in which sql seems to be incorrect. I just assumed, that this is ActiveQuery bug :)
Btw, two queries from tickets body are produced when calling this:
@prices, @prices_count = @search.all, @search.count
but this is obvious...
-
Ben Johnson February 17th, 2009 @ 12:39 PM
- State changed from open to resolved
Let me know if you find anything else. I dont know what to do with the array returned when using the group option. The count is incorrect though?
-
Maciej Litwiniuk February 17th, 2009 @ 01:35 PM
@search = Price.new_search({:per_page=>5000}) @search.conditions.adult_is_not = nil @search.group = "tour_id" @prices, @prices_count = @search.all, @search.count puts @prices.type # => Array puts @prices_count.class # => Fixnum puts @prices_count # => 11273 !!!!WRONG!!!! puts @prices.length # => 241
So this is bug in SearchLogic, not in ActiveRecord, or I am wrong?
-
Maciej Litwiniuk February 17th, 2009 @ 01:35 PM
puts @prices.type # => Array
should be of courseputs @prices.class # => Array
-
Ben Johnson February 17th, 2009 @ 01:38 PM
I'm not sure if its a bug or not to be honest, but I am certain that trying to perform a calculation with a group statement returns an array. Can you execute the count query without searchlogic?
Price.count(:conditions => "adult is not null", :group => "tour_id")
What do you get for that?
-
Maciej Litwiniuk February 18th, 2009 @ 12:28 PM
>> Price.count(:conditions => "adult is not null", :group => "tour_id") => [[1, 36], [2, 36], [3, 28], [4, 28], ..... , [2423, 26], [2424, 30]]
Even if querying ActiveRecord, not SearchLogic returns an array, shouln't (SearchLogics) @search.count call appropriate query and return a Fixnum?
-
-
Maciej Litwiniuk February 19th, 2009 @ 01:37 PM
Then I don't get it, how i get such results... (copied from above)
@search = Price.new_search({:per_page=>5000}) @search.conditions.adult_is_not = nil @search.group = "tour_id" @prices, @prices_count = @search.all, @search.count puts @prices.class # => Array puts @prices_count.class # => Fixnum puts @prices_count # => 11273 !!!!WRONG!!!! puts @prices.length # => 241
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.