这篇文章会提出一个相对麻烦的查询场景，在这种场景下可能无法直接套用工具库所提供的分页方法，需要自己去实现数据库分页，笔者会在稍后提供自己的解决方案。

上一篇文章简单谈了一下在Rails中较为简单的分页方式，主要是基于Kaminari分页库，有些朋友建议我使用Pagy，然而最近事情稍微有点多，稍后会尝试一下这个库，故而这篇文章还是基于Kaminari来。

较为麻烦的场景

假设我有如下场景

  1. 三个表结构，产品（products），评估（evaluations），用户（users）
  2. 每个产品都有自己所属的用户。专家用户（只有几个，以白名单的方式实现）可以对其他用户上架的产品进行评估，大家都评估完之后转换产品的状态(pending -> finished)。
  3. 实现列表功能，通过过滤条件划分出指定的专家用户有哪些产品是已经评估过的，那些产品是待评估的。

数据表大概是这个样子，我只做个简单的设计

class CreateUsers < ActiveRecord::Migration[6.0]
  def change
    create_table :users do |t|
      t.string :nickname

      t.timestamps
    end
  end
end

class CreateEvaluations < ActiveRecord::Migration[6.0]
  def change
    create_table :evaluations do |t|
      t.references :product, null: false, foreign_key: true
      t.references :user, null: false, foreign_key: true
      t.string :intro
      t.decimal :price

      t.timestamps
    end
  end
end

class CreateProducts < ActiveRecord::Migration[6.0]
  def change
    create_table :products do |t|
      t.string :title
      t.integer :status, default: 0
      t.decimal :original_price
      t.references :user, null: false, foreign_key: true

      t.timestamps
    end
  end
end

这里产品有个状态字段status，所包含的状态有pending（待评估），finished（多个专家评估会自动完成），rejected（不符合规定直接拒绝）。如果产品的状态不是待评估（pending）或者是产品已经包含指定用户的评估记录，那就代表产品已经被处理过了，被归为已处理列表，剩下的产品都被认为是待处理的。

反过来想可能简单一些，简要的查询逻辑大概是

  1. 指定一个专家用户的id，查询出产品并联表查询出它所关联的评估记录，检索出出这个用户没有评估过的所有产品（没有对应的评估记录），还要追加一个附加条件，这些产品必须都是待处理（pending）状态的。满足上面条件的都是需要当前用户处理的产品。
  2. 1中得到结果的补集其实就是不需要用户处理的产品，称为历史记录。

原生的SQL语句

要实现上述第一点，所需要编写的SQL语句有点像是这样子

class Product < ApplicationRecord
  class<<self
    def need_handle_string(user)
      %(
        SELECT products.id
        FROM products LEFT JOIN evaluations
        ON evaluations.product_id = products.id
        WHERE  products.status = 0
        GROUP BY products.id
        HAVING NOT array_agg(evaluations.user_id) @> '{#{user.id}}'
      )
    end
  end
end

user指的是当前用户，我们需要它的id，然后对产品products记录所对应的所有评估记录evaluations中的user_id字段用array_agg进行聚合。它会聚合成一个user_id数组，类似于{1,15,33}。接下来用PG的@>方法检测数组里面是否包含当前用户的user_id。如果包含，则证明用户已经对该产品用做过评估，就认为他已经处理过了。否则的话就表示还没处理，所以我这里用的是HAVING NOT，检索出用户还没处理过的记录。

另外，还要一个附加条件，那就是商品必须是待处理（pending）状态的，在数据库里面它用整数0来取代，故而会有一个判断语句WHERE products.status = 0。

这堆SQL语句如果要用Rails的方法来组装估计会是

Product.joins('LEFT JOIN XXXX').where('....').having('.....')

.....似乎还是原生SQL比较简单一些，然而上面这个方法只不过是SQL语句的组装。要想执行还需要把得到的字符串放到Production.find_by_sql方法里面。只不过上面的查询语句只搜索出了id。

> Product.find_by_sql(Product.need_handle_string(User.first))
=> [#<Product id: 1>, #<Product id: 2>.....

并不利于后续操作，要想得到更全面的数据，可以用子查询的形式做进一步的封装。再弄个方法，对need_handle_string得到的字符串进行包裹

class Product < ApplicationRecord
  class<<self
    # ...
    def wrapper_by_subquery(subquery)
      %(SELECT * FROM products WHERE id IN (#{subquery}) ORDER BY updated_at DESC)
    end
  end
end

大概是这样

> query_string = Product.wrapper_by_subquery(Product.need_handle_string(User.first))

> Product.find_by_sql(query_string)
=> [#<Product id: 10, title: "Product9", status: 0, original_price: 0.1e3, user_id: 13, created_at: "2021-04-06 01:53:42", updated_at: "2021-04-06 01:53:42">, #<Product id...

如此，就能得到一个数据比较全面的Product对象数组，查找出所有待处理的产品。

利用补集，查询出剩下的记录

由于查询待处理产品的查询语句比较复杂，条件也比较多，如果要针对已处理的记录再写一套逻辑出错的几率会比较大，代码也不好维护。要解决这种问题最好的做法还是能够获取未处理记录的补集合，其实所有记录减去待处理记录，剩下的就是历史记录了。

PG提供了简单的EXPECT关键字来实现这一点，所以两个集合各自的查询字符串如下

> unhandled_query_string_just_ids = Product.need_handle_string(user)
=> ...

> handled_query_string_just_ids = "SELECT products.id FROM products EXCEPT #{Product.need_handle_string(User.first)}"
=> "SELECT products.id FROM products EXCEPT \n    SELECT products.id\n    FROM products LEFT JOIN evaluations\n    ON evaluations.product_id = products.id\n    WHERE  products.status = 0\n    GROUP BY products.id\n    HAVING NOT array_agg(evaluations.user_id) @> '{2}'\n  "

同样，上面两个查询语句都只是查询出了id，要想获得更全面的数据还需要把他们以子查询的形式，用Product.wrapper_by_subquery方法包裹，再进一步执行。

数据库分页

SQL语句写得像上面这么复杂就很难直接利用Kaminari提供的工具方法去分页了。

我们目前的SQL语句都是从数据库中拿出所有符合条件的数据，所以我们也可以像上个篇章说的那样用Kaminari.paginate_array去进行分页。然而这样的话，在数据量比较大的时候还是会扛不住。

在这种场景下，只好自己去实现数据库分页了。

  1. 简单改装一下Product.wrapper_by_subquery让它可以接收分页参数。
  2. 查询结果向外暴露出current_page, total_count, total_pages这些跟Kaminari类似的元数据方法，在某种程度上可以模拟Kaminari的行为。

直接贴代码好了

class Product < ApplicationRecord
  class<<self
    def need_handle_string(user)
      %(
        SELECT products.id
        FROM products LEFT JOIN evaluations
        ON evaluations.product_id = products.id
        WHERE  products.status = 0
        GROUP BY products.id
        HAVING NOT array_agg(evaluations.user_id) @> '{#{user.id}}'
      )
    end

    def wrapper_by_subquery(subquery)
      %(SELECT * FROM products WHERE id IN (#{subquery}) ORDER BY updated_at DESC LIMIT :limit OFFSET :offset)
    end

    def wrapper_count_by_subquery(subquery)
      %(SELECT COUNT(*) FROM products WHERE id IN (#{subquery}))
    end

    def add_pagination_methods(page, per_page, condition)
      offset = (page - 1) * per_page

      query_string = wrapper_by_subquery(condition)
      report_string = wrapper_count_by_subquery(condition)
      results = find_by_sql([
                              query_string,
                              {
                                limit: per_page,
                                offset: offset
                              }
                            ])

      results
        .instance_variable_set(:@per_page, per_page)
      results
        .instance_variable_set(:@page, page)
      results
        .instance_variable_set(:@report_string, report_string)

      results.instance_eval do
        def current_page
          @page
        end

        def total_count
          @total_count ||= Product.find_by_sql(@report_string).first.count
        end

        def total_pages
          @total_pages ||= (total_count * 1.0 / @per_page).ceil.to_i
        end
      end
      results
    end

    def unhandled(user, page: 1, per_page: default_per_page)
      condition = need_handle_string(user)
      add_pagination_methods(page, per_page, condition)
    end

    def handled(user, page: 1, per_page: default_per_page)
      condition = "SELECT products.id FROM products EXCEPT #{need_handle_string(user)}"
      add_pagination_methods(page, per_page, condition)
    end
  end
end

简单演示一下结果

> m = User.first
> results = Product.unhandled(m, page: 2, per_page: 1)
=> [#<Product id: 10, title: "Product9", status: 0, original_price: 0.1e3, user_id: 13, created_at: "2021-04-06 01:53:42", updated_at: "2021-04-06 01:53:42">, #<Product id: 9, title: "Product8", status: 0, original_price: 0.1e3, user_id: 12, created_at: "2021-04-06 01:53:42"....

> results.total_pages
=> 10
> results.total_count # 总共10个商品未评估
=> 10
> results.current_page
=> 2

> results = Product.handled(m, page: 2, per_page: 1)
> results.total_count # 当前用户处理过的商品为0
=> 0

再来，简单评估一下

> m = User.first
> e = Evaluation.create(product_id: Product.first.id, user_id: m.id, intro: '简单评估', price: 1000) # 随便评估一个商品

> results = Product.unhandled(m)
> results.total_count # 总共9个商品未评估
=> 9

> results = Product.handled(m)
> results.total_count # 当前处理过的商品为1
=> 1

总结

这篇文章首先提出一种稍微复杂的查询场景，在这种场景下，Rails提供的查询方法已经无法轻易地组装查询逻辑，这个时候用原生的SQL语句反而简单许多。

然而这会遇到另一个问题，原生SQL语句查询出来的资源数组，并无法直接套用Kaminari提供的分页方法，除非使用Kaminari.paginate_array。然而面对数据量较大的场景这种方式就无法满足性能要求，因此需要自己去实现数据库的分页。在本文中我给出了自己的封装，并且为了满足代码的一致性，我还提供了跟Kaminari相似的元数据方法。

参考资料