构建 go-pg ORM 查询以处理多对多关系

huangapple go评论90阅读模式
英文:

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:&quot;companies&quot;`
	ID        int64
	Name      string
	Customers []*Customer `pg:&quot;,many2many:companies_customers&quot;`
}

type Customer struct {
	TableName struct{} `sql:&quot;customers&quot;`
	ID        int64
	Username  string
	Companies []*Company `pg:&quot;,many2many:companies_customers&quot;`
}

If you only need to perform the query with the JOIN, you can do

<!-- language: go -->

var customers []*Customer
err := conn.Model(&amp;customers).Column(&quot;customer.*&quot;).Join(&quot;inner join companies_customers cc on customer.id = cc.customer_id&quot;).Where(&quot;cc.company_id = ?&quot;, companyID).Select()
if err != nil {
	// Error Handler
} else {
	for _, customer := range customers {
		fmt.Printf(&quot;Customer -&gt; id: %d, username:%s \n&quot;, customer.ID, customer.Username)
	}
}

This generate:

<!-- language: SQL -->

SELECT &quot;customer&quot;.* FROM customers AS &quot;customer&quot; 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(&amp;company).Column(&quot;Customers&quot;).Where(&quot;company.id = ?&quot;, companyID).Select()
if err != nil {
	// error handler
} else {
    customers = company.Customers
	for _, customer := range company.Customers {
		fmt.Printf(&quot;Customer -&gt; id: %d, username:%s \n&quot;, customer.ID, customer.Username)
	}
}

This code perform two queries:

SELECT &quot;company&quot;.&quot;id&quot;, &quot;company&quot;.&quot;name&quot; FROM companies AS &quot;company&quot; WHERE (company.id = 1)
SELECT companies_customers.*, &quot;customer&quot;.* FROM customers AS &quot;customer&quot; JOIN companies_customers ON (companies_customers.&quot;company_id&quot;) IN ((1)) WHERE (&quot;customer&quot;.&quot;id&quot; = companies_customers.&quot;customer_id&quot;)

First create a query to fetch the data from company, after that, fetch all the customers for that company.

huangapple
  • 本文由 发表于 2017年1月9日 10:12:03
  • 转载请务必保留本文链接:https://go.coder-hub.com/41540051.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定