Mnesia等同于SQL NOT IN [英] Mnesia equivalent of SQL NOT IN

问题描述

我有两条记录：

-record(foo, {timestamp, name}).
-record(bar, {timestamp, name}).

我想执行一个Mnesia查询，模拟以下SQL查询

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)

什么是高效的Mnesia等价物？

What would be an efficient Mnesia equivalent?

推荐答案

p>好问题！现在我已经想到了两种方式。一个我们使用 qlc ，另一个我们使用 mnesia自己的表累积方法与累加器。这是第一个选项：

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 ，同时交叉引用表 bar中的每个时间戳。如果在 bar 中找不到，则将其添加到累计金额。看看这下面

Another option would be to iterate table 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 
%% traversed

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).

我猜根据表的大小，应用程序和用户的偏好，这些功能都会有后果。但是，请尝试两者，并查看哪一个适合您的应用程序。好的是，这完全是一个阅读的工作，没有写作，所以我期望有效率。成功！

I 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 !

这篇关于Mnesia等同于SQL NOT IN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！