ORDER BY ASC NULLS FIRST/LAST support

[v-byte-cpu]

Describe the feature

Hi there! it would be great if gen project could add modifiers to asc/desc sorting (for instance, column.Asc().NullsFirst() ) or just enable ORDER BY clause in Clauses() method.

Motivation

I want to write the following sql query:

SELECT * FROM some_table ORDER BY some_column ASC NULLS FIRST

if I try to add clause.OrderBy to Clauses() method of a gen-generated interface, then it fails with "clause ORDER BY is banned" error.

So I use the hack described in https://github.com/go-gorm/gen/issues/739:

q.ReplaceDB(q.UnderlyingDB().Clauses(clause.OrderBy{
    Expression: clause.Expr{SQL: "? ASC NULLS FIRST", Vars: []any{someTable.SomeColumn}},
}))

And it actually looks ugly.

Related Issues

https://github.com/go-gorm/gen/issues/739

[randree]

While it may not be the most aesthetically pleasing, it works. I'm optimistic that a more elegant solution will be available soon. Below is an example of how it can be utilized with Postgres, selecting 5 products at random.

    products, err := q.ReplaceDB(q.Product.UnderlyingDB().Clauses(clause.OrderBy{
        Expression: clause.Expr{SQL: "RANDOM()"},
    })).Product.Preload(q.Product.Translation).Limit(5).Find()

For MySQL, use RAND().