Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

分页插件，统计sql 多了 ORDER BY，PostgreSQL数据库 #41

Closed
ciyool opened this issue Oct 15, 2020 · 9 comments
Closed

分页插件，统计sql 多了 ORDER BY，PostgreSQL数据库 #41

ciyool opened this issue Oct 15, 2020 · 9 comments

Comments

@ciyool
Copy link

ciyool commented Oct 15, 2020 

let req = PageRequest::new(1, 1); //分页请求，页码，条数
    let wraper = RB
        .new_wrapper()
        .eq("1", 1)
        .order_by(false, &["create_date"])
        .check()
        .unwrap();
    let r: rbatis_core::Result<Page<CyCustomZtzts>> =
        RB.fetch_page_by_wrapper("", &wraper, &req).await;

生成了sql：

SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1 ORDER BY create_date DESC

含有ORDER BY 无法正常运行
@zhuxiujia
Copy link
Member 

let req = PageRequest::new(1, 1); //分页请求，页码，条数
    let wraper = RB
        .new_wrapper()
        .eq("1", 1)
        .order_by(false, &["create_date"])
        .check()
        .unwrap();
    let r: rbatis_core::Result<Page<CyCustomZtzts>> =
        RB.fetch_page_by_wrapper("", &wraper, &req).await;

生成了sql：

SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1 ORDER BY create_date DESC

含有ORDER BY 无法正常运行

我试了没问题呀，是否是最新版本

 #[async_std::test]
    pub async fn test_fetch_page_by_wrapper() {
        let mut rb = init_rbatis().await;
        //设置 逻辑删除插件
        rb.logic_plugin = Some(Box::new(RbatisLogicDeletePlugin::new("delete_flag")));

        let w = rb.new_wrapper()
            .like("name", "test")
            .order_by(false, &["create_time"])
            .check().unwrap();
        let r: Page<BizActivity> = rb.fetch_page_by_wrapper("", &w, &PageRequest::new(1, 20)).await.unwrap();
        println!("{}", serde_json::to_string(&r).unwrap());
    }

2020-10-15T12:13:47.924730+08:00 INFO rbatis::rbatis - [rbatis] [] Query ==> SELECT count(1) FROM biz_activity WHERE delete_flag = 0 AND name LIKE ? ORDER BY create_time DESC
2020-10-15T12:13:47.929941+08:00 INFO rbatis::rbatis - [rbatis] [] Args  ==> ["%test%"]
2020-10-15T12:14:01.144418+08:00 INFO rbatis::rbatis - [rbatis] [] ReturnRows <== 1
2020-10-15T12:14:01.144496+08:00 INFO rbatis::rbatis - [rbatis] [] Query ==> SELECT id,name,pc_link,h5_link,pc_banner_img,h5_banner_img,sort,status,remark,create_time,version,delete_flag FROM biz_activity WHERE delete_flag = 0 AND name LIKE ? ORDER BY create_time DESC LIMIT 0,20
2020-10-15T12:14:01.144529+08:00 INFO rbatis::rbatis - [rbatis] [] Args  ==> ["%test%"]
2020-10-15T12:14:01.149908+08:00 INFO rbatis::rbatis - [rbatis] [] ReturnRows <== 3
{"records":[{"id":"221","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0},{"id":"222","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0},{"id":"223","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0}],"total":3,"pages":1,"size":20,"current":1,"serch_count":true}

@ciyool
Copy link
Author

ciyool commented Oct 15, 2020 

let req = PageRequest::new(1, 1); //分页请求，页码，条数
    let wraper = RB
        .new_wrapper()
        .eq("1", 1)
        .order_by(false, &["create_date"])
        .check()
        .unwrap();
    let r: rbatis_core::Result<Page<CyCustomZtzts>> =
        RB.fetch_page_by_wrapper("", &wraper, &req).await;

生成了sql：

SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1 ORDER BY create_date DESC

含有ORDER BY 无法正常运行

我试了没问题呀，是否是最新版本

 #[async_std::test]
    pub async fn test_fetch_page_by_wrapper() {
        let mut rb = init_rbatis().await;
        //设置 逻辑删除插件
        rb.logic_plugin = Some(Box::new(RbatisLogicDeletePlugin::new("delete_flag")));

        let w = rb.new_wrapper()
            .like("name", "test")
            .order_by(false, &["create_time"])
            .check().unwrap();
        let r: Page<BizActivity> = rb.fetch_page_by_wrapper("", &w, &PageRequest::new(1, 20)).await.unwrap();
        println!("{}", serde_json::to_string(&r).unwrap());
    }

2020-10-15T12:13:47.924730+08:00 INFO rbatis::rbatis - [rbatis] [] Query ==> SELECT count(1) FROM biz_activity WHERE delete_flag = 0 AND name LIKE ? ORDER BY create_time DESC
2020-10-15T12:13:47.929941+08:00 INFO rbatis::rbatis - [rbatis] [] Args  ==> ["%test%"]
2020-10-15T12:14:01.144418+08:00 INFO rbatis::rbatis - [rbatis] [] ReturnRows <== 1
2020-10-15T12:14:01.144496+08:00 INFO rbatis::rbatis - [rbatis] [] Query ==> SELECT id,name,pc_link,h5_link,pc_banner_img,h5_banner_img,sort,status,remark,create_time,version,delete_flag FROM biz_activity WHERE delete_flag = 0 AND name LIKE ? ORDER BY create_time DESC LIMIT 0,20
2020-10-15T12:14:01.144529+08:00 INFO rbatis::rbatis - [rbatis] [] Args  ==> ["%test%"]
2020-10-15T12:14:01.149908+08:00 INFO rbatis::rbatis - [rbatis] [] ReturnRows <== 3
{"records":[{"id":"221","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0},{"id":"222","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0},{"id":"223","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0}],"total":3,"pages":1,"size":20,"current":1,"serch_count":true}

忘记说了，MySQL正常，但我用的是PostgreSQL数据库，这种SQL无法执行。

@ciyool ciyool changed the title 分页插件，统计sql 多了 ORDER BY 分页插件，统计sql 多了 ORDER BY，PostgreSQL数据库 Oct 15, 2020
@zhuxiujia
Copy link
Member

zhuxiujia commented Oct 15, 2020 •
edited 

let req = PageRequest::new(1, 1); //分页请求，页码，条数
    let wraper = RB
        .new_wrapper()
        .eq("1", 1)
        .order_by(false, &["create_date"])
        .check()
        .unwrap();
    let r: rbatis_core::Result<Page<CyCustomZtzts>> =
        RB.fetch_page_by_wrapper("", &wraper, &req).await;

生成了sql：

SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1 ORDER BY create_date DESC

含有ORDER BY 无法正常运行

我试了没问题呀，是否是最新版本

 #[async_std::test]
    pub async fn test_fetch_page_by_wrapper() {
        let mut rb = init_rbatis().await;
        //设置 逻辑删除插件
        rb.logic_plugin = Some(Box::new(RbatisLogicDeletePlugin::new("delete_flag")));

        let w = rb.new_wrapper()
            .like("name", "test")
            .order_by(false, &["create_time"])
            .check().unwrap();
        let r: Page<BizActivity> = rb.fetch_page_by_wrapper("", &w, &PageRequest::new(1, 20)).await.unwrap();
        println!("{}", serde_json::to_string(&r).unwrap());
    }

2020-10-15T12:13:47.924730+08:00 INFO rbatis::rbatis - [rbatis] [] Query ==> SELECT count(1) FROM biz_activity WHERE delete_flag = 0 AND name LIKE ? ORDER BY create_time DESC
2020-10-15T12:13:47.929941+08:00 INFO rbatis::rbatis - [rbatis] [] Args  ==> ["%test%"]
2020-10-15T12:14:01.144418+08:00 INFO rbatis::rbatis - [rbatis] [] ReturnRows <== 1
2020-10-15T12:14:01.144496+08:00 INFO rbatis::rbatis - [rbatis] [] Query ==> SELECT id,name,pc_link,h5_link,pc_banner_img,h5_banner_img,sort,status,remark,create_time,version,delete_flag FROM biz_activity WHERE delete_flag = 0 AND name LIKE ? ORDER BY create_time DESC LIMIT 0,20
2020-10-15T12:14:01.144529+08:00 INFO rbatis::rbatis - [rbatis] [] Args  ==> ["%test%"]
2020-10-15T12:14:01.149908+08:00 INFO rbatis::rbatis - [rbatis] [] ReturnRows <== 3
{"records":[{"id":"221","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0},{"id":"222","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0},{"id":"223","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0}],"total":3,"pages":1,"size":20,"current":1,"serch_count":true}

忘记说了，MySQL正常，但我用的是PostgreSQL数据库，这种SQL无法执行。

用navcat之类的工具确定一下 ORDER BY 是否不能在pg下执行，如果确定的话，分页插件应该去除统计sql出现的order by 语句

@ciyool
Copy link
Author

ciyool commented Oct 15, 2020 

let req = PageRequest::new(1, 1); //分页请求，页码，条数
    let wraper = RB
        .new_wrapper()
        .eq("1", 1)
        .order_by(false, &["create_date"])
        .check()
        .unwrap();
    let r: rbatis_core::Result<Page<CyCustomZtzts>> =
        RB.fetch_page_by_wrapper("", &wraper, &req).await;

生成了sql：

SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1 ORDER BY create_date DESC

含有ORDER BY 无法正常运行

我试了没问题呀，是否是最新版本

 #[async_std::test]
    pub async fn test_fetch_page_by_wrapper() {
        let mut rb = init_rbatis().await;
        //设置 逻辑删除插件
        rb.logic_plugin = Some(Box::new(RbatisLogicDeletePlugin::new("delete_flag")));

        let w = rb.new_wrapper()
            .like("name", "test")
            .order_by(false, &["create_time"])
            .check().unwrap();
        let r: Page<BizActivity> = rb.fetch_page_by_wrapper("", &w, &PageRequest::new(1, 20)).await.unwrap();
        println!("{}", serde_json::to_string(&r).unwrap());
    }

2020-10-15T12:13:47.924730+08:00 INFO rbatis::rbatis - [rbatis] [] Query ==> SELECT count(1) FROM biz_activity WHERE delete_flag = 0 AND name LIKE ? ORDER BY create_time DESC
2020-10-15T12:13:47.929941+08:00 INFO rbatis::rbatis - [rbatis] [] Args  ==> ["%test%"]
2020-10-15T12:14:01.144418+08:00 INFO rbatis::rbatis - [rbatis] [] ReturnRows <== 1
2020-10-15T12:14:01.144496+08:00 INFO rbatis::rbatis - [rbatis] [] Query ==> SELECT id,name,pc_link,h5_link,pc_banner_img,h5_banner_img,sort,status,remark,create_time,version,delete_flag FROM biz_activity WHERE delete_flag = 0 AND name LIKE ? ORDER BY create_time DESC LIMIT 0,20
2020-10-15T12:14:01.144529+08:00 INFO rbatis::rbatis - [rbatis] [] Args  ==> ["%test%"]
2020-10-15T12:14:01.149908+08:00 INFO rbatis::rbatis - [rbatis] [] ReturnRows <== 3
{"records":[{"id":"221","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0},{"id":"222","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0},{"id":"223","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0}],"total":3,"pages":1,"size":20,"current":1,"serch_count":true}

忘记说了，MySQL正常，但我用的是PostgreSQL数据库，这种SQL无法执行。

用navcat之类的工具确定一下 ORDER BY 是否不能在pg下执行，如果确定的话，分页插件应该去除统计sql出现的order by 语句
确认过了，不能运行。
image

@zhuxiujia
Copy link
Member

zhuxiujia commented Oct 15, 2020 •
edited 

let req = PageRequest::new(1, 1); //分页请求，页码，条数
    let wraper = RB
        .new_wrapper()
        .eq("1", 1)
        .order_by(false, &["create_date"])
        .check()
        .unwrap();
    let r: rbatis_core::Result<Page<CyCustomZtzts>> =
        RB.fetch_page_by_wrapper("", &wraper, &req).await;

生成了sql：

SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1 ORDER BY create_date DESC

含有ORDER BY 无法正常运行

我试了没问题呀，是否是最新版本

 #[async_std::test]
    pub async fn test_fetch_page_by_wrapper() {
        let mut rb = init_rbatis().await;
        //设置 逻辑删除插件
        rb.logic_plugin = Some(Box::new(RbatisLogicDeletePlugin::new("delete_flag")));

        let w = rb.new_wrapper()
            .like("name", "test")
            .order_by(false, &["create_time"])
            .check().unwrap();
        let r: Page<BizActivity> = rb.fetch_page_by_wrapper("", &w, &PageRequest::new(1, 20)).await.unwrap();
        println!("{}", serde_json::to_string(&r).unwrap());
    }

2020-10-15T12:13:47.924730+08:00 INFO rbatis::rbatis - [rbatis] [] Query ==> SELECT count(1) FROM biz_activity WHERE delete_flag = 0 AND name LIKE ? ORDER BY create_time DESC
2020-10-15T12:13:47.929941+08:00 INFO rbatis::rbatis - [rbatis] [] Args  ==> ["%test%"]
2020-10-15T12:14:01.144418+08:00 INFO rbatis::rbatis - [rbatis] [] ReturnRows <== 1
2020-10-15T12:14:01.144496+08:00 INFO rbatis::rbatis - [rbatis] [] Query ==> SELECT id,name,pc_link,h5_link,pc_banner_img,h5_banner_img,sort,status,remark,create_time,version,delete_flag FROM biz_activity WHERE delete_flag = 0 AND name LIKE ? ORDER BY create_time DESC LIMIT 0,20
2020-10-15T12:14:01.144529+08:00 INFO rbatis::rbatis - [rbatis] [] Args  ==> ["%test%"]
2020-10-15T12:14:01.149908+08:00 INFO rbatis::rbatis - [rbatis] [] ReturnRows <== 3
{"records":[{"id":"221","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0},{"id":"222","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0},{"id":"223","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0}],"total":3,"pages":1,"size":20,"current":1,"serch_count":true}

忘记说了，MySQL正常，但我用的是PostgreSQL数据库，这种SQL无法执行。

用navcat之类的工具确定一下 ORDER BY 是否不能在pg下执行，如果确定的话，分页插件应该去除统计sql出现的order by 语句
确认过了，不能运行。
image

确认一下，去除count中的order by 的sql 是否能正常统计？ 还有你的报错信息是什么。

我感觉 是不是类型转换引起的，order_by 改成 .order_by(false, &["create_time::timestamp"])

@ciyool
Copy link
Author

ciyool commented Oct 15, 2020 

let req = PageRequest::new(1, 1); //分页请求，页码，条数
    let wraper = RB
        .new_wrapper()
        .eq("1", 1)
        .order_by(false, &["create_date"])
        .check()
        .unwrap();
    let r: rbatis_core::Result<Page<CyCustomZtzts>> =
        RB.fetch_page_by_wrapper("", &wraper, &req).await;

生成了sql：

SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1 ORDER BY create_date DESC

含有ORDER BY 无法正常运行

我试了没问题呀，是否是最新版本

 #[async_std::test]
    pub async fn test_fetch_page_by_wrapper() {
        let mut rb = init_rbatis().await;
        //设置 逻辑删除插件
        rb.logic_plugin = Some(Box::new(RbatisLogicDeletePlugin::new("delete_flag")));

        let w = rb.new_wrapper()
            .like("name", "test")
            .order_by(false, &["create_time"])
            .check().unwrap();
        let r: Page<BizActivity> = rb.fetch_page_by_wrapper("", &w, &PageRequest::new(1, 20)).await.unwrap();
        println!("{}", serde_json::to_string(&r).unwrap());
    }

2020-10-15T12:13:47.924730+08:00 INFO rbatis::rbatis - [rbatis] [] Query ==> SELECT count(1) FROM biz_activity WHERE delete_flag = 0 AND name LIKE ? ORDER BY create_time DESC
2020-10-15T12:13:47.929941+08:00 INFO rbatis::rbatis - [rbatis] [] Args  ==> ["%test%"]
2020-10-15T12:14:01.144418+08:00 INFO rbatis::rbatis - [rbatis] [] ReturnRows <== 1
2020-10-15T12:14:01.144496+08:00 INFO rbatis::rbatis - [rbatis] [] Query ==> SELECT id,name,pc_link,h5_link,pc_banner_img,h5_banner_img,sort,status,remark,create_time,version,delete_flag FROM biz_activity WHERE delete_flag = 0 AND name LIKE ? ORDER BY create_time DESC LIMIT 0,20
2020-10-15T12:14:01.144529+08:00 INFO rbatis::rbatis - [rbatis] [] Args  ==> ["%test%"]
2020-10-15T12:14:01.149908+08:00 INFO rbatis::rbatis - [rbatis] [] ReturnRows <== 3
{"records":[{"id":"221","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0},{"id":"222","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0},{"id":"223","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0}],"total":3,"pages":1,"size":20,"current":1,"serch_count":true}

忘记说了，MySQL正常，但我用的是PostgreSQL数据库，这种SQL无法执行。

用navcat之类的工具确定一下 ORDER BY 是否不能在pg下执行，如果确定的话，分页插件应该去除统计sql出现的order by 语句
确认过了，不能运行。
image

确认一下，去除count中的order by 的sql 是否能正常统计？ 还有你的报错信息是什么。

我感觉 是不是类型转换引起的，order_by 改成 .order_by(false, &["create_time::timestamp"])

【出错】含有 order_by 结果如下：

10-15 15:15:37.833405500 [rbatis::rbatis] INFO:[271] [rbatis] [] Query ==> SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1 ORDER BY create_date DESC
10-15 15:15:37.847567600 [rbatis::rbatis] INFO:[272] [rbatis] [] Args  ==> [1]
10-15 15:15:38.019037600 [] INFO:[34] [F]===> Err(Database(PgError(Response { severity: Error, code: "42803", message: "column \"cy_custom_ztzts.create_date\" must appear in the GROUP BY clause or be used in an aggregate f
unction", detail: None, hint: None, position: Some(60), internal_position: None, internal_query: None, where_: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some("parse_agg.c"), line: Some(1409), routine: Some("check_ungrou
ped_columns_walker") })))

【正确】没有order_by 结果如下：

10-15 15:19:27.325783100 [rbatis::rbatis] INFO:[271] [rbatis] [] Query ==> SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1
10-15 15:19:27.327838000 [rbatis::rbatis] INFO:[272] [rbatis] [] Args  ==> [1]
10-15 15:19:27.531099400 [rbatis::rbatis] INFO:[294] [rbatis] [] ReturnRows <== 1

【出错】order_by 改成 .order_by(false, &["create_date::timestamp"]) 结果如下：

10-15 15:24:52.236024500 [rbatis::rbatis] INFO:[271] [rbatis] [] Query ==> SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1 ORDER BY create_date::timestamp DESC
10-15 15:24:52.238953900 [rbatis::rbatis] INFO:[272] [rbatis] [] Args  ==> [1]
10-15 15:24:52.422045700 [] INFO:[35] [F]===> Err(Database(PgError(Response { severity: Error, code: "42803", message: "column \"cy_custom_ztzts.create_date\" must appear in the GROUP BY clause or be used in an aggregate f
unction", detail: None, hint: None, position: Some(60), internal_position: None, internal_query: None, where_: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some("parse_agg.c"), line: Some(1409), routine: Some("check_ungrou
ped_columns_walker") })))

@zhuxiujia
Copy link
Member

zhuxiujia commented Oct 15, 2020 •
edited 

let req = PageRequest::new(1, 1); //分页请求，页码，条数
    let wraper = RB
        .new_wrapper()
        .eq("1", 1)
        .order_by(false, &["create_date"])
        .check()
        .unwrap();
    let r: rbatis_core::Result<Page<CyCustomZtzts>> =
        RB.fetch_page_by_wrapper("", &wraper, &req).await;

生成了sql：

SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1 ORDER BY create_date DESC

含有ORDER BY 无法正常运行

我试了没问题呀，是否是最新版本

 #[async_std::test]
    pub async fn test_fetch_page_by_wrapper() {
        let mut rb = init_rbatis().await;
        //设置 逻辑删除插件
        rb.logic_plugin = Some(Box::new(RbatisLogicDeletePlugin::new("delete_flag")));

        let w = rb.new_wrapper()
            .like("name", "test")
            .order_by(false, &["create_time"])
            .check().unwrap();
        let r: Page<BizActivity> = rb.fetch_page_by_wrapper("", &w, &PageRequest::new(1, 20)).await.unwrap();
        println!("{}", serde_json::to_string(&r).unwrap());
    }

2020-10-15T12:13:47.924730+08:00 INFO rbatis::rbatis - [rbatis] [] Query ==> SELECT count(1) FROM biz_activity WHERE delete_flag = 0 AND name LIKE ? ORDER BY create_time DESC
2020-10-15T12:13:47.929941+08:00 INFO rbatis::rbatis - [rbatis] [] Args  ==> ["%test%"]
2020-10-15T12:14:01.144418+08:00 INFO rbatis::rbatis - [rbatis] [] ReturnRows <== 1
2020-10-15T12:14:01.144496+08:00 INFO rbatis::rbatis - [rbatis] [] Query ==> SELECT id,name,pc_link,h5_link,pc_banner_img,h5_banner_img,sort,status,remark,create_time,version,delete_flag FROM biz_activity WHERE delete_flag = 0 AND name LIKE ? ORDER BY create_time DESC LIMIT 0,20
2020-10-15T12:14:01.144529+08:00 INFO rbatis::rbatis - [rbatis] [] Args  ==> ["%test%"]
2020-10-15T12:14:01.149908+08:00 INFO rbatis::rbatis - [rbatis] [] ReturnRows <== 3
{"records":[{"id":"221","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0},{"id":"222","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0},{"id":"223","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0}],"total":3,"pages":1,"size":20,"current":1,"serch_count":true}

忘记说了，MySQL正常，但我用的是PostgreSQL数据库，这种SQL无法执行。

用navcat之类的工具确定一下 ORDER BY 是否不能在pg下执行，如果确定的话，分页插件应该去除统计sql出现的order by 语句
确认过了，不能运行。
image

确认一下，去除count中的order by 的sql 是否能正常统计？ 还有你的报错信息是什么。
我感觉 是不是类型转换引起的，order_by 改成 .order_by(false, &["create_time::timestamp"])

【出错】含有 order_by 结果如下：

10-15 15:15:37.833405500 [rbatis::rbatis] INFO:[271] [rbatis] [] Query ==> SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1 ORDER BY create_date DESC
10-15 15:15:37.847567600 [rbatis::rbatis] INFO:[272] [rbatis] [] Args  ==> [1]
10-15 15:15:38.019037600 [] INFO:[34] [F]===> Err(Database(PgError(Response { severity: Error, code: "42803", message: "column \"cy_custom_ztzts.create_date\" must appear in the GROUP BY clause or be used in an aggregate f
unction", detail: None, hint: None, position: Some(60), internal_position: None, internal_query: None, where_: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some("parse_agg.c"), line: Some(1409), routine: Some("check_ungrou
ped_columns_walker") })))

【正确】没有order_by 结果如下：

10-15 15:19:27.325783100 [rbatis::rbatis] INFO:[271] [rbatis] [] Query ==> SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1
10-15 15:19:27.327838000 [rbatis::rbatis] INFO:[272] [rbatis] [] Args  ==> [1]
10-15 15:19:27.531099400 [rbatis::rbatis] INFO:[294] [rbatis] [] ReturnRows <== 1

【出错】order_by 改成 .order_by(false, &["create_date::timestamp"]) 结果如下：

10-15 15:24:52.236024500 [rbatis::rbatis] INFO:[271] [rbatis] [] Query ==> SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1 ORDER BY create_date::timestamp DESC
10-15 15:24:52.238953900 [rbatis::rbatis] INFO:[272] [rbatis] [] Args  ==> [1]
10-15 15:24:52.422045700 [] INFO:[35] [F]===> Err(Database(PgError(Response { severity: Error, code: "42803", message: "column \"cy_custom_ztzts.create_date\" must appear in the GROUP BY clause or be used in an aggregate f
unction", detail: None, hint: None, position: Some(60), internal_position: None, internal_query: None, where_: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some("parse_agg.c"), line: Some(1409), routine: Some("check_ungrou
ped_columns_walker") })))

试一下在 mavcat里 跑一下 能否跑成功

SELECT count(1) FROM (SELECT * FROM cy_custom_ztzts WHERE 1 = 1 ORDER BY create_date DESC)  table_count

@ciyool
Copy link
Author

ciyool commented Oct 15, 2020 

let req = PageRequest::new(1, 1); //分页请求，页码，条数
    let wraper = RB
        .new_wrapper()
        .eq("1", 1)
        .order_by(false, &["create_date"])
        .check()
        .unwrap();
    let r: rbatis_core::Result<Page<CyCustomZtzts>> =
        RB.fetch_page_by_wrapper("", &wraper, &req).await;

生成了sql：

SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1 ORDER BY create_date DESC

含有ORDER BY 无法正常运行

我试了没问题呀，是否是最新版本

 #[async_std::test]
    pub async fn test_fetch_page_by_wrapper() {
        let mut rb = init_rbatis().await;
        //设置 逻辑删除插件
        rb.logic_plugin = Some(Box::new(RbatisLogicDeletePlugin::new("delete_flag")));

        let w = rb.new_wrapper()
            .like("name", "test")
            .order_by(false, &["create_time"])
            .check().unwrap();
        let r: Page<BizActivity> = rb.fetch_page_by_wrapper("", &w, &PageRequest::new(1, 20)).await.unwrap();
        println!("{}", serde_json::to_string(&r).unwrap());
    }

2020-10-15T12:13:47.924730+08:00 INFO rbatis::rbatis - [rbatis] [] Query ==> SELECT count(1) FROM biz_activity WHERE delete_flag = 0 AND name LIKE ? ORDER BY create_time DESC
2020-10-15T12:13:47.929941+08:00 INFO rbatis::rbatis - [rbatis] [] Args  ==> ["%test%"]
2020-10-15T12:14:01.144418+08:00 INFO rbatis::rbatis - [rbatis] [] ReturnRows <== 1
2020-10-15T12:14:01.144496+08:00 INFO rbatis::rbatis - [rbatis] [] Query ==> SELECT id,name,pc_link,h5_link,pc_banner_img,h5_banner_img,sort,status,remark,create_time,version,delete_flag FROM biz_activity WHERE delete_flag = 0 AND name LIKE ? ORDER BY create_time DESC LIMIT 0,20
2020-10-15T12:14:01.144529+08:00 INFO rbatis::rbatis - [rbatis] [] Args  ==> ["%test%"]
2020-10-15T12:14:01.149908+08:00 INFO rbatis::rbatis - [rbatis] [] ReturnRows <== 3
{"records":[{"id":"221","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0},{"id":"222","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0},{"id":"223","name":"test","pc_link":"","h5_link":"","pc_banner_img":null,"h5_banner_img":null,"sort":"0","status":0,"remark":"","create_time":"2020-06-17T20:10:23","version":"0","delete_flag":0}],"total":3,"pages":1,"size":20,"current":1,"serch_count":true}

忘记说了，MySQL正常，但我用的是PostgreSQL数据库，这种SQL无法执行。

用navcat之类的工具确定一下 ORDER BY 是否不能在pg下执行，如果确定的话，分页插件应该去除统计sql出现的order by 语句
确认过了，不能运行。
image

确认一下，去除count中的order by 的sql 是否能正常统计？ 还有你的报错信息是什么。
我感觉 是不是类型转换引起的，order_by 改成 .order_by(false, &["create_time::timestamp"])

【出错】含有 order_by 结果如下：

10-15 15:15:37.833405500 [rbatis::rbatis] INFO:[271] [rbatis] [] Query ==> SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1 ORDER BY create_date DESC
10-15 15:15:37.847567600 [rbatis::rbatis] INFO:[272] [rbatis] [] Args  ==> [1]
10-15 15:15:38.019037600 [] INFO:[34] [F]===> Err(Database(PgError(Response { severity: Error, code: "42803", message: "column \"cy_custom_ztzts.create_date\" must appear in the GROUP BY clause or be used in an aggregate f
unction", detail: None, hint: None, position: Some(60), internal_position: None, internal_query: None, where_: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some("parse_agg.c"), line: Some(1409), routine: Some("check_ungrou
ped_columns_walker") })))

【正确】没有order_by 结果如下：

10-15 15:19:27.325783100 [rbatis::rbatis] INFO:[271] [rbatis] [] Query ==> SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1
10-15 15:19:27.327838000 [rbatis::rbatis] INFO:[272] [rbatis] [] Args  ==> [1]
10-15 15:19:27.531099400 [rbatis::rbatis] INFO:[294] [rbatis] [] ReturnRows <== 1

【出错】order_by 改成 .order_by(false, &["create_date::timestamp"]) 结果如下：

10-15 15:24:52.236024500 [rbatis::rbatis] INFO:[271] [rbatis] [] Query ==> SELECT count(1) FROM cy_custom_ztzts WHERE 1 = $1 ORDER BY create_date::timestamp DESC
10-15 15:24:52.238953900 [rbatis::rbatis] INFO:[272] [rbatis] [] Args  ==> [1]
10-15 15:24:52.422045700 [] INFO:[35] [F]===> Err(Database(PgError(Response { severity: Error, code: "42803", message: "column \"cy_custom_ztzts.create_date\" must appear in the GROUP BY clause or be used in an aggregate f
unction", detail: None, hint: None, position: Some(60), internal_position: None, internal_query: None, where_: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some("parse_agg.c"), line: Some(1409), routine: Some("check_ungrou
ped_columns_walker") })))

试一下在 mavcat里 跑一下 能否跑成功

SELECT count(1) FROM (SELECT * FROM cy_custom_ztzts WHERE 1 = 1 ORDER BY create_date DESC)  table_count

执行成功。
但是这样不会影响效率么？最好还是能自动删除 order by。

@zhuxiujia
Copy link
Member

v1.7.0已去除，修复

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@zhuxiujia @ciyool