错误：在使用GORM时缺少FROM子句表项。

huangapple
go评论106阅读模式
英文:

ERROR: missing FROM-clause entry for table when using GORM

问题

这是我的数据库中的表格：

  1. CREATE TABLE vehicles
  2. (
  3.     id          VARCHAR PRIMARY KEY,
  4.     make        VARCHAR NOT NULL,
  5.     model       VARCHAR NOT NULL
  6. );
  8. CREATE TABLE collisions
  9. (
  10.     id            VARCHAR PRIMARY KEY,
  11.     longitude     FLOAT NOT NULL,
  12.     latitude      FLOAT NOT NULL
  13. );
  15. CREATE TABLE vehicle_collisions
  16. (   
  17.     vehicle_id    VARCHAR NOT NULL,
  18.     collision_id  VARCHAR NOT NULL,
  19.     PRIMARY KEY (vehicle_id, collision_id)
  20. );

所以我需要找到具有特定 collision_id 的车辆列表。我正在使用 gorm。

我尝试以以下方式实现：

  1. var vehicles []entities.Vehicles
  2. err := r.db.Joins("JOIN vehicles as vh on vh.id=vehicle_collisions.vehicle_id").Where("vehicle_collisions.collision_id=?",
  3.     id).Find(&vehicles).Error
  4. if err != nil {
  5.     fmt.Println(err)
  6. }

但是它给我抛出了错误：

  1. ERROR: missing FROM-clause entry for table "vehicle_collisions" (SQLSTATE 42P01)

任何帮助将不胜感激。

英文:

These are the tables in my database

  1. CREATE TABLE vehicles
  2. (
  3.     id          VARCHAR PRIMARY KEY,
  4.     make        VARCHAR NOT NULL,
  5.     model       VARCHAR NOT NULL,
  6. )
  8. CREATE TABLE collisions
  9. (
  10.     id            VARCHAR PRIMARY KEY,
  11.     longitude     FLOAT NOT NULL,
  12.     latitude      FLOAT NOT NULL,
  13. )
  15. CREATE TABLE vehicle_collisions
  16. (   
  17.     vehicle_id    VARCHAR NOT NULL,
  18.     collision_id  VARCHAR NOT NULL,
  19.     PRIMARY KEY (vehicle_id, collision_id)
  20. )

So i need to find list of vehicles with a particular collision_id. I am using gorm .

I tried to implement it in a way

  1. 	var vehicles []entities.Vehicles
  2. 	err := r.db.Joins("JOIN vehicles as vh on vh.id=vehicle_collisions.vehicle_id").Where("vehicle_collisions.collision_id=?",
  3. 		id).Find(&vehicles).Error
  4. 	if err != nil {
  5. 		fmt.Println(err)
  6. 	}

But it is throwing me error

  1.  ERROR: missing FROM-clause entry for table "vehicle_collisions" (SQLSTATE 42P01)

Any help would really be appreciated.

答案1

得分: 1

感谢mkopriva的指点。
当你将&vehicles（类型为[]entities.Vehicles）传递给Find函数时，生成的查询语句如下所示：

  1. SELECT
  2.   *
  3. FROM
  4.   vehicles
  5. JOIN
  6.   vehicles vh
  7. ON vh.id = vehicle_collisions.vehicle_id
  8. WHERE vehicle_collisions.collision_id=1

这个查询语句是不正确的，为了解决这个问题，需要修改查询语句如下：

  1. err := r.db.
  2. 	Joins("JOIN vehicle_collisions AS vc ON vc.vehicle_id=vehicles.id").
  3. 	Where("vc.collision_id = ?", id).
  4. 	Find(&vehicles).Error
英文:

Thank you mkopriva as pointed
when you pass &vehicles which is []entities.Vehicles to Find the query generated would be as below:

  1. SELECT
  2.   *
  3. FROM
  4.   vehicles
  5. JOIN
  6.   vehicles vh
  7. ON vh.id = vehicle_collisions.vehicle_id
  8. WHERE vehicle_collisions.collision_id=1

which won't be correct to solve the problem modify the query as:

  1. err := r.db.
  2. 	Joins("JOIN vehicle_collisions AS vc ON vc.vehicle_id=vehicles.id").
  3. 	Where("vc.collision_id = ?", id).
  4. 	Find(&vehicles).Error

答案2

得分: 0

由于问题缺少一些细节，我尝试猜测它们。希望所提供的答案与您相关！以下是在我的环境中运行的代码：

  1. package main
  3. import (
  4. 	"gorm.io/driver/postgres"
  5. 	"gorm.io/gorm"
  7. 	_ "github.com/lib/pq"
  8. )
  10. type Vehicle struct {
  11. 	Id         string
  12. 	Make       string
  13. 	Model      string
  14. 	Collisions []Collision `gorm:"many2many:vehicle_collisions"`
  15. }
  17. type Collision struct {
  18. 	Id        string
  19. 	Longitude float64
  20. 	Latitude  float64
  21. }
  23. func main() {
  24. 	dsn := "host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable"
  25. 	db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
  26. 	if err != nil {
  27. 		panic(err)
  28. 	}
  30. 	db.AutoMigrate(&Vehicle{})
  31. 	db.AutoMigrate(&Collision{})
  33. 	// 向数据库添加虚拟数据
  34. 	collision := &Collision{"1", 4.4, 4.5}
  35. 	db.Create(collision)
  36. 	db.Create(&Collision{"2", 1.1, 1.5})
  37. 	db.Create(&Vehicle{Id: "1", Make: "ford", Model: "fiesta", Collisions: []Collision{*collision}})
  38. 	db.Create(&Vehicle{Id: "2", Make: "fiat", Model: "punto", Collisions: []Collision{*collision}})
  40. 	// 获取所有与碰撞1相关的车辆
  41. 	var vehicles []Vehicle
  42. 	db.Debug().Joins("inner join vehicle_collisions vc on vehicles.id = vc.vehicle_id").Find(&vehicles, "vc.collision_id = ?", "1")
  43. }

代码从结构体的定义开始。

请注意字段Collisions上的Gorm注释。

在添加一些数据之后，查询应该非常简单：我们使用Joins方法从vehicle_collisions表中加载数据，并在Find方法中仅筛选出collision_id等于"1"的记录。

如果这对您有帮助，或者您需要其他信息，请告诉我！

英文:

As the question lacks some details, I tried to guess them. I hope that the answer provided is relevant to you! Let me present the code that is working on my side:

  1. package main
  3. import (
  4. 	"gorm.io/driver/postgres"
  5. 	"gorm.io/gorm"
  7. 	_ "github.com/lib/pq"
  8. )
  10. type Vehicle struct {
  11. 	Id         string
  12. 	Make       string
  13. 	Model      string
  14. 	Collisions []Collision `gorm:"many2many:vehicle_collisions"`
  15. }
  17. type Collision struct {
  18. 	Id        string
  19. 	Longitude float64
  20. 	Latitude  float64
  21. }
  23. func main() {
  24. 	dsn := "host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable"
  25. 	db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
  26. 	if err != nil {
  27. 		panic(err)
  28. 	}
  30. 	db.AutoMigrate(&Vehicle{})
  31. 	db.AutoMigrate(&Collision{})
  33. 	// add dummy data to the db
  34. 	collision := &Collision{"1", 4.4, 4.5}
  35. 	db.Create(collision)
  36. 	db.Create(&Collision{"2", 1.1, 1.5})
  37. 	db.Create(&Vehicle{Id: "1", Make: "ford", Model: "fiesta", Collisions: []Collision{*collision}})
  38. 	db.Create(&Vehicle{Id: "2", Make: "fiat", Model: "punto", Collisions: []Collision{*collision}})
  40. 	// get all vehicles for collision 1
  41. 	var vehicles []Vehicle
  42. 	db.Debug().Joins("inner join vehicle_collisions vc on vehicles.id = vc.vehicle_id").Find(&vehicles, "vc.collision_id = ?", "1")
  43. }

The code starts with the structs' definitions.
> Please note the Gorm annotation on the field Collisions.

After adding some data, the query should be pretty straightforward: we use the Joins method to load data from the table vehicle_collisions and in the Find method we filter out only records with collision_id equal to "1".
Let me know if this helps you or you need something else!

huangapple
  • 本文由 发表于 2022年3月24日 18:53:38
  • 转载请务必保留本文链接：https://go.coder-hub.com/71601147.html
  • go
  • go-gorm
  • postgresql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定