建立多对多关系的go-pg ORM查询

Question

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()

Answer 1

In this particular case, you can do some workaround to perform this specific query, I suppose you have theses structures:

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

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 
", customer.ID, customer.Username)
    }
}

This generate:

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:

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 
", 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.