Need to exclude ids from the second table which exist in the first table

Question

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.

Answer 1

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.