英文:
What other attribute should i add for my Gorm table?
问题
我的目标是简单地连接三个表(对不起,我在SQL方面的知识有限)
我正在构建一个类似于Patreon的基于社区的平台,每个社区可以有不同的计划(免费、付费)
示例代码:
type User struct {
Communities []Community `gorm:"many2many:user_communities" json:"communities"`
}
type Community struct {
UserID `json:"userID"`
Users []User `gorm:"many2many:user_communities" json:"users"`
Plans []Plan `json:"plans"`
// 如何添加另一个属性来检查具有不同计划的用户?
}
type Plan struct {
CommunityID uint `json:"communityID"`
Name string `json:"name"`
Price string `json:"price"`
}
根据上面的模型:
- 一个用户可以加入多个社区
- 一个社区可以有多个用户
- 一个用户可以拥有一个社区(创建者)
- 对于每个社区,一个用户可以加入不同的计划(免费、付费)
我更关注如何解决第4个问题
所以问题是,我应该为我的社区表添加什么来区分以免费计划或付费计划加入的用户?
因为我只能查询属于特定社区的用户,而不能查询属于该社区的用户基于免费或付费计划。
示例查询
// 查找该用户是否属于某个社区
if err := database.Mysql.Table("users u").Joins("JOIN user_communities uc ON u.id = uc.user_id").Where("u.id = ? AND uc.community_id = ?", userID, communityID).Select("1").Scan(&found).Error; err != nil {
c.JSON(http.StatusBadRequest, gin.H{"error": "error happened"})
return
}
英文:
My goal is to simply join three tables (Sorry my knowledge in sql is limited)
I'm building a community-based platform like Patreon where each community can have different plans (Free, Paid)
Example Code:
type User struct {
Communities []Community `gorm:"many2many:user_communities" json:"communities"`
}
type Community struct {
UserID `json:"userID"`
Users []User `gorm:"many2many:user_communities" json:"users"`
Plans []Plan `json:"plans"`
// How do i add another attirbute to check for user with different plan?
}
type Plan struct {
CommunityID uint `json:"communityID"`
Name string `json:"name"`
Price string `json:"price"`
}
Based on the model above
- One user can join many communities
- One community can have many users
- One user can own a community (creator)
- and for each community, one user can join for the different plans (Free, Paid)
I'm more focused on how to solve number 4
So the question here is what should I add for my community table to differentiate between the user who joins as Free plan or Paid plan?
because I can only query users who belong to a specific community not users who belong to that community based on a Free or Paid plan.
Example query
// Find whether that user belongs to a community
if err := database.Mysql.Table("users u").Joins("JOIN user_communities uc ON u.id = uc.user_id").Where("u.id = ? AND uc.community_id = ?", userID, communityID).Select("1").Scan(&found).Error; err != nil {
c.JSON(http.StatusBadRequest, gin.H{"error": "error happened"})
return
}
答案1
得分: 1
一种方法是将计划作为多对多表的一部分。
我不太了解gorm,但在普通的SQL中,可以这样写。
模式:
create table appUser (
id serial not null,
name text not null,
primary key (id)
);
create table Community (
id serial not null,
owner_id int not null,
name text not null,
primary key (id),
foreign key (owner_id) references appUser(id)
);
create table plan (
id serial not null,
community_id int not null,
name text not null,
price float not null,
primary key (id),
foreign key (community_id) references Community(id)
);
create table UserCommunity (
user_id int not null,
community_id int not null,
plan_id int not null,
primary key (user_id, community_id),
foreign key (plan_id) references plan(id)
);
用法:
insert into appUser (name) values ('maria'), ('jose'), ('clara');
insert into Community (name, owner_id) values ('community1', 1);
insert into plan (community_id, name, price) values
(1, 'free', 0.0), (1, 'paid', 10.0);
insert into UserCommunity (user_id, community_id, plan_id) values
(2, 1, 1), (3, 1, 2);
select
appUser.name as "user",
community.name as "community",
plan.name as "plan"
from appUser
join UserCommunity on appUser.id = UserCommunity.user_id
join Community on Community.id = UserCommunity.community_id
join plan on plan.id = UserCommunity.plan_id
where plan.name = 'paid';
Fiddle: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=bd37832969396a40944f40ef17f18465
英文:
One way could be to make the plan part of the many-to-many table.
I don't know gorm really, but in plain SQL this could look something like this.
schema:
create table appUser (
id serial not null,
name text not null,
primary key (id)
);
create table Community (
id serial not null,
owner_id int not null,
name text not null,
primary key (id),
foreign key (owner_id) references appUser(id)
);
create table plan (
id serial not null,
community_id int not null,
name text not null,
price float not null,
primary key (id),
foreign key (community_id) references Community(id)
);
create table UserCommunity (
user_id int not null,
community_id int not null,
plan_id int not null,
primary key (user_id, community_id),
foreign key (plan_id) references plan(id)
);
usage:
insert into appUser (name) values ('maria'), ('jose'), ('clara');
insert into Community (name, owner_id) values ('community1', 1);
insert into plan (community_id, name, price) values
(1, 'free', 0.0), (1, 'paid', 10.0);
insert into UserCommunity (user_id, community_id, plan_id) values
(2, 1, 1), (3, 1, 2);
select
appUser.name as "user",
community.name as "community",
plan.name as "plan"
from appUser
join UserCommunity on appUser.id = UserCommunity.user_id
join Community on Community.id = UserCommunity.community_id
join plan on plan.id = UserCommunity.plan_id
where plan.name = 'paid';
Fiddle: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=bd37832969396a40944f40ef17f18465
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论