I have two records:
-record(foo, {timestamp, name}).
-record(bar, {timestamp, name}).
And I would like to execute a Mnesia query that mimics the following SQL query
SELECT f.* FROM foo f WHERE f.timestamp NOT IN ( SELECT b.timestamp FROM boo b)
What would be an efficient Mnesia equivalent?
Good Question ! Now, i have thought of two ways. One where we use qlc
and another where we use mnesia's own table iteration methods with accumulators
. Here is the first option:
%% Here i use 'qlc', with a guard %% which ensures that the timestamp %% in the given 'foo record' %% does NOT exist in table 'bar'
query()-> Is_not_in_bar = fun(Stamp)-> case mnesia:dirty_read({bar,Stamp}) of [] -> true; _ -> false end end,
Q = qlc:q([R || R <- mnesia:table(foo),
Is_not_in_bar(R#foo.timestamp) == true])), Fun = fun(QH)-> qlc:e(QH) end, mnesia:activity(transaction,Fun,[Q],mnesia_frag).
foo
while cross referencing each timestamp in the table bar
. If its not found in bar
then its added to the accumulated amount. look at this below
%% Here i iterate table 'foo' %% for each record i find, %% i try to cross reference %% its timestamp in table 'bar' %% If its not found, then i add that %% 'foo record' into the Buffer %% I accumulate this Buffer until %% the entire table 'foo' has been %% traversedI guess depending on the table size, the application and the user preference, each of these functions will have consequences. However, try both of them and see which one blends well into your app. The good thing is that this is entirely a read job, no writes, so i expect to be efficient enough. Success !
query_vsn2()-> Iterator = fun(#foo{timestamp = T} = Rec,Buffer)-> NewBuffer = case mnesia:dirty_read({bar,T}) of [] -> [Rec|Buffer]; _ -> Buffer end, NewBuffer end, Find = fun(Loop)-> mnesia:foldl(Loop,[],foo) end, mnesia:activity(transaction,Find,[Iterator],mnesia_frag).