How to refactor this code to retrieve custom (virtual) columns from DB along with default ones with one query only

[frederikhors]

I'm studying Golang trying to build an application.

I have written many models:

• Player
• Book
• Score
• Referee
• Life
• Team
• Doc
• Family

and so on...

A model is like this:

type Player struct {
  id   int
  name string
}

I'm using go-PG like this:

db := database.New(config)
defer db.Close()

var players []Player

error := db.Model(&players).Select()
// handle error

and it works.

The SQL query is:

SELECT * FROM players

with these rows:

+--------+----------+
| id     | name     |
+--------+----------+
| 1      | John     |
+--------+----------+
| 2      | Mike     |
+--------+----------+
| 3      | Bob      |
+--------+----------+

Now I need to query from DB one or more "virtual" columns like this:

'mysqlcalculation' AS mycalculation

so I tried to use:

query.Column("*").ColumnExpr("'mysqlcalculation' AS mycalculation")

which generates a correct SQL query:

SELECT *, 'mysqlcalculation' AS mycalculation FROM players

with these rows:

+--------+----------+------------------+
| id     | name     | mycalculation    |
+--------+----------+------------------+
| 1      | John     | mysqlcalculation |
+--------+----------+------------------+
| 2      | Mike     | mysqlcalculation |
+--------+----------+------------------+
| 3      | Bob      | mysqlcalculation |
+--------+----------+------------------+

I'm doing this because I am interested in the result in the mycalculation column which is much more convenient to calculate in the database than in Go; it can be JSON or string. Simply data.

Now my ORM panic with:

PNC error="pg: can't find column=mycalculation in model=Player (try discard_unknown_columns)"

I can understand that because now go-pg doesn't know how to bind data, so now I'm using:

var playerWithCalculation []struct {
  Mycalculation  string
  models.Player
}

var players []Player

error := db.Model(&playerWithCalculation).Column("*").ColumnExpr("'mysqlcalculation' AS mycalculation").Select()
// handle error

for i := range playerWithCalculation {
  players = append(players, &playerWithCalculation[i].Player)
}

QUESTION

I think this is wasteful and expensive. Many allocations and loops.

And besides, I have several models and resolvers and methods!

What can I do?

How can I improve this code and my architecture?

I think a possible solution is to run two queries, perhaps in two different goroutines. But I don't want to run two queries for this.

[frederikhors]

@vmihailenco I refactored question.

Maybe I'm completely wrong to think about the problem.

But precisely for this there is the open source community and geniuses like you.

[vmihailenco]

Hi @frederikhors,

Perhaps I misunderstand the problem, but when I need virtual column I usually add one to the model, e.g.

type Player struct {
  ID   int
  Name string

  // Virtual columns.
  Mycalculation  string `pg:"-"`
}

Your solution with playerWithCalculation looks perfectly fine too and in some cases I use it too.

Hope that helps.