A many-to-many relationship without a third table in PostgreSQL using Elixir Ecto

Sometimes using a third table to link many-to-many is not necessary and adds to the draft of the additional complexity. Try to avoid the use of the third table using the a column of type array added in PostgreSQL 9.1

Let's create a small application on Elixir Phoenix with the name of the Demo to demonstrate:

the

$ mix phoenix.new demo
$ cd demo

Check that the order of using the generated tests:

the

$ mix test

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

the

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

Now we want to create a user model (User), which can belong to multiple groups. So the user will only have access to records of Post own groups. Instead create a third table to link users and groups let's add to the users table column group_ids:

the

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

Here's the User model:

the

# the 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 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 themselves to any group. If this logic does not suit You, you can add additional validation, certifying that the value group_ids is a subset of allowed for the user groups. Well, or you can just forbid the user to change group_ids.

We also can add an index on group_ids:

the

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

Unable to create a migration for this.

Gonna plan a method Post.accessible_by/2, which will return all records Post available for user groups. To do this, create a test:

the

# test/models/post_test.exs
defmodule Demo.PostTest do
use Demo.ModelCase

alias Demo.{Post, Group, User}

# Omitted tests of the method of the changeset

test "the 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:

the

# 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 records from all Post of user groups.

We can go further and allow the Post entry to belong to multiple groups. This will add a column group_ids to the table posts for table users and column group_id delete it. Now the entry Post will be available to the user, and then only when the array of group_ids the entry Post and the array of group_ids the user has at least one common element.

For this we can use operator overlap in PostgreSQL. Modified the Post model:

the

# the 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])

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 tests of the model. Don't forget to add index on column group_ids in the posts table.

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

Article based on information from habrahabr.ru