[Ur] Capturing classes of queries/Dynamic SQL queries

Wed Jun 25 14:10:22 EDT 2014

Your question inspired me to add one small parsing extension to Ur/Web!  
With that extension, available from the public Mercurial repo 
<http://hg.impredicative.com/urweb>, the following code gets the job done:

table images : { Id : int, Content : blob }
table tags : { Id : int, Tag : string }

datatype mode = Present | Absent
type condition = { Tag : string, Mode : mode }

type tag_query = sql_query [] [] [] [Id = int]

fun addCondition (c : condition) (q : tag_query) : tag_query =
     case c.Mode of
         Present => (SELECT I.Id AS Id
                     FROM ({{q}}) AS I
                       JOIN tags ON tags.Id = I.Id AND tags.Tag = {[c.Tag]})
       | Absent => (SELECT I.Id AS Id
                    FROM ({{q}}) AS I
                      LEFT JOIN tags ON tags.Id = I.Id AND tags.Tag = 
{[c.Tag]}
                    WHERE tags.Tag IS NULL)

fun withConditions (cs : list condition) : tag_query =
     List.foldl addCondition (SELECT images.Id AS Id FROM images) cs

fun main (cs : list condition) : transaction page =
     x <- queryX (withConditions cs) (fn r => <xml><li>{[r.Id]}</li></xml>);
     return <xml><body>
       {x}
     </body></xml>

On 06/21/2014 06:23 PM, orchidaceae phalaenopsis wrote:
> 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.impredicative.com/pipermail/ur/attachments/20140625/ca6f44a7/attachment.html>