How to execute COUNT (*) OVER() AS some_column_name using GORM

[RayendraSabandar]

Your Question

How to execute COUNT (*) OVER() AS some_column_name using GORM

The document you expected this should be explained

https://gorm.io/docs/advanced_query.html#Count

Environment

ORM: gorm.io/gorm v1.21.16 Go: 1.18.3 Database: Postgres OS: macOS Monterey 12.5

Expected answer

This script works fine using sql script in dbeaver

SELECT *,
COUNT(*) OVER() AS some_column_name
FROM some_table_name
WHERE some_query
LIMIT 10

I want a column that gives me the total records found that satisfy the query but exclude the limit or offset. So even if the limit is 10, I could get a total records of 10 or above that would be very useful for pagination.

For this service, I am using db.Where(), Joins(), Order(), Or(), and Limit(). I don't want to use all raw query and type in the sql script from the beaver

how do I insert this script COUNT(*) OVER() AS some_column_name using gorm, like db.Something()

[RayendraSabandar]

So I add a new field to my model

type Job struct {
	domain.BaseModel
	...
	FullCount            int64        `json:"-"`
}

And add this line to my query

db = db.Select("some_table_name.*, count (*) Over() as full_count")

But to extract that data I have to validate whether or not the result has a length greater than 0 and take the property of FullCount of the first index like so,

if len(result) > 0 {
	fullCount = result[0].FullCount
	lastPage = math.Ceil(float64(fullCount) / float64(limit))
}

I do hope there's a new feature to be able to take the value as it is without having to take it from the first index

[zstone12]

db.Where(`some_query`)
var count int 
db.Model(&model.some_table_name{}).Count(&count)
db.Limit().Offset().Find()

Maybe this code can solve your problem