Search code examples
mysqlgosqlx

Scan items from a query with multiple columns of the same name

I have a query like

myQuery := "SELECT DISTINCT table1.id, table1.active, table2.active FROM table1 INNER JOIN table 2 ON table1.id = table2.foreign_id AND table2.active = true

and I'm working with MySQL.

I have to select table2.active to avoid the SELECT list; this is incompatible with DISTINCT in my sql error.

I'm using sqlx but I figure the problems lie deeper. I can't scan the result into a target object with err := conn.Unsafe().Select(dest, myQuery), with dest of type interface{} being a pointer to an interface slice, because of two aspects:

  1. As far as I can tell the Columns from sql.Rows don't hold the qualifiers in their names, so I've gotten basically two Columns whose names are just active, without table1 or table2. This means, table2.active overrides table1.active when scanned into the target structure.

  2. Let's also assume that table1.active has another type than table2.active (which doesn't make much sense in this simple example but it does in my real scenario). I'd get an error because the table2.active value would have the wrong type to be scanned into my target object.

Since I don't know the nature of the structs in dest, going over the Rows directly doesn't help much since it feels impossible to write those values into the dest struct manually. I can get all field names of the target structs with some reflections and put them into the SELECT part of the query (that's what I'm doing actually), but not really the other way around.

I'm also wondering whether I could make the query above a subquery, wrapping it into an outer query without all the misleading columns. But a query like SELECT id, active FROM table1 WHERE id IN ([query above]) wouldn't work with all those other columns returned as well. Maybe I can gather the ids manually and run a second query, but that's probably rather ineffective.

Solution

  • Strawberry just gave the solution in the comments: Even columns required in the SELECT clause to prevent the SELECT list; this is incompatible with DISTINCT in my sql error error can be aliased. So basically

    SELECT DISTINCT table1.id, table1.active, table2.active as active2 FROM table1 INNER JOIN table 2 ON table1.id = table2.foreign_id AND table2.active = true

    does the trick - the MySQL error will disappear, but that column does not get in the way of the scanner!