Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

Problem with distinct table_name.*

#205

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.

Reported by gobigdave · April 9th, 2008 @ 05:02 PM

State: resolved
Milestone: none
Assigned to: Mislav Mislav

Activity

  1. Mislav
    Mislav
    • 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.

    April 9th, 2008 @ 06:34 PM

  2. gobigdave
    gobigdave

    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.

    April 9th, 2008 @ 07:40 PM

  3. Olek Poplavsky
    Olek Poplavsky

    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 :)

    June 11th, 2008 @ 06:56 PM

  4. Mislav
    Mislav
    • State changed from open to resolved
    • Tag set to count, sql, will_paginate

    Solved in 5adc2de1b9bd2bc90a3852de3b0dff90bb44d58f

    September 13th, 2008 @ 02:08 AM

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.