Postgres order by with nulls last

[Zhe-Shen]

I'm using Ent with Postgres. When I try to order by a time field with desc order, the null values cause unexpected result with nulls come first, refer to sort-by-column-asc-but-null-values-first. Does Ent support postgres syntax like "order by time desc nulls last" so that nulls come last?

[a8m]

Hey @Zhe-Shen and thanks for raising this issue.

The index API doesn't support this, but it's going to be added in the upcoming release as we're rewriting the migration framework atm (should be released in a few weeks from now). If you're blocked on this, please ping me on our Slack chanhel and I'll try to help. Thanks 🙏

[mikebuss]

@a8m was support for this added? Thank you!

[Liooo]

@a8m

I see nullFirst/Last code in atlas, but seems like this isn't available from ent.
Is there any workaround? Or could you please add the feature? 🙏 We need this one 🥹

[crossworth]

Import: "entgo.io/ent/dialect/sql"

Define a helper functions:

func NullsLast(column string) string {
	b := &sql.Builder{}
	b.Ident(column).WriteString(" NULLS LAST")
	return b.String()
}

func NullsFirst(column string) string {
	b := &sql.Builder{}
	b.Ident(column).WriteString(" NULLS FIRST")
	return b.String()
}

Usage:

users := client.User.Query().Order(func(s *sql.Selector) {
	s.OrderBy(NullsFirst(sql.Asc(user.FieldName)))
}).IDsX(ctx)