#201 ✓invalid
James Zwiers

Finder_sql and will_paginate cause sql truncation

Reported by James Zwiers | March 11th, 2008 @ 08:47 PM


So, I've been working on a rebuild of a complex query (its a friendship style relationship between user accounts). Formerly it was stored using two tables (in addition to the user table), with three records total to represent a single relationship (yes, pretty horrible, and it is having a huge negative impact on our performance due to table bloat).

So, my solution was a single table that would store a single record for each relationship; however, this means that the default behavior in rails (of using a single foreign key in a has_many relationship) wasn't going to work. So, my finder actually looks like:

  has_many :friends, :class_name => "User", :finder_sql => 
   SELECT users.*, friends.position_one AS position FROM friends
    LEFT OUTER JOIN users ON users.id = friends.person_two_id
    WHERE friends.person_one_id = #{id} 
   SELECT users.*, friends.position_two AS position FROM friends
    LEFT OUTER JOIN users ON users.id = friends.person_one_id
    WHERE friends.person_two_id = #{id}
   ORDER BY friends.position;

So, now onto the problem (well, it is possible that I'm just missing something and there is a way to do that type of relationship in Rails, but if there is then I'm either blind or its horribly obscure).

Whenever I try to call paginate on that function I get an error (see below), where the error is caused by the finder_sql being truncated to 128 characters.

The error log is located here

Any help is appreciated. I also realize that this may not even be a will_paginate bug, but something related to an issue within Rails or Ruby itself; however, I figured that starting with the plugins first would be the best place :)

Cheers and thanks,


Comments and changes to this ticket

  • James Zwiers

    James Zwiers March 20th, 2008 @ 09:57 PM

    Okay, so I was just blind that day. Feel free to ignore this, its not actually truncating. Although, it does look like its not happy with multiple SQL statements inside a finder_sql.

  • Mislav

    Mislav March 30th, 2008 @ 01:48 PM

    • State changed from “new” to “invalid”
    • Assigned user changed from “Chris Wanstrath” to “Mislav”

    Definitely not a will_paginate bug, plus the fact you can't even use paginate on associations with finder_sql.

    This hardcore database stuff should be entirely manual. However, after you got the results you could encapsulate them in WillPaginate::Collection that you can render in the view with will_paginate helper.

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.

New-ticket 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!

People watching this ticket