Need to convert the SQL Query to Gorm query

Question

I have this SQL query

Select CONCAT(kafka_user_stream.FirstName,' ', kafka_user_stream.LastName) AS "Full Name", 
kafka_user_stream.UID AS "User ID", 
kafka_user_stream.CountryCode AS "Country",
kafka_user_stream.CreatedAt AS "Registration Date & Time",
COUNT(jackpotmessage_stream.UID) AS "Win Count"
FROM kafka_user_stream LEFT JOIN 
jackpotmessage_stream ON jackpotmessage_stream.UID = kafka_user_stream.UID
WHERE "Type"='goldenTicketWin' 
GROUP BY "Full Name", "User ID", "Country", "Registration Date & Time"
ORDER BY "Win Count" DESC

I want to convert it to Gorm. I can use it using

err = s.db.Exec("...QUERY")

but i cannot extract data from the above query. I need to extract all of the above fields (Full Name, User ID etc) and store them in a struct.

In above query, kafka_user_stream and jackpot_message are the tables extracted from a kafka stream. I am using go-gorm and go.

I tried the Gorm documentation as well as few other references but I am unable to find any solution. Would be very thankful for any leads, insight or help.

score 1 · Accepted Answer · answered Nov 03 '21 at 09:12

With native go/mysql driver, you should use Query() and Scan() methods to get results from the database and store them in a struct, not Exec().

In GORM, you can use SQL Builder for your custom queries:

type Result struct {
  ID   int
  Name string
  Age  int
}

var result Result
db.Raw("SELECT id, name, age FROM users WHERE name = ?", 3).Scan(&result)

score 0 · Answer 2 · answered Nov 03 '21 at 12:25

I figured out a slightly different way as suggested by Aykut which works fine.

rows, _err := s.gdb.Raw(`Select CONCAT(kafka_user_stream.FirstName,' ', kafka_user_stream.LastName) AS "FullName", 
    kafka_user_stream.UID AS "UserID", 
    kafka_user_stream.CountryCode AS "Country",
    kafka_user_stream.CreatedAt AS "CreatedAt",
    COUNT(jackpotmessage_stream.UID) AS "WinCount"
    FROM kafka_user_stream LEFT JOIN 
    jackpotmessage_stream ON jackpotmessage_stream.UID = kafka_user_stream.UID
    WHERE "Type"='goldenTicketWin' 
    GROUP BY "FullName", "UserID", "Country", "CreatedAt"
    ORDER BY "WinCount" DESC;`).Rows()

Need to convert the SQL Query to Gorm query

2 Answers2