需要从第二个表中排除在第一个表中存在的ID。

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

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:&quot;id&quot;`
UserID string `json:&quot;user_id&quot;`
Status string `json:&quot;status&quot;`
Students
ID string `json:&quot;id&quot;`
UserID string `json:&quot;user_id&quot;`
RequestID string `json:&quot;request_id&quot;`
StartDate time.Time `json:&quot;start_date&quot;`
EndDate time.Time `json:&quot;end_date&quot;`

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{&quot;canceled&quot;, declined&quot;}

   type Result struct {
      tableName        struct{}  `pg:&quot;students&quot;`
	  ID               string `json:&quot;id&quot;`
	  UserID      int       `json:&quot;user_id&quot;`
	  RequestID string    `pg:&quot;fk:request_id&quot;`
	  Status           string    `json:&quot;status&quot;`
   }

   var res []Result

	err := Model(&amp;res).
	  ColumnExpr(&quot;id, user_id, requests.status&quot;).
	  Where(&quot;user_id = ?&quot;, UseID).
      Where(&quot;start_date &gt;= ? AND end_date &lt;= ?&quot;, startDate, endDate).
	  Join(&quot;LEFT JOIN requests ON requests.id = request_id&quot;).
	  WhereIn(&quot;requests.status IN (?)&quot;, 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:&quot;students&quot;`
    ID         string    `json:&quot;id&quot;`
    UserID     string    `json:&quot;user_id&quot;`
    RequestID  string    `pg:&quot;fk:request_id&quot;`
    Status     string    `json:&quot;status&quot;`
}

var res []Result
status := []string{&quot;canceled&quot;, &quot;declined&quot;}

err := Model(&amp;res).
    ColumnExpr(&quot;students.id, students.user_id, students.request_id, requests.status&quot;).
    Where(&quot;students.user_id = ?&quot;, UserID).
    Where(&quot;students.start_date &gt;= ? AND students.end_date &lt;= ?&quot;, startDate, endDate).
    Where(&quot;NOT EXISTS (SELECT 1 FROM requests WHERE students.request_id = requests.id AND requests.status IN (?))&quot;, 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.

huangapple
  • 本文由 发表于 2023年7月24日 02:29:29
  • 转载请务必保留本文链接:https://go.coder-hub.com/76749754.html
匿名

发表评论

匿名网友

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

确定