Problem with distinct table_name.*
Reported by gobigdave | April 9th, 2008 @ 05:02 PM
This problem is not limited to acts_as_taggable, but it was through it that I found it. In general, everything works great between acts_as_taggable and will_paginate. However, if you do something like:
tag_options = Referral.find_options_for_find_tagged_with(@selected_tags, :match_all => true)
@referrals = @current_user.referrals.paginate_viewable(:all, tag_options.merge(:page => 1))
The :match_all => true option causes acts_as_taggable to add :select => "distinct referrals.*" to tag_options, and that upsets the wp_count method in finder.rb. Mysql really doesn't like distinct * in counts.
To fix this, I add the following to the top of wp_count:
options[:select].sub!(/([\s\w]*)./, "\\1.id") if options[] # count by id's, not wildcard ()
I have a couple of other queries that use :select => "distinct table_name.*" in their finder options, and they cause the same paginate issue.
I'm hoping to make the switch over to the gem, but until this is taken care of, I can't.
Comments and changes to this ticket
-
Mislav April 9th, 2008 @ 06:34 PM
- State changed from “new” to “open”
Will paginate let's you specify a modified :select part specifically for the count query:
Referral.paginate :page => 1, :count => { :select => 'distinct referrals.id' }This is just an example how you can use the :count param to override any part of the count query. Does that help?
Meanwhile, I'll think about if this can be automated so that users don't have to think about this. But I will try not to add too much magic to advanced stuff as that may frustrate users who didn't want magic.
-
gobigdave April 9th, 2008 @ 07:40 PM
That helps a lot, and I can update my code to not rely on my patched version. However, I'm thinking that doing a :select => "distinct table_name.*" is not uncommon when there are joins and such involved. I completely agree that keeping too much magic out is a good thing, but in this case it can get you in trouble without it. A quick look around Google finds several pages describing a similar issue.
Thanks again for the great plugin. I especially like the new paginate_each method. I have several places where I process everything in a table (100's of thousands) I had custom code to do this, but leveraging will_paginate makes life a lot easier.
-
Olek Poplavsky June 11th, 2008 @ 06:56 PM
I had same exact problem for the last half a year, worked around with some ugly monkey patching... Thank you for the clean solution, even if it is not automatic :)
-
Mislav September 13th, 2008 @ 02:08 AM
- State changed from “open” to “resolved”
- Tag set to “count, sql, will_paginate”
Solved in 5adc2de1b9bd2bc90a3852de3b0dff90bb44d58f
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.
Create new ticket
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป
Everyone's favorite Ruby library for pagination of practically anything!
gobigdave
Mislav