Better way of dealing with a MYSQL SELECT query on a id that can also be nil

user_3772810 0
Viewed 28

Is there a better way of dealing with a MYSQL SELECT query on a id that can also be nil. The code below works, but I would like to have it in one query. Is that possible?

    if parent_id != nil {
        err := db.QueryRow("SELECT id FROM category WHERE parent_id = ?", parent_id).Scan(&category_id)
        if err != nil {
            fmt.Println("ID not found", err)
        }
    } else {
        err := db.QueryRow("SELECT id FROM category WHERE parent_id is NULL").Scan(&category_id)
        if err != nil {
            fmt.Println("ID not found", err)
        }
    }
go
1 Answers

You should be able to use the NULL-safe equal to operator.

err := db.QueryRow(`SELECT id
    FROM category
    WHERE parent_id <=> ?
`, parent_id).Scan(&category_id)

As an alternative, you could, inside the SQL, test the passed in Go argument twice (you'll have to pass it twice):

  1. do a plain equality comparison against the column.
  2. check if the parameter and the column are both NULL.
err := db.QueryRow(`SELECT id
    FROM category
    WHERE parent_id = ?
    OR (? IS NULL AND parent_id IS NULL)
`, parent_id, parent_id).Scan(&category_id)
user_9659000
Related