pg
pg copied to clipboard
How to refactor this code to retrieve custom (virtual) columns from DB along with default ones with one query only
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.
@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.
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.