[Ur] SQL table.field IN list

[Adam Chlipala]

Yes, "IN" isn't supported by Ur/Web at the moment, and I would be glad 
to see a PR adding support!  It could probably be as simple as modeling 
the second argument of "IN" as a regular Ur/Web list, processed 
internally with a fold similar to what Simon wrote externally (but 
generating "IN" syntax instead of a compound Boolean condition).

On 1/12/19 10:50 AM, Artyom Shalkhakov wrote:
> Hi Simon,
>
> сб, 12 янв. 2019 г. в 16:15, Simon Van Casteren <simon.van.casteren at gmail.com>:
>> Hi,
>>
>> Am I correct in thinking that the standard library/compiler doesn't support SQL "IN" statements? It's something that I use a lot in my programs, and in psql for example you can just do:
>>
>> SELECT *
>> FROM uw_lesson_lessons
>> WHERE uw_id in (1, 2, 3)
>>
> Yes, it doesn't seem to be supported at the moment.
>
>> I made my own function to handle this, using a bunch of sql_or statements:
>>
>> fun inlist
>>        [a ::: Type]
>>        [otherfields ::: {Type}]
>>        [tablename :: Name]
>>        [columnname :: Name]
>>        [otherfields ~ [columnname = a]]
>>        (_ : sql_injectable a)
>>        (ids: list a)
>>      : sql_exp ([tablename = [columnname = a] ++ otherfields]) ([]) ([]) bool
>>    =
>>    List.foldl
>>      (fn id acc => (SQL {acc} OR {{tablename}}.{columnname} = {[id]}))
>>      (WHERE {[False]})
>>      ids
>>
>> I'm not sure about the performance characteristics of this though. Anyway, just wondering if it is already supported but I can't find it, or it's not supported at all?
>>
> Well I think you should really file an issue for this on GitHub. I'd
> like to work on it but it seems that it would require extending
> Ur/Web's grammar, then adding some way to handle the right-hand side
> of the operator -- which is an SQL tuple (right?), and we don't have
> those in the syntax elsewhere except the INSERT clause handling. This
> is a bit too much for my ability right now.
>
> Other than that, I'm really in favor of adding it.