CODEWITHSUNDEEP
ruby-on-rails-4will-paginate
Mi Ro
Mi Ro

Reputation: 760

will_paginate "breaks" query result

I have a query which shows 3566 results what is ok. When I use paginate on it, result is 18 but in console I see that query which it runs is ok

this is my controller

  def listcontractors
@listcons = Contract.paginate(:page => params[:page], :per_page => 50).joins(:contractor)


    .select("contractors.id,name,ico,city,country,count(resultinfo_id)")
              .group("contractors.id,name,ico,city,country")
              .order("name")
  end

this is query I see in console, when I put it in psql result is ok

(22.2ms) SELECT COUNT(*) AS count_all, contractors.id,name,ico,city,country AS contractors_id_name_ico_city_country FROM "contractors" INNER JOIN "contracts" ON "contracts"."contractor_id" = "contractors"."id" GROUP BY contractors.id,name,ico,city,country Contractor Load (30.8ms) SELECT contractors.id,name,ico,city,country,count(resultinfo_id) as count FROM "contractors" INNER JOIN "contracts" ON "contracts"."contractor_id" = "contractors"."id" GROUP BY contractors.id,name,ico,city,country ORDER BY name LIMIT 50 OFFSET 1050

when I remove .paginate part from the query, result is ok my models are 

    class Contract < ActiveRecord::Base
  belongs_to :resultinfo
  belongs_to :contractor
end

class Contractor < ActiveRecord::Base
    has_many :contracts
end

I tried to switch query to Contractor.joins(:contract) but issue was same, with paginate result is much lower than it should be

any idea why this happens? thanks

Upvotes: 1

Views: 160

Answers (2)

Mi Ro
Mi Ro

Reputation: 760

thanks to gmcnaughton I created this solution

ids = Contractor.order("name").pluck(:id)
 @listcons = ids.paginate(:page => params[:page], :per_page => 50)
 @groupedcons = Contractor.joins(:contracts)
     .where(id: @listcons)
     .select("contractors.id,name,ico,city,country,count(resultinfo_id)")
     .group("contractors.id,name,ico,city,country")
     .order("name")

and I had to add to initializers require 'will_paginate/array' because otherwise it shows undefined total_pages method for an array

Upvotes: 1

gmcnaughton
gmcnaughton

Reputation: 2293

Mixing paginate and group is tricky. paginate sets an OFFSET and LIMIT on the query, which get applied to the result of the GROUP BY -- rather than limiting what records will get grouped.

If you want to paginate through the all Contracts, then group each page of 50 results (one page at a time), try this:

def listcontractors
  # get one page of contract ids
  ids = Contract.paginate(:page => params[:page], :per_page => 50).pluck(:id)

  # group just the items in that page
  @listcons = Contract.where(id: ids)
                      .select("contractors.id,name,ico,city,country,count(resultinfo_id)")
                      .group("contractors.id,name,ico,city,country")
                      .order("name")
end

Hope that helps!

Upvotes: 0

Related Questions