will_paginate breaks when tally or plusminus_tally is used

[f3ndot]

When using tally or plusminus_tally is used in a ActiveRelation it breaks the will_paginate page(params[:page]) or paginate(:page => params[:page]) methods.

I'm not sure who's at fault but I believe thumbs_up may be at fault the way the SQL is structured. If I run a regular relation asking for the count of records returned:

> Violation.without_spammed.order("created_at DESC").page(1).count
   (0.4ms)  SELECT COUNT(*) FROM "violations" WHERE "violations"."spammed" = 'f'
=> 4

And if I use the plusminus_tally in the line:

> Violation.without_spammed.plusminus_tally.reorder("created_at DESC").page(1).count
   (0.5ms)  SELECT COUNT(*) AS count_all, violations.id, violations.title, violations.description, violations.address, violations.violator_id, violations.created_at, violations.updated_at, violations.user_id, violations.slug, violations.flagged, violations.user_ip, violations.user_agent, violations.referrer, violations.spammed AS violations_id_violations_title_violations_description_violations_address_violations_violator_id_violations_created_at_violations_updated_at_violations_user_id_violations_slug_violations_flagged_violations_user_ip_violations_user_agent_violations_referrer_ FROM "violations" LEFT OUTER JOIN votes ON violations.id = votes.voteable_id AND votes.voteable_type = 'Violation' WHERE "violations"."spammed" = 'f' GROUP BY violations.id, violations.title, violations.description, violations.address, violations.violator_id, violations.created_at, violations.updated_at, violations.user_id, violations.slug, violations.flagged, violations.user_ip, violations.user_agent, violations.referrer, violations.spammed
=> {false=>1}

It appears to break what is expected: A result of 4. Please advise!

Environment: Rails 3.2.11 thumbs_up 0.6.3 will_paginate 3.0.4

[bouchard]

Hey Justin,

Not sure what's going on, thumbs_up does use some complicated queries so that could very well be at fault. An easy workaround would be to paginate the objects first, then filter a plusminus_tally query based on that object array. Another suggestion would be to see what the query is without the #count method on the end... it'll give us a better idea of what's invalid about the SQL.

Braden (Brady) Bouchard [email protected] the well inspired.

On Monday, 4 February, 2013 at 8:51 AM, Justin Bull wrote:

When using tally or plusminus_tally is used in a ActiveRelation it breaks the will_paginate page(params[:page]) or paginate(:page => params[:page]) methods. I'm not sure who's at fault but I believe thumbs_up may be at fault the way the SQL is structured. If I run a regular relation asking for the count of records returned:

Violation.without_spammed.order("created_at DESC").page(1).count (0.4ms) SELECT COUNT(*) FROM "violations" WHERE "violations"."spammed" = 'f' => 4

And if I use the plusminus_tally in the line:

Violation.without_spammed.plusminus_tally.reorder("created_at DESC").page(1).count (0.5ms) SELECT COUNT(*) AS count_all, violations.id, violations.title, violations.description, violations.address, violations.violator_id, violations.created_at, violations.updated_at, violations.user_id, violations.slug, violations.flagged, violations.user_ip, violations.user_agent, violations.referrer, violations.spammed AS violations_id_v iolations_title_violations_description_violations_address_violations_violator_id_violations_created_at_violations_updated_at_violations_user_id_violations_slug_violations_flagged_violations_user_ip_violations_user_agent_violations_referrer_ FROM "violations" LEFT OUTER JOIN votes ON violations.id = votes.voteable_id AND votes.voteable_type = 'Violation' WHERE "violations"."spammed" = 'f' GROUP BY violations.id, violations.title, violations.description, violations.address, violations.violator_id, violations.created_at, violations.updated_at, violations.user_id, violations.slug, violations.flagged, violations.user_ip, violations.user_agent, violations.referrer, violations.spammed => {false=>1}

It appears to break what is expected: A result of 4. Please advise! Environment: Rails 3.2.11 thumbs_up 0.6.3 will_paginate 3.0.4

— Reply to this email directly or view it on GitHub (https://github.com/bouchard/thumbs_up/issues/64).

[f3ndot]

Hi Brady,

Here is a regular query:

irb(main):001:0> Violation.without_spammed.order("created_at DESC").page(1)
  Violation Load (0.4ms)  SELECT "violations".* FROM "violations" WHERE "violations"."spammed" = 'f' ORDER BY created_at DESC LIMIT 10 OFFSET 0
=> [A bunch of Violation objects]

Here is the query with thumbs_up:

irb(main):002:0> Violation.without_spammed.plusminus_tally.reorder("created_at DESC").page(1)
  Violation Load (0.6ms)  SELECT violations.*, SUM(CASE votes.vote WHEN 't' THEN 1 WHEN 'f' THEN -1 ELSE 0 END) AS plusminus_tally, COUNT(votes.id) AS vote_count FROM "violations" LEFT OUTER JOIN votes ON violations.id = votes.voteable_id AND votes.voteable_type = 'Violation' WHERE "violations"."spammed" = 'f' GROUP BY violations.id, violations.title, violations.description, violations.address, violations.violator_id, violations.created_at, violations.updated_at, violations.user_id, violations.slug, violations.flagged, violations.user_ip, violations.user_agent, violations.referrer, violations.spammed ORDER BY created_at DESC LIMIT 10 OFFSET 0
=> [A bunch of Violation objects]

It looks like the GROUP BY part of the query is causing the COUNT() to behave differently.

[bouchard]

Feel free to take a stab at fixing this query, I'm happy to take pull requests if we can make it work with will_paginate, but unfortunately I won't be able to work on it myself.

[f3ndot]

It would appear that you'd require large restructuring of your db schema (using a junction table) in order to fix this issue. It appears neither of us have the time to fix this issue :)

[eric-sal]

With will_paginate v3.0.pre2, I was also getting an exception when using it with thumbs_up: BlogPost.plusminus_tally.paginate(:per_page => 10, :page => 1)

I was able to solve the issue by recreating the plusminus_tally query as a scope specific to my model.

class BlogPost < ActiveRecord::Base
  scope :by_score, joins("LEFT OUTER JOIN votes ON blog_posts.id = votes.voteable_id AND votes.voteable_type = 'BlogPost'").
                   group('blog_posts.id').
                   order('SUM(CASE user_votes.vote WHEN true THEN 1 WHEN false THEN -1 ELSE 0 END) DESC')
end

Rather than ordering on the calculated score in the select statement aliased as plusminus_tally, I execute the sum in the order clause directly.

Using the scope, I could paginate my blog posts based on the thumbs_up plusminus tally with: BlogPost.by_score.paginate(:per_page => 10, :page => 1).

[techpeace]

Thanks a ton, @whtt-eric! That solution worked for me. :+1:

[romaind]

FYI, it doesn't work either with Kaminari. And @whtt-eric solves the problem too. Thanks ! :+1:

[pangolingo]

I'm seeing a similar issue. Even something simple like Topic.plusminus_tally.pluck(:id) throws an error Mysql2::Error: Unknown column 'plusminus_tally' in 'order clause':...

I'll have to try @whtt-eric's solution. If it works, we may want to factor the plusminus_tally method.

@bouchard are you still actively developing this gem?

[bouchard]

Happy to take pull requests if you want to fix it!