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
1 Like
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
6

Sorry to resurrect an old thread but I asked this exact question recently and the author answered here: How to use `Fun.in` with list `Var` query parameter? · Issue #84 · MechanicalRabbit/FunSQL.jl · GitHub

It is Fun." IN "(Get.name, Var.NAMES)

7

To use the IN statement with user-provided lists in FunSQL, you can utilize the Fun.in function. However, when dealing with large collections, it’s advisable to avoid splatting due to potential performance issues. Instead, consider using Fun." IN " with a Var query parameter.

This approach allows you to pass a variable-length argument list without directly splatting, which can be beneficial for handling large datasets efficiently.