How to store multiple boolean values in ecto

General Prog (Archived)
, ,
1

I wonder how to store best multiple boolean values in ecto/any other orm. Lets assume it will be 10 true/false values which most of the time will be similar but not always and I won’t search by these values. I can either:

  • create 10 columns-which will most of the time store the same values-It does not fool right to me,
  • Store it as json in one column-then I can search this column
  • have an value/pointer that points to a separate table with the values-most of the time these are similar so it might be only a couple of rows so less indexing
  • in rails I found also a gem attr_bitwise that stores up to 32bit values as 1 integer - from my pov this makes the most sense
    https://medium.com/jobteaser-dev-team/rails-bitwise-enum-with-super-powers-5030bda5dbab
    What are your thoughts?
2

:wave:

There are also bit strings PostgreSQL: Documentation: 16: 8.10. Bit String Types available for postgres which could probably be combined with an “ecto” type to map to your domain logic.

# not tested
defmodule MyMultiBool do
  @moduledoc "stores multiple bool values in postgres"
  @behaviour Ecto.Type
  @compile {:inline, to_bool: 1, decode: 1}

  defstruct [:a, :b, :c]
  @type t :: %__MODULE__{
    a: boolean,
    b: boolean,
    c: boolean
  }

  @impl true
  def type do
    :bit
  end

  @impl true
  def cast(%__MODULE__{} = multi_bool) do
    {:ok, multi_bool}
  end

  def cast(value) do
    decode(value)
  end

  @impl true
  def load(value) do
    decode(value)
  end

  @impl true
  def dump(%__MODULE__{a: a, b: b, c: c}) do
    {:ok, <<to_bool(a)::1, to_bool(b)::1, to_bool(c)::1>>}
  end

  def dump(<<_::1, _::1, _::1>> = multi_bool) do
    {:ok, multi_bool}
  end

  defp decode(<<a::1, b::1, c::1>>) do
    {:ok, %__MODULE__{a: a == 1, b: b == 1, c: c == 1}}
  end

  defp decode(_other) do
    :error
  end

  defp to_bool(1), do: true
  defp to_bool(0), do: false
end
2 Likes
3

Thanks, I will try it.