ransack
ransack copied to clipboard
activerecord-hackery
searching on has_many associations returns duplicate records
class User < ApplicationRecord
has_many :books
end
class Book < ApplicationRecord
belongs_to :user
end
> User.ransack({books_kind_eq: "novel"}).result.to_sql
"SELECT \"users\".* from \"users\" LEFT OUTER JOIN \"books\" on \"books\".\"user_id\" = \"users\".\"id\" WHERE \"books\".\"kind\" = 'novel'"
result(distinct: true) could resolve this problem, but I want to do this dynamically.
it 'issue #1050 example (add `distinct` on has_many associations search dynamically)' do
s1 = Search.new(Person, name_eq: "foo", articles_title_eq: "")
expect(s1.result.to_sql).to eq("SELECT \"people\".* FROM \"people\" WHERE \"people\".\"name\" = 'foo' ORDER BY \"people\".\"id\" DESC")
# failure
s2 = Search.new(Person, name_eq: "foo", articles_title_eq: "bar")
expect(s2.result.to_sql).to eq("SELECT DISTINCT \"people\".* FROM \"people\" LEFT OUTER JOIN \"articles\" ON \"articles\".\"person_id\" = \"people\".\"id\" AND ('default_scope' = 'default_scope') WHERE (\"people\".\"name\" = 'foo' AND \"articles\".\"title\" = 'bar') ORDER BY \"people\".\"id\" DESC")
end
I'm surprised this issue isn't getting more traction because when using JOIN with has_many, duplicates can only be avoided by resorting to DISTINCT ON which isn't just inconvenient, it can be a performance killer depending on the RDBMS and the specifics of the query.
A better approach would be to use an EXISTS sub-query as described here. Has this idea been explored at all?
