Mnesia Complex Queries

Questions / Help
1

Hi! I’m using @sheharyarn’s Memento for integrating Mnesia for my Phoenix App. Having little to no experience in erlang, where should I look in the documentation for complex queries, I am storing a map in one of my column and I need to fetch by querying in that map, I can do this in Ecto using fragments but I’m wondering how to do the same using Mnesia.

2

Usually for complicated queries we would use qlc but I’ve not use it in combination with a map. YMMV.

Please update this thread with the results of your investigation.

2 Likes
3

Thanks for the suggestion but I lack understanding in Erlang stuffs. Haven’t found any leads in the meantime, I’ll be sticking with the elixir way of doing things and learn Erlang in the process. As a workaround I just stored the nested data I needed to query as a field in my Mnesia table.

4

Hey @jbrb. It’s absolutely possible doing that with the Erlang Matchspec, but fair warning, the syntax is pretty confusing. Take this table as example:

defmodule Nested do
  use Memento.Table, type: :ordered_set, attributes: [:id, :data]

  def seed do
    nested = [
      %Nested{id: 1, data: %{title: "Elixir",  type: :language,  stars: 15000}},
      %Nested{id: 2, data: %{title: "Phoenix", type: :framework, stars: 13000}},
      %Nested{id: 3, data: %{title: "Memento", type: :library,   stars: 160}},
      %Nested{id: 4, data: %{title: "Ecto",    type: :library,   stars: 4000}},
      %Nested{id: 5, data: %{title: "Ruby",    type: :language,  stars: 16000}},
    ]

    Memento.transaction(fn -> Enum.each(nested, &Memento.Query.write/1) end)
  end
end


Memento.Table.create(Nested)
Nested.seed()

This is how you’d match a nested map’s values using the matchspec in Memento:

match_head = {Nested, :"$1", %{title: :"$2", type: :"$3", stars: :"$4"}} 
guards = [{:==, :"$3", :language}]
result = [:"$_"]

Query.select_raw(Nested, [{ match_head, guards, result }])
# => [
#  %Nested{id: 1, data: %{title: "Elixir", type: :language, stars: 15000}},
#  %Nested{id: 5, data: %{title: "Ruby", type: :language, stars: 16000}},
#]

I created an issue in the project github with a lot more examples. You can check them out here:

Reply to this thread on ElixirForum if you have more questions.

6 Likes
5

@sheharyarn This solved my problem. I quite understand the syntax due to this ElixirSchool example I have read earlier.

Thank you!

6

Also if you have any lisp experience it becomes trivial, just replace { } with ( ) and your there. :grin: :wink:

3 Likes
7

Also it is possible to use :ets.fun2ms(...) function, which which translates an Elixir function to match spec expression - and Mnesia too, supports this, since it uses ets/dets:

iex(1)> p = :ets.fun2ms(fn ({{1,_,x,_},_} = rec) when 3 < x and x < 7 -> rec end)
[
  {{{1, :_, :"$1", :_}, :_}, [{:andalso, {:<, 3, :"$1"}, {:<, :"$1", 7}}],
   [:"$_"]}
]
4 Likes