Ppx_rapper - join 2 tables and return a list

Learning
1

Hello,
I’m trying to do something trivial - write a select query that joins 2 tables and returns a list of the results

But I can’t come up with a query with the appropriate syntax, does anyone have experience with ppx_rapper?

From the github issue Using %list with function_out · Issue #28 · roddyyaga/ppx_rapper · GitHub :
I have 2 tables, with an id column and want to join them and return the results:

  let a =
    [%rapper
      get_many
        {sql|
           SELECT
             @int{a.id},
             @string{a.name},
             @int{b.id},
             @string{b.name}
           FROM public.table1 as a
           INNER JOIN public.table2 as b
           ON a.b_id = b.id
           WHERE
             a.id IN (%list{%string{ids}})
       |sql}]

this works, but it results in a tuple, which is not great (int * string * int * string) list

record_out doesn’t work, because id is the same in both tables:

Variable id is bound several times in this matching

function_out doesn’t work, because I get

Unbound value loaders

Not sure where this is coming from?
I noticed that not using %list compiles the ppx (WHERE a.id = %string{id}) (but doesn’t work for my use case)

Is it possible to use %list and function_out somehow?

2

With SQL you can rename tables, but also columns. Then

 [%rapper
      get_many
        {sql|
           SELECT
             a.id as @int{a_id},
             a.name as @string{a_name},
             b.id as @int{b_id},
             b.name as @string{b_name}
           FROM public.table1 as a
           INNER JOIN public.table2 as b
           ON a.b_id = b.id
           WHERE
             a.id IN (%list{%string{ids}})
       |sql}]

Should gives you columns which can be processed by record_out.

I use this feature to rename some computed columns (SELECT MAX(rowid) AS @int{count} for example), but it should be useful with record_out.

Note, in ppx/ppx_rapper.ml, the loaders parameter is involved in function_out handling. And one instance doesn’t seem to point to a bounded parameter. I should try a exemple to see if I have the same issue.

4 Likes
3

thanks Frederic, this is a nice solution to use record_out, for the time being I won’t use function_out

4

Thank you very much for this sample! I’ve been using rapper for some time, and have often wanted to easily rename columns, but what I tried (e.g. select @string{foo as bar}...) didn’t have the desired effect, so I’ve always fallen back to using a subquery to do the renaming I want. I’d never tried putting the rapper annotations around only the column aliases!