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()
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 \n", 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 \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.