How to SELECT by BLOB primary key

[thedmdim]

Hello! In short, a can't get a row by blob primary key (blob for uuid)

The table:

CREATE TABLE IF NOT EXISTS cams (
        id BLOB PRIMARY KEY,
        addr STRING,
        adminka STRING,
        stream STRING,
        model STRING,
        country STRING
);

My handler:

type Cam struct {
	ID           string
	Addr         string
	Adminka      string
	Stream       string
	Model        string
	Country      string
	Likes        int64
	Dislikes     int64
}

func (s *Router) GetCam(w http.ResponseWriter, r *http.Request) {

	vars := mux.Vars(r)
	camID, err := hex.DecodeString(vars["cam_id"])
	if err != nil {
		w.WriteHeader(http.StatusNotFound)
		return
	}

	conn := s.db.Get(context.Background())
	defer s.db.Put(conn)

	stmt := conn.Prep(`
		SELECT
			cams.addr,
			cams.adminka,
			cams.stream,
			cams.model, 
			cams.country,
			SUM(reactions.like = 1) AS likes,
			SUM(reactions.like = 0) AS dislikes
		FROM cams
		JOIN reactions ON reactions.cam_id = cams.id
		WHERE cams.id = ?`)
		
	stmt.BindBytes(1, camID)
	row, err := stmt.Step()
	if err != nil {
		w.WriteHeader(http.StatusBadGateway)
		return
	}
	if !row {
		w.WriteHeader(http.StatusNotFound)
		return
	}
	
	fmt.Printf("%x\n", camID)  // prints 57c43ccc2bbb490cb90890587cb9725c

	cam := &Cam{
		ID:           vars["cam_id"],
		Addr:         stmt.GetText("addr"),
		Adminka:      stmt.GetText("adminka"),
		Stream:       stmt.GetText("stream"),
		Model:        stmt.GetText("model"),
		Country:      stmt.GetText("country"),
		Likes:        stmt.GetInt64("likes"),
		Dislikes:     stmt.GetInt64("dislikes"),
	}
	stmt.ClearBindings(); stmt.Reset()

	templates.ExecuteTemplate(w, "cam.html", cam)

When I do manually in db select * from cams where id = X'57c43ccc2bbb490cb90890587cb9725c'; I get my row

But in handler cam remains empty. What am I doing wrong?

[zombiezen]

I think your Go logic is correct, but I think your SQL query is off. Since it's an INNER JOIN, if there are zero likes or dislikes, you won't get result rows.

SELECT
  cams.addr,
  cams.adminka,
  cams.stream,
  cams.model, 
  cams.country,
  (select count() from reactions where "like" = 1 and cam_id = cams.id) AS likes,
  (select count() from reactions where "like" = 0 and cam_id = cams.id) AS dislikes
FROM cams
WHERE cams.id = ?

[thedmdim]

Thanks! You are right, how could I not see it myself <3