关于FindAndCount与Find方法执行差异情况 #1500

Closed
opened 2019-12-29 17:45:05 +00:00 by cooppor · 1 comment

有一阵子没用Go写东西了,最近写一项目用到了xorm。在分页场景下用到了FindAndCount()方法,可是这个方法给人的感觉总是很奇怪,奇怪到了开始怀疑自己。首先是使用这个方法后会有莫明的异常,开始以为是结构体字段定义有问题,尤其是和xorm相关的tag部分。一遍遍的翻阅文档,无果…只能拆分成Find() 和 Count()单独执行,但是增加了好多代码量。尤其是复杂查询的时候感觉强迫症要犯了。

在使用这个方法中具体遇到过几种异常情况:

  1. Error: cascade obj is not exist;

这种情况出现在生成的sql的别名有问题。

  1. Error: unsupported struct type in Scan: *xx.Xxx

这种情况出现在没有显示的调用Table()方法

  1. 各种字段找不到

这种情况在刚开始的时候遇到了不少次,但是想不起来触发的情景了,暂时无法还原。

吐槽一下,这个MD编辑器好难用啊,退格删除太累了…

针对上述情况编写了简单的单元测试用例,完整工程见附件:

文件go.mod

module issus-case-of-find-and-count

go 1.13

require (
	github.com/mattn/go-sqlite3 v2.0.2+incompatible
	xorm.io/core v0.7.2
	xorm.io/xorm v0.8.1
)

文件main.go

// ...

// Region
type Region struct {
	Code      string `xorm:"pk 'code'"`
	Name      string `xorm:"'name'"`
	ShortCode string `xorm:"'short_code'"`
}

// Hotel
type Hotel struct {
	Id       int64  `xorm:"bigint(11) pk autoincr"`
	Name     string `xorm:"'name'"`
	Code     string `xorm:"'code'"`
	Region   string `xorm:"'region'"`
	CreateBy *User  `xorm:"'create_by'"`
}

// User
type User struct {
	Id   int64  `xorm:"bigint(11) pk autoincr"`
	Name string `xorm:"'name'"`
}

// ...

文件find_test.go

package main

import (
	"testing"
)

func TestFind1(t *testing.T) {
	hotels := make([]*Hotel, 0)
	err := engine.
		//Table(new(Hotel)).
		Alias("t").
		//Where("t.region like '6501%'").
		Limit(10, 0).
		Find(&hotels)
	if err != nil {
		t.Error("engine.Alias().Find()  执行失败", err)
	} else {
		t.Logf("engine.Alias().Find() 执行结果,Slice Size %d \n", len(hotels))
	}
}

func TestFind2(t *testing.T) {
	hotels := make([]*Hotel, 0)
	err := engine.
		Table(new(Hotel)).
		Alias("t").
		//Where("t.region like '6501%'").
		Limit(10, 0).
		Find(&hotels)
	if err != nil {
		t.Error("engine.Table().Alias().Find() 执行失败", err)
	} else {
		t.Logf("engine.Table().Alias().Find() 执行结果,Slice Size %d \n", len(hotels))
	}
}

func TestFind3(t *testing.T) {
	hotels := make([]*Hotel, 0)
	err := engine.
		Table(new(Hotel)).
		Alias("t").
		Where("t.region like '6501%'").
		Limit(10, 0).
		Find(&hotels)
	if err != nil {
		t.Error("engine.Table().Alias().Where().Find() 执行失败", err)
	} else {
		t.Logf("engine.Table().Alias().Where().Find() 执行结果,Slice Size %d \n", len(hotels))
	}
}

文件find_and_count_test.go

package main

import (
	"testing"
)

func TestFindAndCount1(t *testing.T) {
	hotels := make([]*Hotel, 0)
	cnt, err := engine.
		//Table(new(Hotel)).
		Alias("t").
		//Where("t.region like '6501%'").
		Limit(10, 0).
		FindAndCount(&hotels)
	if err != nil {
		t.Error("engine.Alias().FindAndCount() 执行失败", err)
	} else {
		t.Logf("engine.Alias().FindAndCount() 执行结果,Slice Size %d, Count %d \n", len(hotels), cnt)
	}
}

func TestFindAndCount2(t *testing.T) {
	hotels := make([]*Hotel, 0)
	cnt, err := engine.
		Table(new(Hotel)).
		Alias("t").
		//Where("t.region like '6501%'").
		Limit(10, 0).
		FindAndCount(&hotels)
	if err != nil {
		t.Error("engine.Table().Alias().FindAndCount() 执行失败", err)
	} else {
		t.Logf("engine.Table().Alias().FindAndCount() 执行结果,Slice Size %d, Count %d \n", len(hotels), cnt)
	}
}

func TestFindAndCount3(t *testing.T) {
	hotels := make([]*Hotel, 0)
	cnt, err := engine.
		Table(new(Hotel)).
		Alias("t").
		Where("t.region like '6501%'").
		Limit(10, 0).
		FindAndCount(&hotels)
	if err != nil {
		t.Error("engine.Table().Alias().Where().FindAndCount() 执行失败", err)
	} else {
		t.Logf("engine.Table().Alias().Where().FindAndCount() 执行结果,Slice Size %d, Count %d \n", len(hotels), cnt)
	}
}

测试结果:

$> go test
[xorm] [info]  2019/12/30 01:32:46.805650 [SQL] SELECT `id`, `name`, `code`, `region`, `create_by` FROM `hotel` AS `t` LIMIT 10
[xorm] [info]  2019/12/30 01:32:46.806013 [SQL] SELECT `id`, `name` FROM `user` AS `t` WHERE `id`=? LIMIT 1 []interface {}{1001}
--- FAIL: TestFindAndCount1 (0.00s)
    find_and_count_test.go:16: engine.Alias().FindAndCount() 执行失败 unsupported struct type in Scan: *main.User
[xorm] [info]  2019/12/30 01:32:46.806492 [SQL] SELECT `id`, `name`, `code`, `region`, `create_by` FROM `hotel` AS `t` LIMIT 10
[xorm] [info]  2019/12/30 01:32:46.806634 [SQL] SELECT `id`, `name` FROM `hotel` AS `t` WHERE `id`=? LIMIT 1 []interface {}{1001}
--- FAIL: TestFindAndCount2 (0.00s)
    find_and_count_test.go:31: engine.Table().Alias().FindAndCount() 执行失败 cascade obj is not exist
[xorm] [info]  2019/12/30 01:32:46.806741 [SQL] SELECT `id`, `name`, `code`, `region`, `create_by` FROM `hotel` AS `t` WHERE (t.region like '6501%') LIMIT 10
[xorm] [info]  2019/12/30 01:32:46.806881 [SQL] SELECT `id`, `name` FROM `hotel` AS `t` WHERE (t.region like '6501%') AND `id`=? LIMIT 1 []interface {}{1001}
--- FAIL: TestFindAndCount3 (0.00s)
    find_and_count_test.go:46: engine.Table().Alias().Where().FindAndCount() 执行失败 cascade obj is not exist
[xorm] [info]  2019/12/30 01:32:46.806985 [SQL] SELECT `id`, `name`, `code`, `region`, `create_by` FROM `hotel` AS `t` LIMIT 10
[xorm] [info]  2019/12/30 01:32:46.807237 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1001}
[xorm] [info]  2019/12/30 01:32:46.807440 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1002}
[xorm] [info]  2019/12/30 01:32:46.807588 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1002}
[xorm] [info]  2019/12/30 01:32:46.807775 [SQL] SELECT `id`, `name`, `code`, `region`, `create_by` FROM `hotel` AS `t` LIMIT 10
[xorm] [info]  2019/12/30 01:32:46.807930 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1001}
[xorm] [info]  2019/12/30 01:32:46.808010 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1002}
[xorm] [info]  2019/12/30 01:32:46.808080 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1002}
[xorm] [info]  2019/12/30 01:32:46.808177 [SQL] SELECT `id`, `name`, `code`, `region`, `create_by` FROM `hotel` AS `t` WHERE (t.region like '6501%') LIMIT 10
[xorm] [info]  2019/12/30 01:32:46.808302 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1001}
[xorm] [info]  2019/12/30 01:32:46.808378 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1002}
[xorm] [info]  2019/12/30 01:32:46.808446 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1002}
FAIL
exit status 1
FAIL    issus-case-of-find-and-count    0.991s
有一阵子没用Go写东西了,最近写一项目用到了xorm。在分页场景下用到了FindAndCount()方法,可是这个方法给人的感觉总是很奇怪,奇怪到了开始怀疑自己。首先是使用这个方法后会有莫明的异常,开始以为是结构体字段定义有问题,尤其是和xorm相关的tag部分。一遍遍的翻阅文档,无果…只能拆分成Find() 和 Count()单独执行,但是增加了好多代码量。尤其是复杂查询的时候感觉强迫症要犯了。 在使用这个方法中具体遇到过几种异常情况: 1. Error: cascade obj is not exist; > 这种情况出现在生成的sql的别名有问题。 2. Error: unsupported struct type in Scan: *xx.Xxx > 这种情况出现在没有显示的调用Table()方法 3. 各种字段找不到 > 这种情况在刚开始的时候遇到了不少次,但是想不起来触发的情景了,暂时无法还原。 **吐槽一下,这个MD编辑器好难用啊,退格删除太累了…** 针对上述情况编写了简单的单元测试用例,完整工程见附件: 文件`go.mod` ``` module issus-case-of-find-and-count go 1.13 require ( github.com/mattn/go-sqlite3 v2.0.2+incompatible xorm.io/core v0.7.2 xorm.io/xorm v0.8.1 ) ``` 文件`main.go` ``` // ... // Region type Region struct { Code string `xorm:"pk 'code'"` Name string `xorm:"'name'"` ShortCode string `xorm:"'short_code'"` } // Hotel type Hotel struct { Id int64 `xorm:"bigint(11) pk autoincr"` Name string `xorm:"'name'"` Code string `xorm:"'code'"` Region string `xorm:"'region'"` CreateBy *User `xorm:"'create_by'"` } // User type User struct { Id int64 `xorm:"bigint(11) pk autoincr"` Name string `xorm:"'name'"` } // ... ``` 文件`find_test.go` ``` package main import ( "testing" ) func TestFind1(t *testing.T) { hotels := make([]*Hotel, 0) err := engine. //Table(new(Hotel)). Alias("t"). //Where("t.region like '6501%'"). Limit(10, 0). Find(&hotels) if err != nil { t.Error("engine.Alias().Find() 执行失败", err) } else { t.Logf("engine.Alias().Find() 执行结果,Slice Size %d \n", len(hotels)) } } func TestFind2(t *testing.T) { hotels := make([]*Hotel, 0) err := engine. Table(new(Hotel)). Alias("t"). //Where("t.region like '6501%'"). Limit(10, 0). Find(&hotels) if err != nil { t.Error("engine.Table().Alias().Find() 执行失败", err) } else { t.Logf("engine.Table().Alias().Find() 执行结果,Slice Size %d \n", len(hotels)) } } func TestFind3(t *testing.T) { hotels := make([]*Hotel, 0) err := engine. Table(new(Hotel)). Alias("t"). Where("t.region like '6501%'"). Limit(10, 0). Find(&hotels) if err != nil { t.Error("engine.Table().Alias().Where().Find() 执行失败", err) } else { t.Logf("engine.Table().Alias().Where().Find() 执行结果,Slice Size %d \n", len(hotels)) } } ``` 文件`find_and_count_test.go` ``` package main import ( "testing" ) func TestFindAndCount1(t *testing.T) { hotels := make([]*Hotel, 0) cnt, err := engine. //Table(new(Hotel)). Alias("t"). //Where("t.region like '6501%'"). Limit(10, 0). FindAndCount(&hotels) if err != nil { t.Error("engine.Alias().FindAndCount() 执行失败", err) } else { t.Logf("engine.Alias().FindAndCount() 执行结果,Slice Size %d, Count %d \n", len(hotels), cnt) } } func TestFindAndCount2(t *testing.T) { hotels := make([]*Hotel, 0) cnt, err := engine. Table(new(Hotel)). Alias("t"). //Where("t.region like '6501%'"). Limit(10, 0). FindAndCount(&hotels) if err != nil { t.Error("engine.Table().Alias().FindAndCount() 执行失败", err) } else { t.Logf("engine.Table().Alias().FindAndCount() 执行结果,Slice Size %d, Count %d \n", len(hotels), cnt) } } func TestFindAndCount3(t *testing.T) { hotels := make([]*Hotel, 0) cnt, err := engine. Table(new(Hotel)). Alias("t"). Where("t.region like '6501%'"). Limit(10, 0). FindAndCount(&hotels) if err != nil { t.Error("engine.Table().Alias().Where().FindAndCount() 执行失败", err) } else { t.Logf("engine.Table().Alias().Where().FindAndCount() 执行结果,Slice Size %d, Count %d \n", len(hotels), cnt) } } ``` 测试结果: ``` $> go test [xorm] [info] 2019/12/30 01:32:46.805650 [SQL] SELECT `id`, `name`, `code`, `region`, `create_by` FROM `hotel` AS `t` LIMIT 10 [xorm] [info] 2019/12/30 01:32:46.806013 [SQL] SELECT `id`, `name` FROM `user` AS `t` WHERE `id`=? LIMIT 1 []interface {}{1001} --- FAIL: TestFindAndCount1 (0.00s) find_and_count_test.go:16: engine.Alias().FindAndCount() 执行失败 unsupported struct type in Scan: *main.User [xorm] [info] 2019/12/30 01:32:46.806492 [SQL] SELECT `id`, `name`, `code`, `region`, `create_by` FROM `hotel` AS `t` LIMIT 10 [xorm] [info] 2019/12/30 01:32:46.806634 [SQL] SELECT `id`, `name` FROM `hotel` AS `t` WHERE `id`=? LIMIT 1 []interface {}{1001} --- FAIL: TestFindAndCount2 (0.00s) find_and_count_test.go:31: engine.Table().Alias().FindAndCount() 执行失败 cascade obj is not exist [xorm] [info] 2019/12/30 01:32:46.806741 [SQL] SELECT `id`, `name`, `code`, `region`, `create_by` FROM `hotel` AS `t` WHERE (t.region like '6501%') LIMIT 10 [xorm] [info] 2019/12/30 01:32:46.806881 [SQL] SELECT `id`, `name` FROM `hotel` AS `t` WHERE (t.region like '6501%') AND `id`=? LIMIT 1 []interface {}{1001} --- FAIL: TestFindAndCount3 (0.00s) find_and_count_test.go:46: engine.Table().Alias().Where().FindAndCount() 执行失败 cascade obj is not exist [xorm] [info] 2019/12/30 01:32:46.806985 [SQL] SELECT `id`, `name`, `code`, `region`, `create_by` FROM `hotel` AS `t` LIMIT 10 [xorm] [info] 2019/12/30 01:32:46.807237 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1001} [xorm] [info] 2019/12/30 01:32:46.807440 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1002} [xorm] [info] 2019/12/30 01:32:46.807588 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1002} [xorm] [info] 2019/12/30 01:32:46.807775 [SQL] SELECT `id`, `name`, `code`, `region`, `create_by` FROM `hotel` AS `t` LIMIT 10 [xorm] [info] 2019/12/30 01:32:46.807930 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1001} [xorm] [info] 2019/12/30 01:32:46.808010 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1002} [xorm] [info] 2019/12/30 01:32:46.808080 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1002} [xorm] [info] 2019/12/30 01:32:46.808177 [SQL] SELECT `id`, `name`, `code`, `region`, `create_by` FROM `hotel` AS `t` WHERE (t.region like '6501%') LIMIT 10 [xorm] [info] 2019/12/30 01:32:46.808302 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1001} [xorm] [info] 2019/12/30 01:32:46.808378 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1002} [xorm] [info] 2019/12/30 01:32:46.808446 [SQL] SELECT `id`, `name` FROM `user` WHERE `id`=? LIMIT 1 []interface {}{1002} FAIL exit status 1 FAIL issus-case-of-find-and-count 0.991s ```
lunny added the
kind
bug
label 2019-12-30 01:47:00 +00:00
lunny added this to the 1.1.1 milestone 2020-03-10 04:50:59 +00:00
lunny removed this from the 1.1.1 milestone 2020-03-23 13:37:25 +00:00
Owner

This should be fixed and #1621 could confirm this.

This should be fixed and #1621 could confirm this.
lunny closed this issue 2020-03-23 13:37:48 +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#1500
No description provided.