Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- select p.*,
- array(select url from image i where p.prod_id = i.prod_id) as images,
- (select v from prod_variation v where v.prod_id = p.prod_id limit 1) as variants
- from product p limit 1;
- select p.*,
- array(select url from image i where p.prod_id = i.prod_id) as images,
- array(select v from prod_variation v where v.prod_id = p.prod_id) as variants
- from product p limit 1;
- Images []string `sql:",array"`
- Variants ProdVariant `sql:"composite:prod_variation"`
- Variants []ProdVariant `sql:",array,composite:prod_variation"`
- CREATE TABLE product (
- prod_id integer primary key,
- name text,
- price real
- );
- CREATE TABLE prod_variation (
- aid integer primary key,
- prod_id integer references product(prod_id),
- stock_count integer,
- color text
- );
- CREATE TABLE image (
- prod_id integer references product(prod_id),
- url text,
- primary key (url,prod_id)
- );
- CREATE INDEX on image (prod_id);
- CREATE INDEX on prod_variation (prod_id);
- INSERT INTO product(prod_id, name, price) VALUES (1, 'Prod 1', 10.0);
- INSERT INTO image(prod_id, url) VALUES (1, 'http://url.com/p1-1.jpg');
- INSERT INTO image(prod_id, url) VALUES (1, 'http://url.com/p1-2.jpg');
- INSERT INTO prod_variation(aid,prod_id, stock_count, color) VALUES (11, 1, 5, 'red');
- INSERT INTO prod_variation(aid,prod_id, stock_count, color) VALUES (12, 1, 7, 'blue');
- INSERT INTO product(prod_id,name, price) VALUES (2, 'Prod 2', 20.0);
- INSERT INTO image(prod_id, url) VALUES (2, 'http://url.com/p2-1.jpg');
- INSERT INTO image(prod_id, url) VALUES (2, 'http://url.com/p2-2.jpg');
- INSERT INTO prod_variation(aid,prod_id, stock_count, color) VALUES (21, 2, 10, 'black');
- INSERT INTO prod_variation(aid,prod_id, stock_count, color) VALUES (22, 2, 15, 'white');
- package main
- import (
- "log"
- "github.com/go-pg/pg"
- _ "github.com/go-pg/pg/orm"
- "github.com/kylelemons/godebug/pretty"
- )
- type Product struct {
- tableName struct{} `pg:",discard_unknown_columns"sql:"product"`
- ProdId uint64 `sql:",pk"`
- Name string
- Price float32 `sql:",notnull"`
- Images []string `sql:",array"`
- Variants ProdVariant `sql:"composite:prod_variation"`
- }
- type ProdVariant struct {
- tableName struct{} `pg:",discard_unknown_columns"sql:"prod_variation"`
- AID string `sql:",pk"`
- ProdId string `pg:"fk:prod"`
- StockCount int `sql:",notnull"`
- Color string
- }
- type ProductImage struct {
- tableName struct{} `pg:",discard_unknown_columns"sql:"image"`
- ProdId uint64 `pg:"fk:prod"`
- URL string
- }
- func selectProducts(tx *pg.Tx) error {
- queryFormat := `select p.*,
- array(select url from image i where p.prod_id = i.prod_id) as images,
- (select v from prod_variation v where v.prod_id = p.prod_id limit 1) as variants
- from product p limit 1;`
- Res := []Product{}
- if _, err := tx.Query(&Res, queryFormat); err != nil {
- return err
- }
- log.Printf("Select result: %s", pretty.Sprint(&Res))
- return nil
- }
- func main() {
- opt := pg.Options{Addr: "127.0.0.1:5432", User: "tester", Password: "test12345", Database: "test"}
- conn := pg.Connect(&opt)
- if err := conn.RunInTransaction(selectProducts); err != nil {
- log.Fatalf("Request failed [product]: %s", err)
- }
- }
- 2019/01/25 03:15:13 Select result: [{ProdId: 1,
- Name: "Prod 1",
- Price: 10,
- Images: ["http://url.com/p1-1.jpg",
- "http://url.com/p1-2.jpg"],
- Variants: {AID: "11",
- ProdId: "1",
- StockCount: 5,
- Color: "red"}}]
Add Comment
Please, Sign In to add comment