Wrong entries count with group clause

[rohangrg]

Hi @matt-riemer

I am using this wonderful gem for quite a while now, could you please help me in this issue ASAP

the entries count and pagination is completely wrong when group is used in the final query

Eg: Pincode.select("pincodes.city_id").group(:city_id) the count should have been 2000+ in my case but showing only 39

try using group by on any field and see the behaviour of total count, and pagination

Fun fact: If I change per page to All , then i can see all 2000+ records but the entry count is still shows wrong number

[matt-riemer]

Heyo,

I ran a grouped query on one of my projects, and the pagination count worked correctly.

But, the sql group stuff is complex, and not something I use often. It could very well be incorrect.

Question, what's the .size of your collection?

Pincode.select("pincodes.city_id").group(:city_id).size is that 39?

What does Pincode.pluck(:city_id).uniq.length return? Is that 39? Then I'd expect datatables to only display 39 rows.

Here's the code that counts up the size of the collection:

https://github.com/code-and-effect/effective_datatables/blob/master/app/models/effective/datatable_column_tool.rb#L91

and

https://github.com/code-and-effect/effective_datatables/blob/master/app/models/effective/effective_datatable/compute.rb#L10

Is your dataset filtered in any way? Scopes or filters? Could be that going on

Thanks

[rohangrg]

Pincode.select("pincodes.city_id").group(:city_id).size

actually returns a hash, i guess that is what messing up

my exact code is Pincode.left_joins(:city, :state) .group("cities.id, states.id, pincodes.city_id, pincodes.state_id") .select("cities.id, cities.name as city_name, states.name as state_name")

26230 - pincodes 3016 - cities 38 - states

No filters !! No Scope

on calling .size.size on above code getting 39 first size returning hash and second size returning 39

on calling .pluck("cities.id").size getting actual count which is 3016

Thanks

[matt-riemer]

Hmm, I think you need to simplify the table

Could you write something like:

class PincodesDatatable < Effective::Datatable

  datatable do
    col :created_at, visible: false
    col :updated_at, visible: false

    col :id, visible: false

    col :city
    col :state

    actions_col
  end

  collection do
    Pincode.all.includes(:city, :state)
  end

end

If your State and City models have a def to_s; name; end then it'll all work out

[rohangrg]

nope this won't work, because this will show many duplicate entries, to remove duplicate entries i have to use group.

also this is simple example, have used group clause in many complicated queries and they will be difficult to write again.

[rohangrg]

Hi @matt-riemer Any update on this?

Thanks

[danajanssen]

if there's no comment added to the issue, there isn't an update. please don't follow up like this