英文:
Building go-pg ORM query for many to many relationship
问题
我有3个表来表示我的多对多关系。客户,公司,公司客户。
公司:
- id
- 名称
客户:
- id
- 用户名
公司客户:
- id
- 客户id
- 公司id
现在我想运行的查询是选择所有具有company_id为1的客户。原始的SQL查询可能是这样的:
SELECT * FROM customers c INNER JOIN customers_companies cc ON c.id = cc.customer_id WHERE cc.company_id = 1
我尝试在go-pg中做类似的事情:
var customers []*Customer
s.DB.Model(&customers).Relation("Companies", func(q *orm.Query) (*orm.Query, error) {
return q.Where("company_id = ?", companyID), nil
}).Select()
英文:
I've got 3 tables to represent my many to many relationship. customers, companies, companies_customers.
companies:
- id
- name
customers:
- id
- username
companies_customers:
- id
- customer_id
- company_id
Now the query I want to run is to select all the customers with a company_id of 1. A raw SQL query could/might look something like this:
SELECT * FROM customers c INNER JOIN customers_companies cc ON c.id = cc.customer_id WHERE cc.company_id = 1
I've tried doing something like this in go-pg:
var customers []*Customer
s.DB.Model(&customers).Relation("Companies", func(q *orm.Query) (*orm.Query, error) {
return q.Where("company_id = ?", companyID), nil
}).Select()
答案1
得分: 4
在这种特殊情况下,你可以通过一些变通方法来执行这个特定的查询,我假设你有以下这些结构:
type Company struct {
TableName struct{} `sql:"companies"`
ID int64
Name string
Customers []*Customer `pg:",many2many:companies_customers"`
}
type Customer struct {
TableName struct{} `sql:"customers"`
ID int64
Username string
Companies []*Company `pg:",many2many:companies_customers"`
}
如果你只需要执行带有JOIN
的查询,你可以这样做:
var customers []*Customer
err := conn.Model(&customers).Column("customer.*").Join("inner join companies_customers cc on customer.id = cc.customer_id").Where("cc.company_id = ?", companyID).Select()
if err != nil {
// 错误处理
} else {
for _, customer := range customers {
fmt.Printf("Customer -> id: %d, username:%s \n", customer.ID, customer.Username)
}
}
这将生成以下SQL语句:
SELECT "customer".* FROM customers AS "customer" inner join companies_customers cc on customer.id = cc.customer_id WHERE (cc.company_id = 1)
但是,你也可以这样做:
var customers []*Customer
var company Company
err = conn.Model(&company).Column("Customers").Where("company.id = ?", companyID).Select()
if err != nil {
// 错误处理
} else {
customers = company.Customers
for _, customer := range company.Customers {
fmt.Printf("Customer -> id: %d, username:%s \n", customer.ID, customer.Username)
}
}
这段代码执行了两个查询:
SELECT "company"."id", "company"."name" FROM companies AS "company" WHERE (company.id = 1)
SELECT companies_customers.*, "customer".* FROM customers AS "customer" JOIN companies_customers ON (companies_customers."company_id") IN ((1)) WHERE ("customer"."id" = companies_customers."customer_id")
首先创建一个查询来获取company
的数据,然后获取该公司的所有客户。
英文:
In this particular case, you can do some workaround to perform this specific query, I suppose you have theses structures:
<!-- language: go -->
type Company struct {
TableName struct{} `sql:"companies"`
ID int64
Name string
Customers []*Customer `pg:",many2many:companies_customers"`
}
type Customer struct {
TableName struct{} `sql:"customers"`
ID int64
Username string
Companies []*Company `pg:",many2many:companies_customers"`
}
If you only need to perform the query with the JOIN
, you can do
<!-- language: go -->
var customers []*Customer
err := conn.Model(&customers).Column("customer.*").Join("inner join companies_customers cc on customer.id = cc.customer_id").Where("cc.company_id = ?", companyID).Select()
if err != nil {
// Error Handler
} else {
for _, customer := range customers {
fmt.Printf("Customer -> id: %d, username:%s \n", customer.ID, customer.Username)
}
}
This generate:
<!-- language: SQL -->
SELECT "customer".* FROM customers AS "customer" inner join companies_customers cc on customer.id = cc.customer_id WHERE (cc.company_id = 1)
But, you also can do the following:
<!-- language: go -->
var customers []*Customer
var company Company
err = conn.Model(&company).Column("Customers").Where("company.id = ?", companyID).Select()
if err != nil {
// error handler
} else {
customers = company.Customers
for _, customer := range company.Customers {
fmt.Printf("Customer -> id: %d, username:%s \n", customer.ID, customer.Username)
}
}
This code perform two queries:
SELECT "company"."id", "company"."name" FROM companies AS "company" WHERE (company.id = 1)
SELECT companies_customers.*, "customer".* FROM customers AS "customer" JOIN companies_customers ON (companies_customers."company_id") IN ((1)) WHERE ("customer"."id" = companies_customers."customer_id")
First create a query to fetch the data from company
, after that, fetch all the customers for that company.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论