Gorm 请问如何构建 SELECT EXISTS 语句？

Your Question

The document you expected this should be explained

请问如何构建如下语句？

-- Check if a product with id 123 exists and is not deleted
SELECT EXISTS (SELECT 1 FROM `product` WHERE `product`.`id` = 123 AND `product`.`deletedAt` IS NULL) AS exists_check;

Expected answer

Comment From: jinzhu

Hello,

You can construct a SELECT EXISTS query in a couple of ways with GORM.

Method 1: Using a Subquery with Select

This is the idiomatic GORM approach. GORM will also automatically handle soft deletes for you if your model includes gorm.DeletedAt.

var exists bool
// Assuming Product is your model and embeds gorm.Model for soft delete support
subquery := db.Model(&Product{}).Select("1").Where("id = ?", 123)
db.Select("EXISTS(?)", subquery).Scan(&exists)

// The `exists` variable will now be true or false

If you need to use an alias like AS exists_check and scan the result into a struct as in your original question, you can use the Raw method:

var result struct {
    ExistsCheck bool `gorm:"column:exists_check"`
}
subquery := db.Model(&Product{}).Select("1").Where("id = ?", 123)
db.Raw("SELECT EXISTS(?) AS exists_check", subquery).Scan(&result)

// result.ExistsCheck will contain the boolean result

Method 2: Using count

A functionally equivalent and very common alternative is to check if count(*) > 0, which can often be more readable.

var exists bool
db.Model(&Product{}).Select("count(*) > 0").Where("id = ?", 123).Find(&exists)

// The `exists` variable will also be true or false here

Both methods will efficiently check for the existence of a record. You can choose the one that best fits your preference and coding style.

Hope this helps

Comment From: shuqingzai

var exists bool // Assuming Product is your model and embeds gorm.Model for soft delete support subquery := db.Model(&Product{}).Select("1").Where("id = ?", 123) db.Select("EXISTS(?)", subquery).Scan(&exists) @jinzhu

第一种方法生成的语句带有 SELECT EXISTS .... FROM table ，这会查询原表数据 gorm 似乎无法实现纯 SELECT EXISTS ，只能写原生 SQL

根据第一种方法构建的 SQL

SELECT EXISTS
    (SELECT 1 FROM `product` WHERE id = 123 AND `product`.`deletedAt` IS NULL)
FROM `product`;

如何将最后的 FROM product 去掉？支持 SELECT EXISTS ? @jinzhu 希望得到的是如下SQL:

SELECT EXISTS
    (SELECT 1 FROM `product` WHERE id = 123 AND `product`.`deletedAt` IS NULL)
    AS `value`;