Search code examples
databaseselecterlangmnesia

Erlang Mnesia Equivalent of SQL Select FROM WHERE Field IN (value1, value2, value3, ...)

I have an mnesia table with fields say f1, f2, f3. Now if I were to select all the rows with the field value as V1, I would use mnesia:select and match specifications or a simple mnesia:match_object. Now I need to select all the rows which have V1, V2, V3 ... or Vn (a list of arbitrary length) as the value for field f1. In SQL I would do something like

SELECT * FROM tablename WHERE f3 IN (V1, V2, V3, ... , Vn)

How do I do this in mnesia?

Solution

  • And for the match-spec solution to this problem, if QLC is measured as not efficient enough.

    > ets:fun2ms(fun ({_,X}=E) when X == a -> E end).
[{{'_','$1'},[{'==','$1',a}],['$_']}]

    The ets:fun2ms is a parse transform that can translate some funs into matchspec values. I use it as a fast way to get the matchspec I want. We get a list with a matchspec that finds a tuple where the second element is a. We can repeat it for other keys.

    So lets populate an ets table with something to try this out on, then create a matchspec that matches only items with second element as 'a' or 'c'. (I enter this 

    ets:new(foo, [named_table]).

ets:insert(foo, [{1,a},{2,b},{3,c},{4,d}]).

Vs = [a,c].

MS = [{{'_','$1'},[{'==','$1',V}],['$_']} || V <- Vs].

ets:select(foo, MS).

    When I run this I get:

    [{1,a},{3,c}]