Database Inheritance in Go

Question

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?

Jonathan Hall · Accepted Answer · 2017-07-13 19:19:03Z

2

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;

answered Jul 13, 2017 at 19:19

Jonathan Hall

80.5k19 gold badges162 silver badges206 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JayD3e Over a year ago

The reason I considered it a go question, is I'm specifically looking for a way to do this within the confines of database/sql, where I can conditionally load it into different classes. Thanks for the explanation, I still might be able to use this strategy, but I just need to figure out how to conditionally load what it returns into different classes.

Jonathan Hall Over a year ago

@JayD3e Go doesn't have classes. But you to load it into different struct types, with a single query, you'll need some type that contains all the fields, then iterate over all the results, sorting them manually. It's probably much simpler to use separate queries. Also, a library like sqlx might come in handy for you.

Collectives™ on Stack Overflow

Database Inheritance in Go

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related