Join usage

Join usage

  • Join(string,interface{},string)

The first param is join type, you can use any keyword of your database type, the second could accept two forms. One is the table’s name as a string type, another is []string{"tablename", "alias"} which size is two. The third is join condition.

Below we will show the usage by example:

Suppose we have two tables, one is user, another is group and every user only belong to one group. So we can define the structs.

  1. type Group struct {
  2.     ID int64
  3.     Name string
  4. }
  1. type User struct {
  2.     ID int64
  3.     Name string
  4.     GroupId int64 `xorm:"index"`
  5. }

If we want to query all user and his group name. how to do via xorm? The code is below:

  1. type UserGroup struct {
  2.     User `xorm:"extends"`
  3.     Name string
  4. }
  6. func (UserGroup) TableName() string {
  7.     return "user"
  8. }
  10. var users []UserGroup
  11. err := engine.Join("INNER", "group", "group.id = user.group_id").Find(&users)

Here we composite User and string type as a new struct UserGroup. And we add a xorm tag extends on User. Of course, we can also use Sql function to query the data.

  1. var users []UserGroup
  2. err := engine.SQL("select user.*, group.name from user, group where user.group_id = group.id").Find(&users)

And then we find we also need Group’s id because we will put a link on the web page. So we can change the UserGroup, now we composite two struct as a new struct. The code is below:

  1. type UserGroup struct {
  2.     User `xorm:"extends"`
  3.     Group `xorm:"extends"`
  4. }
  6. func (UserGroup) TableName() string {
  7.     return "user"
  8. }
  10. var users []UserGroup
  11. err := engine.Join("INNER", "group", "group.id = user.group_id").Find(&users)

Notice the sequence of composite of User and Group because User has Id and Grpup has Id two. There is a rule we should obey:

The sequence of struct field SHOULD be the same as the sequence of select columns.

And another point should be noticed that if you use the composited struct on html template files. You maybe have to use {{.User.Id}} or {{.Group.Id}} but not {{.Id}}

And for more join tables it also ok.

  1. type Type struct {
  2.     Id int64
  3.     Name string
  4. }
  6. type UserGroupType struct {
  7.     User `xorm:"extends"`
  8.     Group `xorm:"extends"`
  9.     Type `xorm:"extends"`
  10. }
  12. var users []UserGroupType
  13. engine.Table("user").Join("INNER", "group", "group.id = user.group_id").
  14.     Join("INNER", "type", "type.id = user.type_id").
  15.     Find(&users)

Of course , we could use Where, And, Or as condition, or use the second param of Find as condition.

  1. engine.Table("user").Join("INNER", "group", "group.id = user.group_id").
  2.     Join("INNER", "type", "type.id = user.type_id").
  3.     Where("user.name like ?", "%"+name+"%").Find(&users, &User{Name:name})

Finally, we can use alias on the process. For example:

  1. engine.Table("user").Alias("u").
  2.     Join("INNER", []string{"group", "g"}, "g.id = u.group_id").
  3.     Join("INNER", "type", "type.id = u.type_id").
  4.     Where("u.name like ?", "%"+name+"%").Find(&users, &User{Name:name})