FunSQL "in" statement?

General Usage
1

In FunSQL, is there a way to use in statements with user-provided lists? E.g. I want to recreate the query select * from my_table where id in (1, 2). I’ve tried several approaches, such as q = From(:my_table) |> Where(Fun.in(Get.id, [1, 2])) , but that gives the error MethodError: Cannot convert an object of type Vector{Int64} to an object of type FunSQL.AbstractSQLNode .

I get a similar error if I try the @funsql macro

@funsql begin
    from(my_table)
    filter(id in [1, 2])
end
2

Try Fun.in(Get.id, 1, 2) instead.

1 Like
3

Thanks! Do you know if there’s any way to do this without splatting for large collections? Some of my collections are large enough that I get errors when trying to splat.

I can get the query to work with directly with SQLStrings by using where id = any($my_array...), but I was hoping to use a query builder.

4

As an alternative to the wonderful FunSQL, TidierDB.jl allows you to use in without splatting. I haven’t tried w huge collections, but I don’t think it would cause an issue.

vals = [1,  2,  3, 4];
@eval @chain t(table) begin
        @filter(value in $vals)
        @collect
    end
2 Likes
5

Interesting, I would think that if a collection is “large enough” to break splatting it is probably large enough to cause other issues downstream anyways – the SQL query contains the whole list as a string in the end. Turns out the splatting threshold is lower :slight_smile:

Try

Fun.in(Get.id, From((;x=[1,2,3])) |> Select(:x)))

should work!
Maybe there are more straightforward ways? Ping @xitology.
FunSQL.jl is generally very good with supporting SQL features, maybe we are just missing something…

1 Like