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

Sometimes using third table for association can be tedious and and bring unnecessary overcomes into app development process. We will try to avoid this by using Array columns introduced in PostgreSQL 9.1.

Let’s build micro app in Elixir Phoenix called Demo to demonstrate it:

$ mix demo
$ cd demo

Make sure that everything is ok by running tests:

$ mix test

Now we will generate Group model and Post model that belongs to a 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 have a user model that can be a member of many groups. Also user will have access to only posts that belong to user’s groups. Instead of generating classical many to many association through the third table lets add array column group_ids to users table:

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

Here is the User model:

Note that method changeset allows to change group_ids, so if we gonna use this method on editing user’s profile by the user himself then user is able to become a member of any group. If this isn’t suite for your application you should add additional logic to validate that :group_ids are subset of allowed group ids or simply exclude :group_ids from cast.

We can also create an index on group_ids:

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

You can create a separate migration for this.

Now design a method Post.accessible_by/2 that returns all posts from all user’s groups. We will create test for this:

And implementation:

Here we select all posts from all user groups.

We can go further and allow a Post be a part of several groups instead of only one. In this case table posts can have column group_ids as well as table users and any post will be accessible to the user if and only if user’s group_ids and post’s group_ids have at least one common group id.

We can use PostgreSQL overlap operator (&&) to achieve this. Updated Post model:

As an exercise you can update create table posts migration and Post model test, do not forget to add index on posts group_ids.

I hope this would be helpful to anyone. Thanks