Add support for `~~` to work with array types

[tonyarnold]

Given the following (incomplete) models:

final class OrganizationUser: PostgreSQLUUIDPivot {
    var roles: OrganizationUser.Roles = []

    typealias Roles = Set<Role>

    enum Role: Int, PostgreSQLRawEnum {
        case licenseManager = 1
        case billingManager = 2
        case owner = 3
    }
}

I would expect that I might be able to use one of the following:

query.filter(\OrganizationUser.roles ~~ .owner) // Type '[OrganizationUser.Roles]' (aka 'Array<Set<OrganizationUser.Role>>') has no member 'owner'

query.filter(\OrganizationUser.roles ~~ [.owner]) // Type 'OrganizationUser.Roles' (aka 'Set<OrganizationUser.Role>') has no member 'owner'

The following example works, but is nonsense - it produces a bogus equality query:

query.filter(\OrganizationUser.roles ~~ [[.owner]])

The only thing that currently works is:

query.filter(\OrganizationUser.roles, .contains, [.owner])

I apologise if this isn't the right repository. It seems specific to PostgreSQL's array implementation.

[tanner0101]

Fluent 4 now supports the ~~ on any type conforming to Collection.

[Flatout73]

@tanner0101 How to use it? query.filter(\OrganizationUser.roles ~~ .owner) is still not working:

Binary operator '~~' cannot be applied to operands of type 'KeyPath<...>' and 'String'

[0xTim]

@Flatout73 contains needs an array to work so it should be query.filter(\OrganizationUser.$roles ~~ [.owner])

[Flatout73]

@0xTim Still not working :( I have similar example, but with string instead of enum:

It compiles only when [[query]], but results of the filter are wrong.

[0xTim]

What type is $tags?

[Flatout73]

@0xTim

    @Field(key: "tags")
    var tags: [String]

[0xTim]

@Flatout73 ah do you want to see if any of the tags match with query? That's more complicated and not something Fluent supports (because not all databases support it). You'll need to drop down to a custom filter and use something like https://stackoverflow.com/a/54069718/1823705

[Flatout73]

Yep, thank you, do you have example how to use custom filter? Is it like this: .filter(.custom("<some sql>"))

[tonyarnold]

@Flatout73 you can use it like this:

.filter(field, .custom("ilike"), "%\(queryString)%")

[Flatout73]

I have found solution:

.filter(DatabaseQuery.Field.path(["tags"], schema: "universities"), .custom("&&"), DatabaseQuery.Value.custom("'{\"\(query)\"}'"))

However, it will search if it is exact match, not just substring :(

[0xTim]

If you want substring searching on an array that's probably not going to fit into Fluent at all and you'll need to drop down to SQLKit or saw SQL