在GORM中使用SQL的IIF函数是否可行?

huangapple go评论76阅读模式
英文:

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 (
   &quot;fmt&quot;    
   &quot;gorm.io/gorm&quot;
   &quot;encoding/json&quot;
   &quot;github.com/my_organisation/myorg-repo/db&quot;
)

var database *gorm.DB

var solutions []db.Solution
var listOfAsnmtIDs []uint

func myfuncn (w http.ResponseWriter, r *http.Request){
   //...
   _ = database.Table(&quot;solutions&quot;).Where(&quot;pupil_id = ?&quot;, 
   pupil.ID).Select(&quot;assignment_id&quot;).Find(&amp;solutions)
	
	for _, solution := range solutions {
	    listOfAsnmtIDs = append(listOfAsnmtIDs, solution.AssignmentID)
	}

response := database.Table(&quot;assignments&quot;).Select(`id, created_at, IIF((id IN ?), &#39;attempted&#39;, &#39;Not attempted&#39;) as attempted`, listOfAsnmtIDs).Find(&amp;allAssignments)   

if response.RowsAffected &lt; 1 {
   respondToClient(w, 404, nil, &quot;No assignments found&quot;)
   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 += &quot;,&quot;
		}
		listOfAsnmtIDs = append(listOfAsnmtIDs, solution.AssignmentID)
	}

	response := database.Table(&quot;assignments&quot;).Select(`id, created_at, IIF((id IN ?), &#39;attempted&#39;, &#39;Not attempted&#39;) as attempted`, mad).Find(&amp;allAssignments)

huangapple
  • 本文由 发表于 2022年2月2日 21:52:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/70956843.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定