[Ur] Capturing classes of queries/Dynamic SQL queries

[orchidaceae phalaenopsis]

Hello,

A bit stuck on something, hopefully someone can give some guidance!

To perform a search in our application the user inputs a list of
positive terms and a list of negative terms which will be turned into
a SQL query. The general pattern can be inferred from this example:

val query a b c d e =
    (SELECT images.Id
     FROM images
       JOIN tags AS P1 ON P1.Id=images.Id
       JOIN tags AS P2 ON P2.Id=images.Id
       JOIN tags AS P3 ON P3.Id=images.Id
       LEFT JOIN tags AS N1 ON N1.Id=images.Id AND N1.Tag={[d]}
       LEFT JOIN tags AS N2 ON N2.Id=images.Id AND N2.Tag={[e]}
     WHERE P1.Tag={[a]}
       AND P2.Tag={[b]}
       AND P3.Tag={[c]}
       AND N1.Tag IS NULL
       AND N2.Tag IS NULL)

I would like to generate queries like this from lists containing
positive and negative terms. It doesn't seem possible to do this
directly though because there is no source of computed names (i.e. we
can't have functions int -> Name).

If I limit the possible searches to <n positive terms and <m negative
terms then it would be possible to just hard code n*m queries. This is
what we're doing right now and it's working fine but for a new feature
we need to double the number of queries.

If I pass in two records whose fields are used to generate the query
with row-type metaprogramming techniques I think that I only need to
hard code n+m ways of converting lists into records so I'll probably
go with this solution - as long as this is the right way to do this.
The types involved are pretty scary and difficult to put together so I
wanted to be sure I wasn't doing something strange.

I've started to work on the record solution and by inspecting the
output of urweb with "-stop parse" I managed to correct the output a
bit and get this accepted:

val query' a =
    sql_query {Rows =
           sql_query1 [[]]
              {Distinct = False,
               From = sql_inner_join
                      (sql_from_table [#Images] images)
                      (sql_from_table [#P1] tags)
                      (sql_binary sql_eq
                          (sql_field [#P1] [#Id])
                          (sql_field [#Images] [#Id])),
               Where = sql_binary sql_eq
                          (sql_field [#P1] [#Tag])
                          (sql_inject a),
               GroupBy = sql_subset_all [(_ :: {{Type}})],
               Having = sql_inject True,
               SelectFields = sql_subset_all [(_ :: {{Type}})],
               SelectExps = {}},
               OrderBy = sql_order_by_Nil [(_ :: {Type})],
               Limit = sql_no_limit,
           Offset = sql_no_offset}

So would this be the right way to build our dynamic query? It's a
shame not to use the nice built-in query syntax but it looks like I
have to get one level lower. Thanks for any input on this.