limit分页不支持sqlite3数据库 #1902

New Issue

xjjrocker · 2021-05-06T03:39:11Z

xjjrocker commented

2021-05-06 03:39:11 +00:00

XEngine.Limit(pageSize, pageSize*(pageIndex-1)).Find(m)分页，对sqlite3不起作用，跟踪下来发现产生的sql语句没有limit代码。用mysql没问题，改成sqllite就不行了，分页失效。
xorm有limit语法，又说支持sqllite，但分页没有相应的兼容代码。
sqllite可以用 limit 15 offset 20 表示: 从表跳过20条记录选出15条记录；xorm对于sqllite的limit那个语法是空白的应该可以增加上这块，以让分页也兼容sqlite。

XEngine.Limit(pageSize, pageSize*(pageIndex-1)).Find(m)分页，对sqlite3不起作用，跟踪下来发现产生的sql语句没有limit代码。用mysql没问题，改成sqllite就不行了，分页失效。 xorm有limit语法，又说支持sqllite，但分页没有相应的兼容代码。 sqllite可以用 limit 15 offset 20 表示: 从表跳过20条记录选出15条记录；xorm对于sqllite的limit那个语法是空白的应该可以增加上这块，以让分页也兼容sqlite。

lunny commented

2021-05-07 01:27:06 +00:00

Which version are you using?

xjjrocker commented

2021-05-07 14:58:54 +00:00

Which version are you using?

xorm.io/core v0.7.3
xorm.io/xorm v1.0.5

以上来自go.mod，刚刚更新到1.07，问题依旧存在。

> Which version are you using? xorm.io/core v0.7.3 xorm.io/xorm v1.0.5 以上来自go.mod，刚刚更新到1.07，问题依旧存在。

lunny commented

2021-05-08 04:32:19 +00:00

I cannot reproduce this. Could you paste your test codes. And you should not use xorm.io/core but xorm.io/xorm/core with v1.x.x

I cannot reproduce this. Could you paste your test codes. And you should not use `xorm.io/core` but `xorm.io/xorm/core` with v1.x.x

xjjrocker commented

2021-05-09 16:28:33 +00:00

// 分页查询

func RoleFindAll(m *[]Role, pageSize /*每页条数*/, pageIndex /*第几页*/ int) (int, error) {
	role := new(Role)
	total, _ := XEngine.Count(role)

	// 修正删除导致的页面查询问题:如果当前页没数据了，就退回前一页
	if !(int(total) > pageSize*(pageIndex-1)) {
		pageIndex = pageIndex - 1
	}

	if err := XEngine.Limit(pageSize, pageSize*(pageIndex-1)).Find(m); err != nil {
		return 0, err
	}

	return int(total), nil
}

是limit针对sqllite3的db不支持

// 分页查询 ``` func RoleFindAll(m *[]Role, pageSize /*每页条数*/, pageIndex /*第几页*/ int) (int, error) { role := new(Role) total, _ := XEngine.Count(role) // 修正删除导致的页面查询问题:如果当前页没数据了，就退回前一页 if !(int(total) > pageSize*(pageIndex-1)) { pageIndex = pageIndex - 1 } if err := XEngine.Limit(pageSize, pageSize*(pageIndex-1)).Find(m); err != nil { return 0, err } return int(total), nil } ``` 是limit针对sqllite3的db不支持

lunny commented

2021-05-09 17:48:11 +00:00

According sqlite document, both SQL syntax are acceptable. https://www.sqlite.org/lang_select.html#limitoffset

xjjrocker commented

2021-05-10 02:02:56 +00:00

According sqlite document, both SQL syntax are acceptable. https://www.sqlite.org/lang_select.html#limitoffset

不是他们不支持是xorm没有转换，你可以shousql跟踪啊。非常容易复现。

> According sqlite document, both SQL syntax are acceptable. https://www.sqlite.org/lang_select.html#limitoffset 不是他们不支持是xorm没有转换，你可以shousql跟踪啊。非常容易复现。

lunny commented

2021-05-10 04:09:00 +00:00

You can find the tests here #1904 , I cannot reproduce your problem.

You can find the tests here https://gitea.com/xorm/xorm/pulls/1904 , I cannot reproduce your problem.

xjjrocker commented

2021-05-15 14:34:47 +00:00

func OperInOutFindAll(m *[]ResOperLogin, x ResOperLogin, pageSize /*每页条数*/, pageIndex /*第几页*/ int) (int, error) {
	oper := new(OperLogin)
	total, _ := XEngine.Count(oper)

	var sqlstr = "select t_oper_login.*, t_oper.oper_name from t_oper_login, t_oper where t_oper_login.operid = t_oper.operid"
	if x.Operid != "" {
		sqlstr = sqlstr + " and t_oper.operid='" + x.Operid + "'"
	}
	if x.OperName != "" {
		sqlstr = sqlstr + " and t_oper.oper_name like'%" + x.OperName + "%'"
	}
	sqlstr = sqlstr + " order by t_oper_login.login_date,t_oper_login.login_time desc"

	// 修正删除导致的页面查询问题:如果当前页没数据了，就退回前一页
	if !(int(total) > pageSize*(pageIndex-1)) {
		pageIndex = pageIndex - 1
	}

	if err := XEngine.SQL(sqlstr).Limit(pageSize, pageSize*(pageIndex-1)).Find(m); err != nil {
		// if err := XEngine.Limit(pageSize, pageSize*(pageIndex-1)).Find(m); err != nil {
		return 0, err
	}

	return int(total), nil
}

就是这段代码，出来的就是没分页的。请问哪里错了吗?
XEngine.ShowSQL(true),看到的执行的sql代码根本没有limit。
生成的语句是：

[xorm] [info]  2021/05/15 23:16:20.119867 [SQL] select t_oper_login.*, t_oper.oper_name from t_oper_login, t_oper where t_oper_login.operid = t_oper.operid order by t_oper_login.login_date,t_oper_login.login_time desc []

``` func OperInOutFindAll(m *[]ResOperLogin, x ResOperLogin, pageSize /*每页条数*/, pageIndex /*第几页*/ int) (int, error) { oper := new(OperLogin) total, _ := XEngine.Count(oper) var sqlstr = "select t_oper_login.*, t_oper.oper_name from t_oper_login, t_oper where t_oper_login.operid = t_oper.operid" if x.Operid != "" { sqlstr = sqlstr + " and t_oper.operid='" + x.Operid + "'" } if x.OperName != "" { sqlstr = sqlstr + " and t_oper.oper_name like'%" + x.OperName + "%'" } sqlstr = sqlstr + " order by t_oper_login.login_date,t_oper_login.login_time desc" // 修正删除导致的页面查询问题:如果当前页没数据了，就退回前一页 if !(int(total) > pageSize*(pageIndex-1)) { pageIndex = pageIndex - 1 } if err := XEngine.SQL(sqlstr).Limit(pageSize, pageSize*(pageIndex-1)).Find(m); err != nil { // if err := XEngine.Limit(pageSize, pageSize*(pageIndex-1)).Find(m); err != nil { return 0, err } return int(total), nil } ``` 就是这段代码，出来的就是没分页的。请问哪里错了吗? XEngine.ShowSQL(true),看到的执行的sql代码根本没有limit。生成的语句是： ``` [xorm] [info] 2021/05/15 23:16:20.119867 [SQL] select t_oper_login.*, t_oper.oper_name from t_oper_login, t_oper where t_oper_login.operid = t_oper.operid order by t_oper_login.login_date,t_oper_login.login_time desc [] ```

lunny commented

2021-06-06 05:28:04 +00:00

You cannot mix SQL and Limit. That's not supported and will not.

You cannot mix `SQL` and `Limit`. That's not supported and will not.

lunny added the

wontfix

label 2021-06-06 05:28:10 +00:00

lunny closed this issue

2021-06-06 12:56:38 +00:00

Sign in to join this conversation.

No Milestone

No Assignees

2 Participants

Notifications

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: xorm/xorm#1902