Using ActiveRecord to query count() over()-CodePudding

I want to limit the SQL query result set while also knowing how many possible rows match. I found that (with Postgresql), I could write

SELECT *,count(*) over() as total FROM ... WHERE ... LIMIT 5

which will return a max of 5 rows, with a column called total which contains the total number of rows which match the query (it's the same value for all rows); this, to avoid multiple queries.

How can represent this as an ActiveRecord query? While I can pass the select clause to .select, but the "count(*) over()" field is ignored. I had to resort to exec_query and specify then entire select string.

CodePudding user response：

To achieve this with ActiveRecord, you can use the .count method on the queried model to get the total number of matching rows, and then use the .limit method to limit the returned result set. For example:

Model.where(...)
    .limit(5)
    .select("*", "count(*) as total")

This will return a maximum of 5 rows, with an additional column called "total" that contains the total number of matching rows. Note that this will not work with the .all method, as it does not accept a limit or select clause.

Alternatively, you can use the .find_by_sql method and pass in the entire SQL query string, including the count and limit clauses:

Model.find_by_sql("SELECT *, count(*) over() as total FROM ... WHERE ... LIMIT 5")

This will return the same result set as the first example, but using a raw SQL query instead of ActiveRecord methods.