[Feature Request] - Add SqlServer2008Dialect to support page

[swola]

Reason

会不会加sqlserver2008的分页支持

Description

自己写的简单分页试了下能用 不知道复杂sql会不会出问题

Existing solutions

@Override
public void paginate(PaginationContext ctx) {
    //反射获取ctx中的originSql
    String originalSql;
    try {
        java.lang.reflect.Field field = ctx.getClass().getDeclaredField("originSql");
        field.setAccessible(true);
        originalSql = ((String) field.get(ctx)).toLowerCase();
        field.set(ctx, "");
    } catch (NoSuchFieldException | IllegalAccessException e) {
        throw new RuntimeException("Failed to access originSql field", e);
    }
    //判断带不带order by
    if (!originalSql.contains("order by")){
        originalSql = originalSql + " order by .current_timestamp ";
    }

    ctx.origin()
            .sql("SELECT * FROM (")
            .sql("SELECT * , ROW_NUMBER() OVER ( ORDER BY ")
            .sql(StrUtil.subAfter(StrUtil.subAfter(originalSql, "order by", true),".", true))
            .sql(") AS row_number FROM (")
            .sql(StrUtil.subBefore(originalSql, "order by", false))
            .sql(") as inner_table")
            .sql(") as outer_table WHERE row_number BETWEEN ")
            .variable(ctx.getOffset() + 1)
            .sql(" AND ")
            .variable(ctx.getOffset() + ctx.getLimit());
}

[scvzerng]

这个好像有两个问题

1. order by 如果有多个他目前只会取第一个
2. 多表关联的时候关联表的order by 字段会被优化掉,所以以下案例 orderProduct.product.name 字段会被优化掉 order by 截取无效

比如一下查询

    List<Order> page = sqlClients.createQuery(T)
                .where(T.status().eq("FINISH"))
                .where(T.orderProducts(op -> op.id().ge(0L)))
                .where(T.orderProducts(op -> op.product().name().like("%钢%")))
                .orderBy(T.asTableEx().orderProducts().id(), T.asTableEx().orderProducts().product().name())
                .select(
                        T.fetch(
                                Fetchers.ORDER_FETCHER
                                        .allScalarFields()
                                        .orderProducts(
                                                Fetchers.ORDER_PRODUCT_FETCHER
                                                        .allScalarFields()
                                                        .product(
                                                                Fetchers.PRODUCT_FETCHER
                                                                        .allScalarFields()
                                                        )
                                        )

                        )
                ).execute();

select
    tb_1_.ID,
    tb_1_.STATUS,
    tb_1_.NET_WEIGHT,
    tb_1_.CREATE_TIME
from [Order] tb_1_
inner join ORDER_PRODUCT tb_2_
    on tb_1_.ID = tb_2_.order_id
inner join PRODUCT tb_3_
    on tb_2_.PRODUCT_ID = tb_3_.ID
where
        tb_1_.STATUS = ? /* FINISH */
    and
        exists(
            select
                1
            from ORDER_PRODUCT tb_4_
            inner join PRODUCT tb_6_
                on tb_4_.PRODUCT_ID = tb_6_.ID
            where
                    tb_4_.order_id = tb_1_.ID
                and
                    tb_4_.ID >= ? /* 0 */
                and
                    tb_6_.NAME like ? /* %钢% */
        )
order by
    tb_2_.ID asc,
    tb_3_.NAME asc

这个实现看上去似乎只能满足order by 在主表的情况下有效一旦有关联表字段参与排序就不行了