CODEWITHSUNDEEP
sqlpostgresqlgo
bli00
bli00

Reputation: 2787

Golang SQL table JOIN into slice field

Consider the following tables

CREATE TABLE foo (
  id INT PRIMARY KEY
)

CREATE TABLE bar (
  id INT PRIMARY KEY
)

CREATE TABLE foo_bar_members (
  foo_id INT NOT NULL REFERENCES foo(id) ON DELETE CASCADE ON UPDATE CASCADE,
  bar_id INT NOT NULL REFERENCES bar(id) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (foo_id, bar_id)
)

foo_bar_members is a relations table that connects foo and bar. View foo as the parent of bar. And I have the following Go struct:

type Foo struct {
  ID     int `db:"id"`
  BarIDs []int
}

BarIDs is a slice of bar.id that is associated with this foo by foo.id. I want to query for something like this:

SELECT * FROM foo f INNER JOIN foo_bar_members fbm ON f.id = fbm.foo_id WHERE f.id = $1

But this query is just an example. Obvious this won't scan into Foo.BarIDs. I could always do two separate queries but I'm wondering if there are better ways. I'm using sqlx.

Upvotes: 0

Views: 2540

Answers (1)

mkopriva
mkopriva

Reputation: 38233

The SQL, the left join here will also return foos that don't have no bars associated.

select
    foo.id
    , array_agg(bar.id)
from foo
left join foo_bar_members m on m.foo_id = foo.id
where foo.id = $1
group by foo.id

If you care only about foos that have bars, then there's no need for the join actually.

select
    m.foo_id
    , array_agg(m.bar_id)
from foo_bar_members m
where m.foo_id = $1
group by m.foo_id

The go code:

const sql = "<one of the queries from above>"

f := new(Foo)
if err := db.QueryRow(sql, 123).Scan(&f.ID, pq.Array(&f.BarIDs)); err != nil {
    return err
}

pq.Array is a function that returns a value that implements the sql.Scanner and driver.Valuer interfaces and the implementation knows how to scan a postgres array into a go slice or turn a go slice into a postgres array.

I'm not familiar with sqlx so it's possible it itself provides some feature that allows you to scan arrays and therefore the use of pq.Array might be unnecessary. Maybe someone who know better will eventually provide their version of the solution.

Upvotes: 1

Related Questions