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

If you need "complex" queries then QLC is a succinct way to state them. QLC is a parse-transform that allow you to use the list comprehension syntax on various tables, including mnesia tables.

You must have the following include, since it enables the parse-transform that make QLC possible in this source file.

-include_lib("stdlib/include/qlc.hrl").

The basic query form looks as the following, creating a query-handle and then evaluating the query.

QH = qlc:q([X || X <- Xs]),
qlc:eval(QH).

You can then use http://www.erlang.org/doc/man/mnesia.html#table-1 to acquire a QLC table back-ended in a mnesia table. So your query could be implemented as the following:

Vs = [V1,V2,...Vn],
QH = qlc:q([X || X <- mnesia:table(tablename), 
                 lists:member(X#tablename.f3, Vs)]),
qlc:eval(QH).

This requires a table-scan, which is not very efficient. If you have an index on the f3-column then you could instead turn it around and query first for entries with f3 = V1, then V2, etc, ... and merge the results.

PS

An alternative is to create a quite complex match-spec out of your list of V-values and run mnesia:select.

Answer 2

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

Answer 3

Christian pointed out nice solution but it can be made little bit simpler

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

so for you match you can make simpler match spec

4> ets:new(foo, [named_table]).
foo
5> ets:insert(foo, [{1,a},{2,b},{3,c},{4,d}]).
true
6> Vs = [a,c].
[a,c]
7> MS = [{{'_',V},[],['$_']} || V <- Vs].                
[{{'_',a},[],['$_']},{{'_',c},[],['$_']}]
8> ets:select(foo, MS).
[{1,a},{3,c}]