Mnesia Bag table - How to wrtie a query to return results grouped by bag

Questions / Help
, ,
1

I am using Mnesia via Memento with the table type bag:

defmodule Tasks.Todos.Schema.V2.Todo do

  alias Tasks.Todos.Schema.V2.Todo

  use Memento.Table,
    attributes: [
      :uid,
      :user_uid,
      :date,
      :title,
      :done,
      :hash,
      :inserted_at,
      :updated_at,
    ],
    index: [
      :user_uid,
      :hash,
      :date,
      # :done,
    ],
    # https://learnyousomeerlang.com/ets#the-concepts-of-ets
    type: :bag
end

The intention of using the type bag is to keep all versions of each updated Todo.

A set of records in the database looks like this:

iex(28)> Tasks.Todos.list_by_date! "2020-06-26", "5de054556a456423c7d7a2778e8fb53fef43ce8d721db835575e76b436e6a804"
[
  %Tasks.Todos.Schema.V2.Todo{
    __meta__: Memento.Table,
    date: "2020-06-26",
    done: false,
    hash: "b1fed2ef2aea274923fb69a83dbc76fe670272c67e4f7cd68651aebfdc34a2ad",
    inserted_at: ~N[2020-06-26 07:55:52.362966],
    title: "Phoenix",
    uid: "24d06f5ece1025fa560e50f85ea23fe5ecb651e5506c4e28f1b216816a53ba0d",
    updated_at: ~N[2020-06-26 07:55:52.362966],
    user_uid: "5de054556a456423c7d7a2778e8fb53fef43ce8d721db835575e76b436e6a804"
  },
  %Tasks.Todos.Schema.V2.Todo{
    __meta__: Memento.Table,
    date: "2020-06-26",
    done: false,
    hash: "3b92c6310c3223215dada16e04cc024cfb7cb9d98626247c5d1eae69d12ed808",
    inserted_at: ~N[2020-06-26 07:56:50.480292],
    title: "Upgrade to Elixir 10",
    uid: "0a4e526765b6a4897ea83437f291009f6698035156f740e3e1b6c44118236fab",
    updated_at: ~N[2020-06-26 07:56:50.480292],
    user_uid: "5de054556a456423c7d7a2778e8fb53fef43ce8d721db835575e76b436e6a804"
  },
  %Tasks.Todos.Schema.V2.Todo{
    __meta__: Memento.Table,
    date: "2020-06-26",
    done: false,
    hash: "c8db6213e7e3cb853cf729052ee69eff9a7f95eb09413e60f0ab7146781dde57",
    inserted_at: ~N[2020-06-26 07:56:50.480292],
    title: "Upgrade to Elixir 10.3",
    uid: "0a4e526765b6a4897ea83437f291009f6698035156f740e3e1b6c44118236fab",
    updated_at: ~N[2020-06-26 07:56:58.465946],
    user_uid: "5de054556a456423c7d7a2778e8fb53fef43ce8d721db835575e76b436e6a804"
  },
  %Tasks.Todos.Schema.V2.Todo{
    __meta__: Memento.Table,
    date: "2020-06-26",
    done: false,
    hash: "7a39cc364031ad778d322ff0fe49feb2854534f1e07de1da52e4f2706bd284b5",
    inserted_at: ~N[2020-06-26 07:55:08.163917],
    title: "Elixir",
    uid: "05ce55338ac4f782c3da584944f23f02868442831d5d0d3432b86bb23616ed7c",
    updated_at: ~N[2020-06-26 07:55:08.163917],
    user_uid: "5de054556a456423c7d7a2778e8fb53fef43ce8d721db835575e76b436e6a804"
  }
]

When queering by date I get all todos as expected, but I would like the result to be grouped by bag.

So from the records above I would like to have the Todo with title "Upgrade to Elixir 10" and "Upgrade to Elixir 10.3" grouped together… Yes I know that is like asking for group by from SQL, but after all this a bag table, thus I think it is reasonable to have this expectation :wink:

My query:

Memento.transaction fn -> Memento.Query.select(Todo, [{:==, :user_uid, user_uid}, {:==, :date, date}]) end

I tried to read the Erlang docs about Mnesia but I was not able to figure out any way of accomplish what I want, maybe is because I don’t know Erlang or because its not really possible to do with the query syntax.

NOTE: I know that I can sort this out with ELixir when I get the records back, but that is not what I am asking for :wink:

2

I don’t know the solution but a workaround would be to do it in 2 steps:

  1. Get all keys with mnesia:all_keys/1 (or mnesia:dirty_all_keys/1)
  2. Read all keys with mnesia:read/3. This gives a list with the bag for every key.

This can be done in a transaction to lock the db I think.

3

Thanks for the workaround suggestion, but this approach is not feasible for production, only for a small set of records in the database, unless I am misunderstanding it.

4

If you are concerned about perfomance loss because of multiple reads, it seams that this is not a big problem as this post suggests (I did nit verify its correctness though):

5

Thanks for the link :slight_smile:

As it says on the link:

The table is really small, so non of ets:match nor select will make a difference.

In production if I have a table with millions of entries and apply the workaround then I don’t see how I cannot pay the toll in terms of performance?