DBIx::Class是否支持SQL窗口函数lag与lead？

你完全不用局限于混合原生SQL，DBIx::Class其实支持窗口函数，同时也有其他更适配的方案，分场景给你列几个：

1. 用DBIx::Class原生语法构造窗口函数（推荐批量场景） #

DBIx::Class允许在select子句中直接定义窗口函数，不需要写全量原生SQL。比如你要按group_id分组、created_at排序，给每条记录带上上下条的id：

# 在你的结果集类（比如 My::Schema::Result::MyTable）中定义方法
sub with_prev_next {
    my $self = shift;
    return $self->search(undef, {
        select => [
            'me.*',
            # 上一条记录的id
            { lag => 'id', -over => { 
                partition_by => 'group_id', 
                order_by => 'created_at' 
            } } => 'prev_id',
            # 下一条记录的id
            { lead => 'id', -over => { 
                partition_by => 'group_id', 
                order_by => 'created_at' 
            } } => 'next_id',
        ],
        # 如果me.*包含所有字段，这里可以省略as，否则要对应所有字段名
        # as => [qw/id title group_id created_at prev_id next_id/],
    });
}

调用的时候直接用：

my $rs = $schema->resultset('MyTable')->with_prev_next;
while (my $row = $rs->next) {
    say "当前id: " . $row->id;
    say "上一条id: " . ($row->prev_id // '无');
    say "下一条id: " . ($row->next_id // '无');
}

2. 嵌入原生SQL片段（复杂窗口逻辑场景） #

如果窗口函数的逻辑更复杂（比如带过滤条件的窗口），可以直接在select里写原生SQL片段：

sub with_prev_next {
    my $self = shift;
    return $self->search(undef, {
        select => [
            'me.*',
            \'LAG(id) OVER (PARTITION BY group_id ORDER BY created_at DESC) AS prev_id',
            \'LEAD(id) OVER (PARTITION BY group_id ORDER BY created_at DESC) AS next_id',
        ],
        as => [qw/id title group_id created_at prev_id next_id/], # 必须和select字段一一对应
    });
}

3. 针对单条记录单独查询上下条（单条场景更高效） #

如果只是针对某一条特定记录找上下条，没必要用窗口函数，直接基于排序字段做两次查询更简单：

假设当前记录的created_at是$current_dt，id是$current_id（避免同时间多条记录的冲突）：

my $rs = $schema->resultset('MyTable');

# 获取上一条记录
my $prev_row = $rs->search({
    -or => [
        { created_at => { '<', $current_dt } },
        { created_at => $current_dt, id => { '<', $current_id } },
    ],
}, {
    order_by => [ { -desc => 'created_at' }, { -desc => 'id' } ],
    rows => 1,
})->single;

# 获取下一条记录
my $next_row = $rs->search({
    -or => [
        { created_at => { '>', $current_dt } },
        { created_at => $current_dt, id => { '>', $current_id } },
    ],
}, {
    order_by => [ { -asc => 'created_at' }, { -asc => 'id' } ],
    rows => 1,
})->single;

方案选择建议 #

• 批量给所有记录加上下条标识：用窗口函数方案（1或2），一次查询搞定，效率更高
• 仅针对单条记录查询：用方案3，逻辑简单，不需要窗口函数的额外开销

内容的提问来源于stack exchange，提问作者Miguel Prz