How to select data for defined page and total count of records?

Question

I have a table with paginated data and this is the way I select data for each page:

@visitors = EventsVisitor
      .select('visitors.*, events_visitors.checked_in, events_visitors.checkin_date, events_visitors.source, events_visitors.id AS ticket_id')
      .joins(:visitor)
      .order(order)
      .where(:event_id => params[:event_id])
      .where(filter_search)
      .where(mode)
      .limit(limit)
      .offset(offset)

Also to build table pagination I need to know total count of records. Currently my solution for this is very rough:

total = EventsVisitor
      .select('count(*) as count, events_visitors.*')
      .joins(:visitor)
      .order(order)
      .where(:event_id => params[:event_id])
      .where(filter_search)
      .where(mode)
      .first()
      .count

So my question is as follows - What is the optimal ruby way to select limited data for the current page and total count of records?

I noticed that if I do @visitors.count - additional sql query will be generated:

SELECT COUNT(count_column) FROM (SELECT 1 AS count_column FROM `events_visitors` INNER JOIN `visitors` ON `visitors`.`id` = `events_visitors`.`visitor_id` WHERE `events_visitors`.`event_id` = 1 LIMIT 15 OFFSET 0) subquery_for_count

First of all, I do not understand what is the reason to send an additional query to get a count of data that we already have, I mean that after we got data from database in @visitors we can count it with ruby without need to send additional queries to DB.

Second - I thought that maybe there are some ways to use something like .total_count that will generate similar count(*) query but without that useless limit/offset?

You don’t have to do another query to get the count of your query. See [`size` vs. `length` vs. `count`](http://stackoverflow.com/questions/6083219/activerecord-size-vs-count). — Andrew Marshall, Jan 09 '13 at 03:20
visitors.count return count of limited portion of data, i can't use it or size or length to get total count of records — SET001, Jan 09 '13 at 03:25
@Andrew Marshall - i understand, this is about why 2nd query is sent when calling count() — SET001, Jan 09 '13 at 03:29

Fivell · Accepted Answer · 2017-09-21T14:15:48.267

15

you should except limit and offset http://guides.rubyonrails.org/active_record_querying.html#except . See how kaminari does it https://github.com/kaminari/kaminari/blob/92052eedf047d65df71cc0021a9df9df1e2fc36e/lib/kaminari/models/active_record_relation_methods.rb#L11

So it might be something like

total = @visitors.except(:offset, :limit, :order).count

edited Sep 21 '17 at 14:15

answered Jan 09 '13 at 07:08

Fivell

11,829
3
61
99

2

ruby is amazing – Mani Oct 29 '16 at 18:53

How to select data for defined page and total count of records?

1 Answers1