Count seems wrong in belongs_to filters if multiple habtm relations are set and one is selected

[arnaudlevy]

class Product < ApplicationRecord
  belongs_to :line
  has_and_belongs_to_many :categories
end

If a product is in 3 categories, and 1 of the categories is selected, it counts as 3 occurrences in the line filter.

The problem comes from this query

SELECT DISTINCT products.line_id AS foreign_id, count(products.line_id) AS occurrences 
FROM "products" 
INNER JOIN "categories_products" ON "categories_products"."product_id" = "products"."id" 
INNER JOIN "categories" ON "categories"."id" = "categories_products"."category_id" 
INNER JOIN "lines" ON "lines"."id" = "products"."line_id" 
WHERE "products"."id" IN (
	SELECT products.id 
	FROM "products" 
	INNER JOIN "categories_products" ON "categories_products"."product_id" = "products"."id" 
	INNER JOIN "categories" ON "categories"."id" = "categories_products"."category_id") 
GROUP BY products.line_id

which count the products.line_id.
In this result set, a product in 3 categories does 3 lines, resulting in 3 occurrences.
Hope it's clear!

[axelerator]

I'll try to look into it the next days

[paulwalk]

Just for the record, I am seeing this issue - with just one habtm relation. The counts are shown correctly for the habtm filter, but not for the other belongs_to filters. The count that is shown in the other belongs_to filters is (in total) the number of times the main model appears in the join table used in the habtm relation.

I have spent a few hours trying to debug this but I have not been able to figure it out.