Multi select statement

General
#1

Hi, I have the table “users”, “addresses”, and “hobbies”. Addresses and hobbies have a foreing key on users.

I want to retrieve a user with all his adresses and all his hobbies.
I could do this using three different select statements and joining the data in my go program, and filling a User struct that looks like this.

type User struct {
Name string
Addresses []Address
Hobbies []Hobbie
}

type Hobbie struct {
Name string
}

type Address struct {
Street string
City string
}

But there will be 3 roundtrips to the database which I’d like to avoid (in the real case there will be a lot more than 3).
Is there a way to do the whole query in one trip? Is there any recommended approach?

PD: I don’t want to use json in a string field, need the data in the other tables for integral referentiality.

#2

Hi Marcos,

It’ll depend on the details of your schema, but you should be able to retrieve all the data in one round trip by doing a join along the lines of:

SELECT users.*, addresses.*, hobbies.*
FROM users
INNER JOIN addresses ON users.id = addresses.userid
INNER JOIN hobbies ON users.id = hobbies.userid
WHERE users.id = 'foo';
#3

Hi Alex, thanks for your quick answer.
I thought of that way, but if I see two reasons that don’t quite satisfy me, please correct me if i’m wrong.

First, I would get a plain table with a lot of repeated data where rows would increase exponentially. For one user with 3 addresses and 5 hobbies and will get 1 * 3 * 5 = 15 rows.

Marcos Street1 Neverland Basketball
Marcos Street1 Neverland Guitar
Marcos Street1 Neverland Footbal
Marcos Street1 Neverland Snowboard
Marcos Street1 Neverland Programming
Marcos Street2 Kinston Basketball
Marcos Street2 Kinston Guitar
Marcos Street2 Kinston Footbal
Marcos Street2 Kinston Snowboard
Marcos Street2 Kinston Programming
Marcos Street3 New York Basketball
Marcos Street3 New York Guitar
Marcos Street3 New York Footbal
Marcos Street3 New York Snowboard
Marcos Street3 New York Programming

Second, to put that data into my struct I’ll have to do some hackish code to analyze that from the fifteen rows, there is only 1 user, 3 addresses and 5 hobbies. Doesnt’t look like a “Best Practice” solution.

¿What to you think?

#4

I don’t know that I would call the code to process that output “hackish” – SQL is more about returning you the data you need than about formatting it nicely – but there may be a better way. The next thing that coms to my mind is using an aggregator on the values from the other tables, like

SELECT users.*, ARRAY_AGG(addresses.street), ARRAY_AGG(hobbies.name) ...

Although that’ll still have the cross-product effect, just not on the users fields.

#5

In many languages/client libraries, you can submit multiple queries at once to avoid these round trips. For example, in Go, you can submit a semicolon-delimited sequence of queries, and use NextResultSet() to read all the results. There’s an example in the database/sql docs. I haven’t actually tried this with CockroachDB, but it should work.

#6

My first suggestion would be to do what Ben suggests, issue multiple statements separated by semicolons and use the client API to retrieve the various result sets in sequence.

If you are adamant to use a single query this would work:

select name,
       array(select street from addresses where userid = 'foo'),
       array(select name from hobbies where userid = 'foo')
  from users
 where id = 'foo'

or, if you want to search users by name:

select name,
       array(select a.street from addresses a, users u where u.name = 'foo' and a.userid = u.id),
       array(select h.name from hobbies h, users u where u.name = 'foo' and h.userid = u.id)
  from users
 where name = 'foo'
#7

I didn’t know that method existed, it’s actually what I was looking for.
Do you have any idea if it is possible to use it with GORM? I’ve read the documentation but couldn’t find anything…

#8

Grepping the GORM repo for NextResultSet doesn’t find anything, so probably not. I’d raise an issue in the GORM repo to see what its author has to say.

#9

Done https://github.com/jinzhu/gorm/issues/1540

Thanks a lot to everyone.