Zaid-Ajaj/Npgsql.FSharp

Is there a better way to create a WHERE IN clause?

aspnetde opened this issue · 6 comments

aspnetde commented

I need to create a query which uses WHERE IN, like:

SELECT * FROM X WHERE X.ID IN (a, b, c, d, ...)

Right now I am manually putting together the query as a string, which feels ... not optimal.

let set (blobIds: BlobId list) =
    let whereIn =
        System.String.Join(", ", blobIds |> List.map (fun blobId -> sprintf "'%O'" blobId.Value))
        
    connect()
    |> Sql.query (sprintf "SELECT b.* FROM blob b WHERE b.id IN (%s)" whereIn)
    |> Sql.executeAsync map
    |> Sql.throwOrUnwrap

Is there a built-in way that I missed?

Zaid-Ajaj commented

Hi there,

Postgres has a built-in called ANY for these types of queries:

SELECT * FROM blob WHERE blob.id = ANY(@blobIds)

Use the function Sql.intArray to provide the integers of the @blobIds input parameter

  • ❤️2
aspnetde commented

TIL :-) Thanks.

retendo commented

@Zaid-Ajaj I'm just migrating from SQLProvider to this library and I use mostly bigint/int64 as primary keys. What could I use instead of Sql.intArray in this case?

Zaid-Ajaj commented

Currently Sql.int64Array doesn't exist but it is just a shorthand for creating a NpgsqlParameter which you can construct yourself as follows where the parameter type is NpgsqlDbType.Array ||| NpgsqlDbType.Bigint

let identifiers = NpgsqlParameter("ids", NpgsqlDbType.Array ||| NpgsqlDbType.Bigint)
identifiers.Value <- [| 42L |]
// later on
Sql.parameters [ "ids", Sql.parameter identifiers ]

I haven't tested but this is the gist of it.

If you also know that your IDs of type bigint are small (less than Int32.MaxValuethen you can convert yourint64intointand intoSql.intArray` but it is risky if you have billions of records.

If the above don't work for you, please open a separate issue so we can track and fix it

  • 👍1
retendo commented

Perfect, thanks. I locally extended the Sql module with:

let int64Array (value: int64 []) =
    let param = NpgsqlParameter()
    param.NpgsqlDbType <- NpgsqlDbType.Array ||| NpgsqlDbType.Bigint
    param.Value <- value
    SqlValue.Parameter param

and then use it like

Sql.parameters [ "ids", Sql.int64Array ids ]

Should be similar to what has been done with Sql.timestampz in the library.

  • 👍1
Zaid-Ajaj commented

The only problem you might come across is that the analyzer doesn't understand new functions that you add yourself and might think you are missing parameters. I don't know if that is the case, but consider opening issues op those if they popup or just use Sql.skipAnalysis function when you don't care 😉

  • 👍1