英文:
Is it possible to use SQL IIF function in GORM?
问题
我有一个用于作业的表格,一个用于解决方案的表格,还有一个用于学生的表格。
我想检索所有的作业,并针对每个作业添加一个“标志”,显示当前登录的学生是否尝试了该作业。
我尝试了这个:
import (
"fmt"
"gorm.io/gorm"
"encoding/json"
"github.com/my_organisation/myorg-repo/db"
)
var database *gorm.DB
var solutions []db.Solution
var listOfAsnmtIDs []uint
func myfuncn (w http.ResponseWriter, r *http.Request){
//...
_ = database.Table("solutions").Where("pupil_id = ?",
pupil.ID).Select("assignment_id").Find(&solutions)
for _, solution := range solutions {
listOfAsnmtIDs = append(listOfAsnmtIDs, solution.AssignmentID)
}
response := database.Table("assignments").Select(`id, created_at, IIF((id IN ?), 'attempted', 'Not attempted') as attempted`, listOfAsnmtIDs).Find(&allAssignments)
if response.RowsAffected < 1 {
respondToClient(w, 404, nil, "No assignments found")
return
}
//...
}
请注意,我只翻译了代码部分,其他内容不包括在内。
英文:
I have a a table for assignments, one for solutions and another for students.
I want to retrieve all assignments and against each I want to add a 'flag' showing that the currently logged-in student has attempted the assignment or not.
I have tried this:
import (
"fmt"
"gorm.io/gorm"
"encoding/json"
"github.com/my_organisation/myorg-repo/db"
)
var database *gorm.DB
var solutions []db.Solution
var listOfAsnmtIDs []uint
func myfuncn (w http.ResponseWriter, r *http.Request){
//...
_ = database.Table("solutions").Where("pupil_id = ?",
pupil.ID).Select("assignment_id").Find(&solutions)
for _, solution := range solutions {
listOfAsnmtIDs = append(listOfAsnmtIDs, solution.AssignmentID)
}
response := database.Table("assignments").Select(`id, created_at, IIF((id IN ?), 'attempted', 'Not attempted') as attempted`, listOfAsnmtIDs).Find(&allAssignments)
if response.RowsAffected < 1 {
respondToClient(w, 404, nil, "No assignments found")
return
}
//...
}
答案1
得分: 0
你只需要列出参数。像这样:
var mad string
for i, solution := range solutions {
mad += strconv.FormatUint(uint64(solution.AssignmentID), 10)
if i != len(solutions) {
mad += ","
}
listOfAsnmtIDs = append(listOfAsnmtIDs, solution.AssignmentID)
}
response := database.Table("assignments").Select(`id, created_at, IIF((id IN ?), 'attempted', 'Not attempted') as attempted`, mad).Find(&allAssignments)
英文:
You just need to list params. Something like this
var mad string
for i, solution := range solutions {
mad += strconv.FormatUint(uint64(solution.AssignmentID), 10)
if i != len(solutions) {
mad += ","
}
listOfAsnmtIDs = append(listOfAsnmtIDs, solution.AssignmentID)
}
response := database.Table("assignments").Select(`id, created_at, IIF((id IN ?), 'attempted', 'Not attempted') as attempted`, mad).Find(&allAssignments)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论