sqlc.embed that overrides struct field name

[marko995]

What do you want to change?

Hey guys,
I can't find something like this in documentation, so I want to ask if it is possible to override the field name in struct using sqlc.embed?

For example, I have a table that contains two foreign keys to same table. When I wrote the query:

SELECT sqlc.embed(g), sqlc.embed(home_team), sqlc.embed(away_team)
FROM game g
LEFT JOIN team home_team ON g.home_team_id = home_team.id
LEFT JOIN team away_team ON g.away_team_id = away_team.id
..............

model looks like this:

type GamesWithTeamsByParamsRow struct {
	Game   Game
	Team   Team
	Team_2 Team
}

Is it possible look like this:

type GamesWithTeamsByParamsRow struct {
	Game   Game
	HomeTeam   Team
	AwayTeam Team
}

I tried using AS home_team and AS away_team, but it doesn't work.

If this feature doesn't exist, may I suggest adding a new argument to sqlc.embed that will tell to generator how to call that field.

What database engines need to be changed?

PostgreSQL, MySQL, SQLite

What programming language backends need to be changed?

Go

[zerefel]

A workaround is mentioned in #3177 - use DB Views. Though it's mostly useful if you redefine your own models and embed the types e.g.

CREATE VIEW home_teams AS SELECT * FROM teams;

type HomeTeam Team
   // or repository.Team if you're not redefining your own models and you rely on sqlc-generated models

I find this is a satisfactory solution for a small project.

[kwoodhouse93]

I agree this workaround is probably manageable for a small project. However, for a larger one with a complex schema, it quickly becomes quite undesirable to add a view every time this crops up. For example, I'm working with a lot of tables that reference each other multiple times, or even that reference themselves, so it quickly becomes important to differentiate between multiple relations of the same type.

I'd agree with @marko995 that the most desirable option here would be a second, optional argument for setting the name of the embed in the generated struct. Alternatively, I expected AS <name> to work as it does for non-embedded fields, but in practice, this actually breaks the generated query (at least using v1.28.0 with postgres & Golang).

---

For example:

SELECT
  sqlc.embed(accounts),
  orders.id
FROM orders
LEFT JOIN accounts ON orders.account_id = accounts.id

Generates:

SELECT
  accounts.id, accounts.email,
  orders.id
FROM orders
LEFT JOIN accounts ON orders.account_id = accounts.id

The struct would be:

type ... struct {
  Accounts Accounts
  OrderID string
}

---

If using as, though, say our SQLC query is:

SELECT
  sqlc.embed(accounts) as order_accounts,
  orders.id
FROM orders
LEFT JOIN accounts ON orders.account_id = accounts.id

Generates:

SELECT
  accounts.accounts, accounts.accounts as order_accounts,
  orders.id
FROM orders
LEFT JOIN accounts ON orders.account_id = accounts.id

The struct is still:

type ... struct {
  Accounts Accounts
  OrderID string
}

So the generated SQL is not a valid query, which makes me think there's a bug here, regardless of the proposed enhancement. sqlc.embed is a huge value-add for working with JOINs. Being able to remove this limitation would make it even more valuable.