英文:
Need to convert the SQL Query to Gorm query
问题
我有这个SQL查询：
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 JOINjackpotmessage_stream ON jackpotmessage_stream.UID = kafka_user_stream.UIDWHERE "Type"='goldenTicketWin'GROUP BY "Full Name", "User ID", "Country", "Registration Date & Time"ORDER BY "Win Count" DESC
我想将其转换为Gorm。我可以使用以下方式：
err = s.db.Exec("...QUERY")
但是我无法从上述查询中提取数据。我需要提取上述所有字段（Full Name、User ID等）并将它们存储在一个结构体中。
在上述查询中，kafka_user_stream和jackpot_message是从kafka流中提取的表。我正在使用go-gorm和go语言。
我尝试了Gorm文档以及其他一些参考资料，但是我无法找到任何解决方案。非常感谢任何线索、见解或帮助。
英文:
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 JOINjackpotmessage_stream ON jackpotmessage_stream.UID = kafka_user_stream.UIDWHERE "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.
答案1
得分: 1
使用原生的go/mysql驱动程序，你应该使用Query()和Scan()方法从数据库中获取结果，并将其存储在一个结构体中，而不是使用Exec()方法。
在GORM中，你可以使用SQL Builder来进行自定义查询：
type Result struct {ID intName stringAge int}var result Resultdb.Raw("SELECT id, name, age FROM users WHERE name = ?", 3).Scan(&result)
英文:
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 intName stringAge int}var result Resultdb.Raw("SELECT id, name, age FROM users WHERE name = ?", 3).Scan(&result)
答案2
得分: 0
我找到了一种稍微不同的方法，正如Aykut建议的那样，它可以正常工作。
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 JOINjackpotmessage_stream ON jackpotmessage_stream.UID = kafka_user_stream.UIDWHERE "Type"='goldenTicketWin'GROUP BY "FullName", "UserID", "Country", "CreatedAt"ORDER BY "WinCount" DESC;`).Rows()
英文:
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 JOINjackpotmessage_stream ON jackpotmessage_stream.UID = kafka_user_stream.UIDWHERE "Type"='goldenTicketWin'GROUP BY "FullName", "UserID", "Country", "CreatedAt"ORDER BY "WinCount" DESC;`).Rows()
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论