如何使用sqlx进行LEFT JOIN查询？

Question

I'd like to get results from joke table where jokes are upvoated in jokevote table.

Here is the query:

var jokes []model.Joke
err := shared.Dbmap.Select(&jokes, "    SELECT  *
    FROM  joke
    LEFT JOIN  jokevote
    WHERE  joke.user_id=?
      AND  jokevote.user_id=?
      AND  jokevote.vote=1

", userId, userId) if err != nil { fmt.Println("%v ", err)
}

But I get this error:

Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE joke.user_id=? AND jokevote.user_id=? AND jokevote.vote=1' at line 1

I have also tried:

err := shared.Dbmap.Select(&jokes, "    SELECT  *
    FROM  joke
    LEFT JOIN  jokevote
    WHERE  joke.user_id=jokevote.user_id
      AND  jokevote.vote=?

", 1)

And got the same error. I looked at the docs and could not find any example of such joins. So wondering how can I fix it.

Answer 1

The examples would me in the mariadb docs. What you are missing is the on clause that tells mariadb how to join the 2 tables. You cannot use the old-school join syntax with left join putting the join criteria into the where clause.

SELECT * FROM joke
LEFT JOIN jokevote ON joke.user_id=jokevote.user_id
WHERE jokevote.vote=?