search

go-gorm / gorm

The fantastic ORM library for Golang, aims to be developer friendly
https://gorm.io
MIT License
36.8k stars 3.93k forks source link

How to add Limit, Offset and Order By clause to Raw chain method? #5898

Open shalakakad opened 1 year ago

shalakakad commented 1 year ago

Your Question

The query that I am trying is err = gdb.Raw(sqlQuery, qp.WhereArgs...).Offset(qp.Offset).Limit(qp.Limit).Order(qp.Order).Scan(&dbRecords).Error

This was working fine before but after migrating to v1.24.0 these clauses are not working. Even after applying the Limit, all records are getting returned. Docs here https://gorm.io/docs/method_chaining.html mention that Raw can’t be used with other chainable methods to build SQL. I need to use union all query and so I am using Raw. I have tried adding Limit order by in the raw query itself like this

    querySuffix := " select * from temp1 R0 " +
    whereClause1 +
    " union all " +
    " select * from security_audit_entry_data S0" +
    whereClause2 +
    ") T0"

queryClause := " offset " + strconv.Itoa(qp.Offset) + " limit " + strconv.Itoa(qp.Limit) + " order by " + qp.Order

sqlQuery := queryPrefix + querySuffix + queryClause.

err = reusableDB.Debug().Raw(sqlCount, qp.WhereArgs...).Count(&count).Error This is giving error near "offset": syntax error

The document you expected this should be explained

https://gorm.io/docs/method_chaining.html

Expected answer

bhasden commented 1 year ago

If you're still looking for a workaround, this issue includes an example. Essentially, calling .Table() puts you back into "method chaining mode" and behaves as expected rather than silently ignoring subsequent chained calls.