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

Question

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?

Answer 1

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}]