Support association using join table such as Many to Many

[Fs02]

Idea 2

Example

type Channel struct {
	ID   int
	Name string

	// mapped to singular version of field name defined in "db" (subscriber) inside through association.
	// impicitly trigger two preload: Preload("subscriptions") and Preload("subscriptions.subscriber")
	// the result than flattened and mapped to subscribers.
	Subscribers   []User         `db:"subscribers" through:subscriptions"`
	Subscriptions []Subscription `ref:"id" fk:"channel_d"`
}

type User struct {
	ID   int
	Name string

	Subscriptions []Subscription
	Channels      []Channel `through:subscriptions"`

	// self-referencing needs two intermediate reference to be set up.
	// trigger Preload("user_followings") and Preload("user_followings.following")
	Followings     []User   `through:"user_followings"` // map to following field
	UserFollowings []Follow `ref:"id" fk:"follower_id"`

	// trigger Preload("user_followers") and Preload("user_follwers.follower")
	UserFollowers []Follow `ref:"id" fk:"following_id"`
	Followers     []User   `through:"user_followers"` // map to follower field
}

type Follow struct {
	Follower    User
	FollowerID  int `db:",primary"`
	Following   User
	FollowingID int  `db:",primary"`
	Accepted    bool // this way, it may contains additional data
}

type Subscription struct {
	ID           int `db:",primary"`
	Subscriber   User
	SubscriberID int
	Channel      Channel
	ChannelID    int
	CreatedAt    time.Time
	UpdatedAt    time.Time
}

Specifications

• Join association is defined using tag calledthrough and doesn't support nested through.
• It's a read only association (all modification will be ignored)
• Every preload of has through assoc will implicitly trigger two preload, the first one is the association defined by through tag, the second one is association inside through field that has the singular name as has through field. The result then flattened and mapped to the final association.
• Preload has many through
• Preload has one through

Merit/Demerit

(+) Support has one and has many through
(+) Can be made read only and still accessible for update.
(+) We can have metadata on join table.
(-) Require additional association defined (especially verbose for self referencing association).

Idea 1

Example:

// Table subscription_users: user_id(int), subscription_id(int)
// Table followers: followed_id(int), following_id(int)

type Subscription struct {
	ID   int
	Name string

	// 1. basic declaration:
	// subscription:id <- subscription_id:subscription_users:user_id -> user:id
	Users []User `ref:"id:subscription_id" fk:"id:user_id" through:"subscription_users"`
}

type User struct {
	ID   int
	Name string

	// 2. back ref
	//    user:id <- user_id:subscription_users:subscription_id -> subscriptions:id
	Subscriptions []Subscription `ref:"id:user_id" fk:"id:subscription_id" through:"subscription_users"`

	// 3. omitting ref and fk tag, will be guessed based on table name:
	//    user:id <- user_id:subscription_users:subscription_id -> subscriptions:id (the same as 2)
	// Subscriptions []Subscription through:"subscription_users"`

	// 4. Self-referencing many to many
	Followers  []User `ref:"id:following_id" fk:"id:follower_id" through:"followers"`
	Followings []User `ref:"id:follower_id" fk:"id:following_id" through:"followers"`
}

Specifications

• Join table is defined using tag calledthrough.
• subscription:id <- subscription_id:subscription_users:user_id -> user:id
  is declared as:
  ref:"id:subscription_id" fk:"id:user_id" through:"subscription_users"
• ref and fk can be completely omitted, and will be guessed as many to many when through tag is available, otherwise has many rule applied.
• Preloading support.
• Insert/Update support Edit: to complex and to magic to implement

Merit/Demerit

(+) Can implement many to many without additional struct.
(-) Doesn't work for has one through.
(-) Can't be made fully readonly(can update but only the join data).

[bickyeric]

hello @Fs02, can you assign me for Preloading support ?
the task is like Preloading Association but for Many to Many relations right?
I'm glad to join the development

[Fs02]

Thanks, glad to have some help as well 😄

[Fs02]

@bickyeric do you have any thought with the current design?

The current design might look similar with activerecord has many through and has_one_through, but it's not since it's not required to have the intermediary association defined inside the struct. I'm thinking maybe we should follow this pattern?

Another consideration of why the above pattern is better because it's still provide a way to update the association when the parent is saved through a table that properly defined inside our code (not just a ghost join table).
It's also give us the ability to preload has one through intermediary table as well.

I've also decided to disable autosave feature for has many through association, since it's just to much magic and it's difficult to provide one consistent and predictable behavior for all association type. and I think it's better disable autosave association by default and enabled it explicitly when needed using struct tag, and this structtag will not be supported for has many/one through.

Edit: See Idea 2

[bickyeric]

@Fs02 I have a struggle implementing Idea 2 on how to decide which field on intermediary struct that is used to reference the association
for User and Channel association we can assume Subscription.UserID used to reference User & Subscription.ChannelID used to reference Channel since ref & fk tag is not defined on User.Subscription.

but I confused with self reference association, for Followers association we will use Follow.FollowerID to reference parent User as it defined on User.Followeds, but how we decide which field on intermediary struct is used to reference child User? are we going to use what is defined on another field with same signature on parent struct (User.Follows with ref: id & fk: following_id)? what if there is more than 2 field with same signature on parent struct?

[Fs02]

@bickyeric after looking back to the design, you are right, the problem is we cannot infer which field inside the immediate table we should use to point to the next association.

Therefore, I've updated the design, now all the association has to be fully defined, and it will trigger two implicit preload, after that the result will be flattened and mapped to final assoc. let me know if it still confusing?

[Rudis1261]

Would like to add my +1 for this feature.