#50 ✓resolved
Maciej Litwiniuk

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

    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

    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 without group 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

    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

    Ben Johnson February 17th, 2009 @ 11:29 AM

    >> Neighborhood.count(:group => "id")
    => [[641773101, 1], [799328388, 1]]
    
  • Maciej Litwiniuk

    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

    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

    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

    Maciej Litwiniuk February 17th, 2009 @ 01:35 PM

    puts @prices.type # => Array should be of course puts @prices.class # => Array

  • Ben Johnson

    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

    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?

  • Ben Johnson

    Ben Johnson February 18th, 2009 @ 01:47 PM

    I can't, I get an array.

  • Maciej Litwiniuk

    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.

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

Pages