has_many association with uniq #251

Closed
bobanj opened this Issue Jul 9, 2012 · 6 comments

Comments

Projects
None yet
4 participants

bobanj commented Jul 9, 2012

Rails 3 app (PostgreSQL)
The association
has_many :voted_users, :through => :votes, :uniq => true

In console:
I have 2 records associatied:
user.voted_users.count #=> 2

when I use paginate on this:

wp = user.voted_users.paginate :per_page => 1, :page => 1 #so I should get 2 pages
wp.total_pages # => 6

The SQL:

SELECT DISTINCT "users".* FROM "users" INNER JOIN "user_tags" ON "users"."id" = "user_tags"."user_id" INNER JOIN "votes" ON "user_tags"."id" = "votes"."voteable_id" WHERE "votes"."voter_id" = 66 LIMIT 1 OFFSET 0

SELECT DISTINCT COUNT(*) FROM "users" INNER JOIN "user_tags" ON "users"."id" = "user_tags"."user_id" INNER JOIN "votes" ON "user_tags"."id" = "votes"."voteable_id" WHERE "votes"."voter_id" = 66

As you can see the prob is with DISTINCT COUNT

Did you ever determine a solution to this? I am running into the same issue.

bobanj commented Oct 18, 2012

you can recalculate and set pagination_obj instance variable total_pages, but it will cost you an extra query, as I can remember we solved this by writing the sql instead of generating it :)

Hello,

This is related to a Rails issue that got fixed in master and that will most probably be fixed in Rails 4. There's tons of issues regarding this problem in the Rails repo :
rails/rails#1003
rails/rails#5554
rails/rails#2924

To name a few ...

Using Rails 3.2.12 I can indeed reproduce the problem :

p.comments.where('content like ?', "%bla%").count
#Generates => SELECT DISTINCT COUNT(*) FROM `comments` WHERE `comments`.`post_id` = 1 AND (content like '%bla%')

Using edge rails now :

p.comments.where('content like ?', "%bla%").count
#Generates => SELECT DISTINCT COUNT(DISTINCT `comments`.`id`) FROM `comments` WHERE `comments`.`post_id` = 1 AND (content like '%bla%')

I don't think they'll be back-porting this to Rails 3, so we'd better close this issue :)

Owner

mislav commented Feb 18, 2013

@anthonyalberto Thanks for researching this. They say it's backported to 3.2-stable. Is that true?

@mislav oh, didn't notice that!

It's actually fixed in 3-2-stable, but it's not included in Rails 3.2.12

Rails 3.2.12 :

pry(main)> p.links.where(comment_id: 1).count
   (0.9ms)  SELECT DISTINCT COUNT(*) FROM `links` INNER JOIN `comments` ON `links`.`comment_id` = `comments`.`id` WHERE `comments`.`post_id` = 1 AND `links`.`comment_id` = 1

Rails 3-2-stable :

pry(main)> p.links.where(comment_id: 1).count
   (0.7ms)  SELECT DISTINCT COUNT(DISTINCT `links`.`id`) FROM `links` INNER JOIN `comments` ON `links`.`comment_id` = `comments`.`id` WHERE `comments`.`post_id` = 1 AND `links`.`comment_id` = 1
Owner

mislav commented Feb 19, 2013

Closing due to this being an AR bug that is fixed in 4.0 and future versions of 3.2

mislav closed this Feb 19, 2013

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment