Ppx_rapper WHERE IN empty list

Ecosystem
,
1

I have the following table:

CREATE TABLE my_table (
    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    label text
);

INSERT INTO public.my_table(id, label)
VALUES
(1,'item 1'),
(2,'item 2'),
(3,'item 3');

in ppx_rapper (which uses caqti underneath) I have the following query:

type res = { id : int; label : string }
let get_my_items ~my_ids =
  [%rapper
    get_many
      {sql|
    SELECT @int{id}, @string{label}
    FROM my_table
    WHERE id IN (%list{%int{my_ids}});
    |sql}
      record_out]
    ~my_ids

This generally works well, unless my_ids is empty, which crashes the query with Cannot encode unit for <>: Empty list. (which makes sense since WHERE IN (); is invalid sql)

What are good ways to structure a ppx_rapper query, so that it accepts my_ids: int list, that if empty - returns all results?
ppx_rapper:

  • does not support optional lists (%list?)
  • does not support conditional queries (if my_ids is empty → skip the whole condition)

Is there some hidden syntax in ppx_rapper to allow that?

2

Not knowledgeable of ppx rapper but the syntax is invalid as you say for the empty case, so just make two different sql calls?

  • create a different query get_all
  • for the get_my_items just call get all for the empty list.

Alternatively create a session based temporary table, insert the values and use that in the IN clause. it might require extra round trips.

3

2 queries works for this toy example, but my real world use case is quite a lot more complex

Manually keeping 2 complex query implementations would work, but is not ideal, and if there’s an alternative- I would rather do it

It just sounds like a simple/solved problem, that I imagine I am not the first person to have encountered

4

for example for non-list arguments, it’s as simple as:

WHERE
(%int?{my_id} is NULL OR id = %int?{my_id})

to conditionally filter by my_id if it is provided

5

Can you do:

WHERE %bool{list_empty}
OR id IN (%list{%int{my_ids_nonempty}})

Where you prepare:

list_empty is just whether my_ids is an empty list or not

my_ids_nonempty is just my_ids if nonempty, else a list with a dummy element like [0]

E.g.

let list_empty, my_ids_nonempty = match my_ids with
  | [] -> true, [0]
  | _ -> false, my_ids

This way, if the list is empty then the first clause is hit and the clause after the OR is ignored, but it’s still syntactically valid because it contains a single element. If the original list is nonempty then it is used as the effective WHERE clause.

6

this is a nice workaround! I think I would do this if there isn’t a better way (that skips the check altogether, so that ‘id’ doesn’t need to be checked against ‘0’)

7

Slight simplification, based on sql - Empty IN clause parameter list in MySQL - Stack Overflow

let my_ids = -1 :: my_ids in (* Since ids will never be negative *)
...
id IN (%list{%int{my_ids}})

This way you guarantee the list is always non-empty, so you don’t need an extra clause to check emptiness.

1 Like
8

that’s simple enough to be a solution, thanks!

1 Like
9

I just realized that

let my_ids = -1 :: my_ids in (* Since ids will never be negative *)
...
id IN (%list{%int{my_ids}})

returns no results when my_ids is empty

while

let list_empty, my_ids_nonempty = match my_ids with
  | [] -> true, [0]
  | _ -> false, my_ids
WHERE %bool{list_empty}
OR id IN (%list{%int{my_ids_nonempty}})

returns all results, which is more often correct

10

I imagine it can be more correct in the sense that maybe you have a UI with a checkbox ‘Filter by users’ and if checked then you allow selecting a set of users. If not checked then the logic is don’t filter by users.

In that case it might make sense to have a domain-specific type or at least names that capture this meaning. E.g.

type 'a filter = All | Only of 'a list

Or

WHERE %bool{select_all}
OR id IN (%list{%int{select_ids}})