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

上一篇文章简单谈了一下在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相似的元数据方法。

参考资料