CODEWITHSUNDEEP
ruby-on-rails-3postgresqlactiverecord
chuck w
chuck w

Reputation: 1761

Returning a count of found_rows along with a limited active records query results

Please excuse the noobee nature of this question.

I have a Story model. Each story has a category attribute stored in its own DB column. I have a named scope called :in_categories, which returns all of the stories that have a category that is included in a given category array:

  scope :in_categories, lambda {|categories|
        where(:category => categories)
  }

What I now want to do is limit the length of the response, but also return a count of the total number of respondent rows in the DB, ignoring the limit. For instance, say I changed the scope to be:

  scope :in_categories, lambda {|categories|
        where(:category => categories).limit(20)
  }

but there are actually 35 stories that have a category in the categories array. How can I get back the 20 active records objects and a count variable of 35? Do I need to have a second DB query? I've come across the FOUND_ROWS_ function in mysql, but I'm using postgresql.

Upvotes: 1

Views: 315

Answers (1)

Alexeyss
Alexeyss

Reputation: 3246

take a look at http://www.postgresql.org/message-id/[email protected]

There is no equivalent. Use

BEGIN;
SELECT * FROM mytable OFFSET X LIMIT Y;
SELECT COUNT(*) AS total FROM mytable;
END;

(To ensure consistent results, both queries should be done in a single transaction.)

don't have rails env on hand therefore only pseudocode

Story.transaction do
  story.in_categories
  story.where(:category => categories).count
end

more info about active record transactions http://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html

Actually we don't use transactions and simply execute these queries sequentially. Never had problems with that but of course it depends on your project.

Upvotes: 1

Related Questions