Provide easy way to use Exists() in query

[dschreij]

Many databases nowadays have functionality to test whether some query returns any results, specifically using EXISTS() . I now often resort to a raw query to make use of this, but it would be great to have a dedicated function to compose EXISTS parts of a query. For instance

db.Select("EXISTS(SELECT * FROM posts WHERE user_id  = 5) AS userHasPosts").Find(&users)

could very roughly become something like

db.Select(db.Exists(db.Where(Post{UserID: 5})).As("userHasPosts")).Find(&users)

Another example, to perform a WHERE EXISTS:

db.Where("EXISTS(SELECT * FROM posts WHERE user_id  = ?)", 5).Find(&users)

one could write

db.Where(db.Exists(db.Where(Post{UserID: 5}))).Find(&users)

The key aim here is not to have to resort to writing raw SQL in strings, because this makes it much harder and error prone to dynamically construct queries that serve as argument to the EXISTS() function, and makes the query much more vulnerable to SQL injections.

I am not familiar at all with writing SQL builders, so I have no clue if this would be hard to implement, or whether there were already good reasons (I couldn't find any) why this hadn't been implemented before, but I would love to hear peoples thoughts on this.

[twocs]

You are using a subquery. The docs don't mention how to do an EXISTS condition but I believe in go-gorm it's something like the following:

mySubquery := db.Table("posts").Select("1").Where("posts.user_id = users.id")
db.Where("EXISTS ?", mySubquery).Find(&users)

Took me a while to understand how to do that, so it could be convenient to do something like:

// currently unsupported db.Exists function
db.Exists(mySubquery).Find(&users)

However, it works for mysql, but do all supported databases have an equivalent to EXISTS?