Many-to-many attitudes without a third table in PostgreSQL using Elixir Ecto

Sometimes using the third table to link many to many is not necessary and adds additional complexity to the project development. We will try to avoid using the third table using an array type column added to PostgreSQL 9.1.

Let's create a small application on Elixir Phoenix called Demo to demonstrate:

$ mix phoenix.new demo $ cd demo 

Check that in order using the generated tests:
')

 $ mix test 

Now we will create a Group and Post model, which will belong to the Group:

 $ mix phoenix.gen.model Group groups name:string $ mix phoenix.gen.model Post posts name:string body:text group_id:references:groups 

Now we want to create a user model (User) that can belong to several groups. Also, the user will have access only to Post entries from their own groups. Instead of creating a third table for the users and groups relationship, let's add a group_ids column to the users table:

 $ mix phoenix.gen.model User users name:string group_ids:array:integer 

Here's what the User model looks like:

 # web/models/user.ex defmodule Demo.User do use Demo.Web, :model schema "users" do field :name, :string field :group_ids, {:array, :integer} timestamps() end @doc """ Builds a changeset based on the `struct` and `params`. """ def changeset(struct, params \\ %{}) do struct |> cast(params, [:name, :group_ids]) |> validate_required([:name, :group_ids]) end end 

Note that the changeset method allows you to change group_ids. So, if we use this method to edit the user profile by the user, the user will be able to add himself to any group. If this logic does not suit you, then you can add additional validation, confirming that the value of group_ids is a subset of the groups allowed for the user. Well, or you can simply prevent the user from changing the group_ids.

We can also add an index to the group_ids :

 CREATE INDEX users_group_ids_rdtree_index ON users USING GIST (group_ids gist__int_ops); 

You can create an additional migration for this.

Now we will plan the Post.accessible_by / 2 method, which will return all Post entries from the groups available to the user. To do this, create a test:

 # test/models/post_test.exs defmodule Demo.PostTest do use Demo.ModelCase alias Demo.{Post, Group, User} #    changeset test "accessible for user" do g1 = %Group{} |> Repo.insert! g2 = %Group{} |> Repo.insert! g3 = %Group{} |> Repo.insert! %Post{group_id: g1.id} |> Repo.insert! p21 = %Post{group_id: g2.id} |> Repo.insert! p22 = %Post{group_id: g2.id} |> Repo.insert! p31 = %Post{group_id: g3.id} |> Repo.insert! user = %User{group_ids: [g2.id, g3.id]} |> Repo.insert! post_ids = Post |> Post.accessible_by(user) |> Ecto.Query.order_by(:id) |> Repo.all |> Enum.map(&(&1.id)) assert post_ids == [p21.id, p22.id, p31.id] end end 

Method implementation:

 # web/models/post.ex defmodule Demo.Post do use Demo.Web, :model schema "posts" do field :name, :string field :body, :string belongs_to :group, Demo.Group timestamps() end @doc """ Builds a changeset based on the `struct` and `params`. """ def changeset(struct, params \\ %{}) do struct |> cast(params, [:name, :body]) |> validate_required([:name, :body]) end def accessible_by(query, user) do from p in query, where: p.group_id in ^user.group_ids end end 

Here we get all Post entries from all user groups.

We can go further and allow Post entries to belong to several groups at once. To do this, add the group_ids column to the posts table as well as for the users table, and delete the group_id column. Now the Post entry will be available to the user if and only if the Post entry and the user’s group_ids array have at least one common element in the group_ids array.

To do this, we can use the overlap operator in PostgreSQL . Modified Post Model:

 # web/models/post.ex defmodule Demo.Post do use Demo.Web, :model schema "posts" do field :name, :string field :body, :string field :group_ids, {:array, :integer} timestamps() end @doc """ Builds a changeset based on the `struct` and `params`. """ def changeset(struct, params \\ %{}) do struct |> cast(params, [:name, :body, :group_ids]) |> validate_required([:name, :body, :group_ids]) end def accessible_by(query, user) do from p in query, where: fragment("? && ?", p.group_ids, ^user.group_ids) end end 

As an exercise, you can also update the migration to create the posts table and the Post model tests. Remember to add an index to the group_ids column in the posts table.

I hope it will be at least useful to someone. Thank.