英文:
Need to exclude ids from the second table which exist in the first table
问题
大家,我再次需要你们的帮助。
我的大脑有点混乱,我不明白我做错了什么。
我有两个表格:
Requests
ID string `json:"id"`
UserID string `json:"user_id"`
Status string `json:"status"`
Students
ID string `json:"id"`
UserID string `json:"user_id"`
RequestID string `json:"request_id"`
StartDate time.Time `json:"start_date"`
EndDate time.Time `json:"end_date"`
作为接收者,我有一个userID,我需要找到在开始日期和结束日期之间的所有用户请求,但应该排除状态为"canceled"或"declined"的请求。
我以为可以使用LEFT JOIN,但它不起作用。
目前我有以下查询:
status := []string{"canceled", "declined"}
type Result struct {
tableName struct{} `pg:"students"`
ID string `json:"id"`
UserID int `json:"user_id"`
RequestID string `pg:"fk:request_id"`
Status string `json:"status"`
}
var res []Result
err := Model(&res).
ColumnExpr("id, user_id, requests.status").
Where("user_id = ?", UseID).
Where("start_date >= ? AND end_date <= ?", startDate, endDate).
Join("LEFT JOIN requests ON requests.id = request_id").
WhereIn("requests.status IN (?)", status).
Select()
目前,我从students表中接收到了所需日期的所有数据,但即使在请求表中有相同的状态为"canceled"或"decline"的数据,它也没有从结果中排除。
如果你需要我提供任何额外的信息来帮助我,请告诉我。
谢谢你们的建议和建议。
英文:
Guys i need your help again.
My brain is broken and i don't understand what i am doing wrong.
I have 2 tables
Requests
ID string `json:"id"`
UserID string `json:"user_id"`
Status string `json:"status"`
Students
ID string `json:"id"`
UserID string `json:"user_id"`
RequestID string `json:"request_id"`
StartDate time.Time `json:"start_date"`
EndDate time.Time `json:"end_date"`
As a receiver i have a userID and i need to find all user's requests between the start and end date, but should exclude requests with status "canceled" or "declined".
I thought that i can use LEFT JOIN but it doesn't work as i need.
At the moment I have the following query:
status := []string{"canceled", declined"}
type Result struct {
tableName struct{} `pg:"students"`
ID string `json:"id"`
UserID int `json:"user_id"`
RequestID string `pg:"fk:request_id"`
Status string `json:"status"`
}
var res []Result
err := Model(&res).
ColumnExpr("id, user_id, requests.status").
Where("user_id = ?", UseID).
Where("start_date >= ? AND end_date <= ?", startDate, endDate).
Join("LEFT JOIN requests ON requests.id = request_id").
WhereIn("requests.status IN (?)", status).
Select()
At the moment i receive all data in the needed date from the table students, but even if the same with the status "canceled" or "decline" is in the request table it's didn't excluded from the result.
If you need any additional information from me to help me, just let me know.
Thank you for any advise and suggestion.
答案1
得分: 2
你需要确保正确应用WHERE
子句来排除状态为“canceled”或“declined”的请求。
type Result struct {
TableName struct{} `pg:"students"`
ID string `json:"id"`
UserID string `json:"user_id"`
RequestID string `pg:"fk:request_id"`
Status string `json:"status"`
}
var res []Result
status := []string{"canceled", "declined"}
err := Model(&res).
ColumnExpr("students.id, students.user_id, students.request_id, requests.status").
Where("students.user_id = ?", UserID).
Where("students.start_date >= ? AND students.end_date <= ?", startDate, endDate).
Where("NOT EXISTS (SELECT 1 FROM requests WHERE students.request_id = requests.id AND requests.status IN (?))", status).
Select()
我使用了一个NOT EXISTS
子查询来检查在requests
表中具有相同request_id
并且状态存在于status
切片中的请求。如果在子查询中找到这样的请求,它们将被排除在最终结果之外。
英文:
You need to ensure that the WHERE
clause to exclude requests with status "canceled" or "declined" is applied correctly.
type Result struct {
TableName struct{} `pg:"students"`
ID string `json:"id"`
UserID string `json:"user_id"`
RequestID string `pg:"fk:request_id"`
Status string `json:"status"`
}
var res []Result
status := []string{"canceled", "declined"}
err := Model(&res).
ColumnExpr("students.id, students.user_id, students.request_id, requests.status").
Where("students.user_id = ?", UserID).
Where("students.start_date >= ? AND students.end_date <= ?", startDate, endDate).
Where("NOT EXISTS (SELECT 1 FROM requests WHERE students.request_id = requests.id AND requests.status IN (?))", status).
Select()
I used a NOT EXISTS
subquery to check for requests that have the same request_id
in the requests
table and have a status that is present in the status
slice If such requests are found in the subquery, they will be excluded from the final result.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论