Need to exclude ids present in first table from second table

php Xiaobian Yuzai is here to introduce to you a question about data processing. When we need to exclude ids that already exist in another table from one table, we can do it through a specific method. This problem is very common in data processing. Solving it can help us better organize and process data, and improve the efficiency and accuracy of data processing. In the following article, we will detail how to achieve this goal by using specific query statements.

Question content

Guys, I need your help again. My brain is broken and I don't understand what I did 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 user id and I need to find all user requests between start date and end date but should exclude requests with status "cancelled" or "denied". I thought I could use left join but it doesn't work as I need. Currently 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()

Currently, I receive all the data for the required date from the table student, but it is not excluded from the results even if the status in the request table is "Cancelled" or "Rejected". If you need any additional information from me to help, please let me know. Thanks for any comments and suggestions.

Workaround

You need to ensure that the where clause is applied correctly to exclude requests with a status of "Cancelled" or "Rejected".

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 the not exists subquery to check for requests with the same request_id in the requests table and whose status exists in status Slicing. If such requests are found in the subquery, they will be excluded from the final results.

The above is the detailed content of Need to exclude ids present in first table from second table. For more information, please follow other related articles on the PHP Chinese website!