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?

Answer 1

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