Using the paginated method on models we can easily paginate through a query that might otherwise return many results. The arguments are the same as the select method but instead of the result it returns a specialPaginator object.


For example, say we have the following table and model: (See Database Schemas for more information on creating tables.)

例如,下面的数据表定义:(Database Schemas那章有更多关于表创建的细节内容。)

create_table("users", {
  { "id", types.serial },
  { "name", types.varchar },
  { "group_id", types.foreign_key },


local Users = Model:extend("users")

We can create a paginator like so:


local paginated = Users:paginated("where group_id = ? order by name asc", 123)
A paginator can be configured by passing a table as the last argument. The following options are supported: per_page: sets the number of items per page

可以在最后一个参数传个lua table定义来配置分页器,下面的这个设置:per_page:设置每页显示项目的条数。

local paginated2 = Users:paginated("where group_id = ?", 4, { per_page = 100 })
prepare_results: a function that is passed the results of get_page and get_all for processing before they are returned. This is useful for bundling preloading information into the paginator. The prepare function takes 1 argument, the results, and it must return the results after they have been processed:

prepare_results:此函数在把结果传给get_page和get_all处理之前就返回了。这个为分页器提前绑定信息很有用。预处理函数有一个参数,结果, 在返回结果之后就已对处理完了。

local preloaded = Posts:paginated("where category = ?", "cats", {
  per_page = 10,
  prepare_results = function(posts)
    Users:include_in(posts, "user_id")
    return posts
Any additional options sent to paginated are passed directly to the underlying select method call when a page is loaded. For example you can provide a fields option in order to limit the fields returned by a page.

在页面被加载后,任何附加参数被传给分页器都直接被select底层方法调用, 例如你可提供一个fields 选项,来限制每页返回的字段数。

Whenever possible you should specify an ORDER clause in your paginated query, as the database might returned unexpected results for each page. The paginator has the following methods:

可能你在查询中指定了一个ORDER语句, 数据库的分页数据可能返回了非预期的结果,分页器有下面的这些方法。


Gets all the items that the query can return, is the same as calling the select method directly. Returns an array table of model instances.


local users = paginated:get_all()
SELECT * from "users" where group_id = 123 order by name asc
Gets page_numth page, where pages are 1 indexed. The number of items per page is controlled by theper_page option, and defaults to 10. Returns an array table of model instances.

取得提定的page_num页, 页数从1索引 ,每页多少项是通过per_page这个参数来控制的,默认是10, Model返回的是lua的数组table。

local page1 = paginated:get_page(1)
local page6 = paginated:get_page(6)
SELECT * from "users" where group_id = 123 order by name asc limit 10 offset 0
SELECT * from "users" where group_id = 123 order by name asc limit 10 offset 50


Returns the total number of pages.



Gets the total number of items that can be returned. The paginator will parse the query and remove all clauses except for the WHERE when issuing a COUNT.


local users = paginated:total_items()
SELECT COUNT(*) as c from "users" where group_id = 123
Returns an iterator function that can be used to iterate through each page of the results. Useful for processing a large query without having the entire result set loaded in memory at once.


for page_results, page_num in paginated:each_page() do
  print(page_results, page_num)
Be careful modifying rows when iterating over each page, as your modifications might change the pagination order and you may process rows multiple times or none at all.



Checks to see if the paginator returns at least 1 item. Returns a boolean. This is more efficient than counting the items and checking for a number greater than 0 because the query generated by this function doesn’t do any counting.


if pager:has_items() then
  -- ...
SELECT 1 FROM "users" where group_id = 123 limit 1



如果不小心安装错 SQL Server 为 Evaluation 的版本,要小心当超过 180 天之后,系统就会无法正常使用了 这几天遇到一个蛮特别的案例,原本收到的问题是 “维护计划” 忽然无法使用,即便是里面没有任何的Task,都无法顺利地执行。但从对方所提供的错误消...… Continue reading

PLUM NIZ静电容键盘怎么样?

Published on September 25, 2020


Published on September 18, 2020