CODEWITHSUNDEEP
databasepostgresqlgodatabase-design
JayD3e
JayD3e

Reputation: 2217

Database Inheritance in Go

I have a situation where I have a base type, let's call it the Pet type (not the real type but an apt example), and 6 underlying variants of this type that all could have their own individual fields. Let's say they are each different types of pets, like Dog, Cat, Bird, etc. Right now they are all organized in the same table called pets, but I think I would benefit from breaking each type out into their own table(dogs, cats, birds, etc) and then inheriting the fields from the main pets table. To be clear, each of my child tables have unique fields that don't apply to any of the other subtypes.

I'm using database/sql, and I query for data like so:

var getPetsQuery = fmt.Sprintf(`SELECT %v FROM pets`)

func (tx *Tx) GetPets() (pets []*model.Pet, err error) {
    return tx.petQueryRow(getPetsQuery)
}

func (tx *Tx) petQueryRow(query string, args ...interface{}) (pets []*model.Pet, err error) {
    rows, err := tx.Query(query, args...)
    if err != nil {
        return nil, errors.Stacktrace(PetQueryFailedErr(err))
    }
    defer rows.Close()

    for rows.Next() {
        pet := &model.Pet{}
        err = rows.Scan(
            &pet.Id,
            &pet.Uid,
            &pet.Created,
            &pet.Type,
            &pet.LegCount,
        )
        results = append(results, pet)
    }

    err = rows.Err()
    if err != nil {
        return nil, errors.Stacktrace(PetQueryFailedErr(err))
    }
    return results, err
}

My question is, using this system, if I wanted to query for a list of pets of all types, how would I go about loading the extra data from each individual subtype table in a single query? Or would I need to load a list of pets first and then query for the additional data individually?

Upvotes: 0

Views: 250

Answers (1)

Jonathan Hall
Jonathan Hall

Reputation: 79754

This isn't a question about Go, but rather one about Postgres. The good news is, Postgres should let you do what you want with the use of UNION. You'll just need to UNION all of your tables together into a single query.

SELECT id,
       uid,
       created,
       legcount,
       wingcount,
       NULL AS fincount
FROM snakes
UNION
SELECT id,
       uid,
       created,
       legcount,
       NULL AS wingcount,
       NULL AS fincount
FROM dogs
SELECT id,
       uid,
       created,
       NULL AS legcount,
       NULL AS wingcount,
       fincount
FROM fish

The key is that for a UNION to work, each subquery must have the exact same columns, so you'll need to fill in NULL columns (as demonstrated with NULL AS fincount, etc).

If you want to perform a query on this, you can wrap it all in a subquery for convenience:

SELECT * FROM (
    SELECT id,
    <snip>
    UNION
    SELECT id,
    <snip>
)
WHERE legcount = 3;

Upvotes: 2

Related Questions