[Question] How to differentiate between fields of the same name coming from different tables in a JOIN ?

[andrei-dascalu]

Consider the schema:

CREATE TABLE `Account` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Relation` bigint(20) unsigned DEFAULT NULL,
  `Company` varchar(50) DEFAULT NULL,
  `MainAddressId` bigint(20) unsigned not null,
  `AlternateAddressId` bigint(20) default null,
  `CreateDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `ModifyDate` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `Locations` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Address` VARCHAR(200) NOT NULL,
  `City` VARCHAR(80) default null,
  `CreateDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `ModifyDate` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`)
);

and queries

-- name: FindAccountById :one
SELECT sqlc.embed(a), sqlc.embed(locMain), sqlc.embed(c)
FROM Account a
LEFT JOIN Locations locMain ON a.MainAddressId = locMain.Id
LEFT JOIN Locations locAlt ON a.AlternateAddressId = locAlt.Id
WHERE a.Id = ? LIMIT 1;

when generating the code (mysql driver), the generated method FindAccountById will be returning a custom FindAccountByIdRow where the Id fields coming from different tables are named like Id, Id_2 and Id_3.

I'm not sure if there's something that can be currently done to easily differentiate or have the generated code use table aliases to tell the tables apart (so that the resulting struct elements would be called AdMain_Id and AdAlt_Id respectively . Is there something that can be done to easily manage this situation? Could this be an improvement? Is this something that's more related to the mysql driver than sqlc?

EDIT:

      rename:
        spotify_url: "SpotifyURL"

doesn't seem useful in this case. The name of the resulting struct field depends on context (eg: every table may have an ID, but whether it ends up being Id_2 or Id_3 depends on the exact query and what joins happen).

EDIT 2:
embeds don't seem to help either, since it would be needed to be able to embed by alias as opposed to table name when doing a join. Something like sqlc.embed(adAlt) isn't recognised as per example above. I guess mainly the issue is with multiple joins on the same table.

EDIT 3:
Some playground examples:

https://play.sqlc.dev/p/37c12941f7f1943997f4a628b82fb6be33d852a71614bb581a3ee14d71c280eb - initial version, fields mangled

https://play.sqlc.dev/p/8aa8bb4478ca2f7a54533ec2d66a167e580a93f6879ab6af4c5ead6e6b6e989e - trying embed, but can't resolve

https://play.sqlc.dev/p/31f018cd6e834cd2ccd9122789cd91c8adf29e7ea5e478e6a29dfafd2846903e - this is weird, it seems to resolve but the resulting embeds are numbered, so rendering them still confusing (as one would have to remember the order of the joins)

Questions:

• could/should the embeds also resolve table aliases that are longer than one single letter?
• if so, could the alias be used to identify fields/embeds instead of numbers ?

[andrewmbenton]

Regarding your first question, embeds do resolve table aliases of any length, but there appears to be a bug related to letter casing. I've created an issue here: #2745. Here's your second playground link but with all alias names lower-cased, which works fine: https://play.sqlc.dev/p/e3de092a32a1c9b611322b42fc24ec1fabd2b3f751e357fac5e1f3d9342838ca

But regarding your larger question about better names for the embedded struct fields, I think using the alias as part of the name (or actually the whole name?) is a good suggestion. It would be a breaking change, but we could put it behind a configuration flag.

I think just using the alias name if provided is the right answer, so in this case that would generate a struct like:

type FindAccountByIdRow struct {
	A              Account
	LocMain   Location
	LocAlt       Location
}