Add "IS [NOT] DISTINCT FROM" comparison operators to Cond

[NCRonB]

It would be nice to have Cond.IsDistinctFrom and Cond.IsNotDistinctFrom for the comparison operators IS DISTINCT FROM and IS NOT DISTINCT FROM.

[huandu]

It's a bit tricky to implement IS DISTINCT FROM and IS NOT DISTINCT FROM operators. It's a part of SQL-2003 standard, but MySQL and many MySQL-like systems don't support it. I need to think of the design for a while.

[huandu]

I found a smart way to support these operators on different database systems. I just submit the fix and will release it soon.

[NCRonB]

Excellent! Thank you!

Does this mean you could also support flavor-specific operators, like PostgreSQL's ARRAY type operators (@>, <@, &&, ||)? If not, what's the best way to include these in a query built with go-sqlbuilder?

[huandu]

Does this mean you could also support flavor-specific operators, like PostgreSQL's ARRAY type operators (@>, <@, &&, ||)? If not, what's the best way to include these in a query built with go-sqlbuilder?

It depends. Regarding to the ARRAY type operators, as I can't find proper alternatives on other DB systems, I don't think it's feasible to support it in this package.

If you want to build flavor-specific operators, expressions or statements, you can use the Var() method to create placeholders for any value and use your favorite string builder (e.g. fmt.Sprintf) to build a desired string.

Here is a sample to build SQL using @>.

type Varer interface {
    Var(value any) string
}

// ArrayContain is used to construct the expression "field @> ARRAY[values]".
func ArrayContain[V Varer, T any](v V, field string, values []T) string {
    return fmt.Sprintf(
        "%s @> ARRAY[%s]",
        sqlbuilder.Escape(field),
        v.Var(sqlbuilder.List(values)),
    )
}

labels := []string{"label1", "label2"}
sb := sqlbuilder.Select("labels").From("users")
sb.Where(
    ArrayContain(sb, "labels", labels),
)

sql, args := sb.BuildWithFlavor(sqlbuilder.PostgreSQL)
fmt.Println(sql)
fmt.Println(args)

// Output:
// SELECT labels FROM users WHERE labels @> ARRAY[$1, $2]
// [label1 label2]

[NCRonB]

Thanks for the sample!

I'm generating very dynamic queries, and I like the API of this package so far. I don't need multi-flavor support for this project as I'm only using PostgreSQL, and I do use PostgreSQL-specific features.

I hope you'll reconsider providing flavor-specific features that aren't supported by all databases. I understand the desire to support all databases with every feature, but it would be nice for those of us using only one database. If a feature is documented as flavor-specific, or if the developer knows the database they're using doesn't support the feature, they can just avoid using it.

[huandu]

@NCRonB I have carefully considered your suggestion, but I still feel that the responsibility for implementing flavor-specific operators should be left to the caller to implement themselves, while this library only implements the various features mentioned in the SQL standard and de facto standards. The main reason for this consideration is that there are too many variations in SQL syntax. Once a flavor is accommodated, it may lead to an increasing number of APIs being added to support various syntaxes, which could potentially clutter the API with flavor-specific APIs, inevitably increasing the cost of understanding and using this library. Since one of the goals of this library is to serve as an infrastructure for other ORM libraries, I prefer to expose interfaces that are easy to use for everyone to expand their own flavor-specific APIs, which is a direction I am very willing to pursue.

Therefore, you might consider what I can help you with to more easily implement the operators you need. Feel free to raise such requirements at any time, and I will prioritize supporting them.

By the way, perhaps we should open a new issue to discuss this topic. Could you summarize your requirements and start a new issue?

[NCRonB]

I appreciate your consideration. Having to implement flavor-specific operators and features myself kind of defeats the purpose of using the builder. I don't consider supporting flavor-specific features clutter, but I understand if you want to limit the scope of this module. My requirements are beyond this scope, so it may not be the best fit for my current project.

[huandu]

For PostgreSQL specific features, package pg may be the best for you. You are free to choose the appropriate library at your discretion. It's perfectly normal if this library is not suitable; finding one that meets your needs is what matters most.