ORM: Table relationships

Introduction

Bun can help you join and query other tables if you are using one of the 4 supported table relations:

For example, you can define Author belongs to Book relation:

1. type Book struct {
2. ID         int64
3. AuthorID int64
4. Author     Author `bun:"rel:belongs-to,join:author_id=id"`
5. }

7. type Author struct {
8. ID int64
9. }

And then use Relation method to join tables:

1. err := db.NewSelect().
2. Model(book).
3. Relation("Author").
4. Where("id = 1").
5. Scan(ctx)

1. SELECT
2. "book"."id", "book"."title", "book"."text",
3. "author"."id" AS "author__id", "author"."name" AS "author__name"
4. FROM "books"
5. LEFT JOIN "users" AS "author" ON "author"."id" = "book"."author_id"
6. WHERE id = 1

You can query from parent the child and vice versa in an has-one/belongs-to relation:

1. type Profile struct {
2. ID     int64 `bun:",pk"`
3. Lang   string
4. UserID int64
5. User *User `rel:"belongs-to"`
6. }

8. type User struct {
9. ID      int64 `bun:",pk"`
10. Name    string
11. Profile *Profile `bun:"rel:has-one"`
12. }

14. err := db.NewSelect().
15. Model(&user).
16. Where("id = 1").
17. Relation("Profile").
18. Scan(ctx)

20. err := db.NewSelect().
21. Model(&profile).
22. Where("id = 1").
23. Relation("User").
24. Scan(ctx)

To select only book ID and the associated author id:

1. err := db.NewSelect().
2. Model(book).
3. Column("book.id").
4. Relation("Author", func (q *bun.SelectQuery) *bun.SelectQuery {
5. return q.Column("id")
6. }).
7. Where("id = 1").
8. Scan(ctx)

1. SELECT "book"."id", "author"."id" AS "author__id"
2. FROM "books"
3. LEFT JOIN "users" AS "author" ON "author"."id" = "book"."author_id"
4. WHERE id = 1

To select a book and join the author without selecting it:

1. err := db.NewSelect().
2. Model(book).
3. Relation("Author", func (q *bun.SelectQuery) *bun.SelectQuery {
4. return q.Exclude("*")
5. }).
6. Where("id = 1").
7. Scan(ctx)

1. SELECT "book"."id"
2. FROM "books"
3. LEFT JOIN "users" AS "author" ON "author"."id" = "book"."author_id"
4. WHERE id = 1

To simulate INNER JOIN instead of LEFT JOIN:

1. err := db.NewSelect().
2. Model(book).
3. Relation("Author").
4. Where("id = 1").
5. Where("author.id IS NOT NULL").
6. Scan(ctx)

Has one relation

To define a has-one relationship, add bun:"rel:has-one" tag to the field. In the following exampleORM relations - 图1open in new window, we have User model that has one Profile model.

1. // Profile belongs to User.
2. type Profile struct {
3. ID     int64 `bun:",pk"`
4. Lang   string
5. UserID int64
6. }

8. type User struct {
9. ID      int64 `bun:",pk"`
10. Name    string
11. Profile *Profile `bun:"rel:has-one,join:id=user_id"`
12. }

You can specify multiple join columns, for example, join:id=user_id,join:vendor_id=vendor_id.

Belongs to relation

To define a belongs-to relationship, you need to add bun:"rel:belongs-to" tag to the field. In the the following exampleORM relations - 图2open in new window we define Profile model that belongs to User model.

1. type Profile struct {
2. ID   int64 `bun:",pk"`
3. Lang string
4. }

6. // User has one profile.
7. type User struct {
8. ID        int64 `bun:",pk"`
9. Name      string
10. ProfileID int64
11. Profile   *Profile `bun:"rel:belongs-to,join:profile_id=id"`
12. }

You can specify multiple join columns, for example, join:profile_id=id,join:vendor_id=vendor_id.

Has many relation

To define a has-many relationship, add bun:"rel:has-many" to the field. In the following exampleORM relations - 图3open in new window, we have User model that has many Profile models.

1. type Profile struct {
2. ID     int64 `bun:",pk"`
3. Lang   string
4. Active bool
5. UserID int64
6. }

8. // User has many profiles.
9. type User struct {
10. ID       int64 `bun:",pk"`
11. Name     string
12. Profiles []*Profile `bun:"rel:has-many,join:id=user_id"`
13. }

You can specify multiple join columns, for example, join:id=user_id,join:vendor_id=vendor_id.

Polymorphic has many relation

You can also define a polymorphic has-many relationship by using type virtual column and polymorphic option.

In the following exampleORM relations - 图4open in new window, we store all comments in a single table but use trackable_type column to save the model table to which this comment belongs to.

1. type Article struct {
2. ID   int64
3. Name string

5. Comments []Comment `bun:"rel:has-many,join:id=trackable_id,join:type=trackable_type,polymorphic"`
6. }

8. type Post struct {
9. ID   int64
10. Name string

12. Comments []Comment `bun:"rel:has-many,join:id=trackable_id,join:type=trackable_type,polymorphic"`
13. }

15. type Comment struct {
16. TrackableID   int64  // Article.ID or Post.ID
17. TrackableType string // "article" or "post"
18. Text          string
19. }

Many to many relation

To define a many-to-many relationship, add bun:"m2m:order_to_items" to the field. You also need to define two has-one relationships on the intermediary model and manually register the model (db.RegisterModel).

In the following exampleORM relations - 图5open in new window, we have Order model that can have many items and each Item can be added to multiple orders. We also use OrderToItem model as an intermediary table to join orders and items.

1. func init() {
2. // Register many to many model so bun can better recognize m2m relation.
3. // This should be done before you use the model for the first time.
4. db.RegisterModel((*OrderToItem)(nil))
5. }

7. type Order struct {
8. ID    int64  `bun:",pk"`
9. // Order and Item in join:Order=Item are fields in OrderToItem model
10. Items []Item `bun:"m2m:order_to_items,join:Order=Item"`
11. }

13. type Item struct {
14. ID int64 `bun:",pk"`
15. }

17. type OrderToItem struct {
18. OrderID int64  `bun:",pk"`
19. Order   *Order `bun:"rel:belongs-to,join:order_id=id"`
20. ItemID  int64  `bun:",pk"`
21. Item    *Item  `bun:"rel:belongs-to,join:item_id=id"`
22. }