2

[rails 2.3.12] named_scope:

named_scope :order_by_price, lambda {{:joins => :variants, :group => "products.id", :order => "MAX(price)"}}

console:

1.
> Product.order_by_price.size
=> 21

2. 
> p = Product.order_by_price
> p.size
=> 4

sql queries:

1.
SELECT count(*) AS count_all FROM `products` INNER JOIN `variants` ON variants.product_id = products.id

2.    
SELECT `products`.* FROM `products` INNER JOIN `variants` ON variants.product_id = products.id GROUP BY products.id ORDER BY MAX(price)

I use will_paginate for pagination. In this case total_entries value is 21 and number of pages is based on this, although there are only 4 products...

Any ideas how can I get this to work correctly?

EDIT

In general I have to include group_by when calling Product.count... how?

santuxus
  • 3,662
  • 1
  • 29
  • 35
  • how many entries are there in the product table? Have you tried in the dbconsole: SELECT * AS count_all FROM `products` INNER JOIN `variants` ON variants.product_id = products.id – Klaus Jul 22 '11 at 15:43
  • I have just test data now - there are 4 Products, that together have 21 Variants. You cannot do "SELECT * AS sth" - this is just for one column, so * does not work. If I do "SELECT products.id AS count_all FROM products INNER JOIN variants ON variants.product_id = products.id" I still get 21 rows instead of 4... I mean problem here is with group_by... – santuxus Jul 22 '11 at 16:12

1 Answers1

1

No answers, but I found a solution. Maybe it will be useful for someone else too. I just had to redefine count, selecting distinct product_id:

  def self.count(*args)
    super(args, {:select => "(products.id)", :distinct => true})
  end
santuxus
  • 3,662
  • 1
  • 29
  • 35