SQL Inner Join Query for Has Many Through Association in Elixir

To set the stage for an answer to the question how to write SQL Inner Join Query for Has Many Through Association in Elixir? Let us try to briefly understand What SQL Inner Join and Has Many Through Association is?

What is SQL Inner Join?
SQL Inner Join, when applied on two tables on specific columns, returns the set of records whose column values match in both the tables.

For example: Let’s say we have the following tables — “orders” and “customers”. When we apply the below Inner Join query, it will return all the records from “orders” table whose value of column “customer_id” is equal to the value of column “id” in “customers” table.

SELECT * FROM orders
INNER JOIN customers
ON customers.id = orders.customer_id

What is Has Many Through Association?
A has many through association is used to set up a many-to-many connection with another model. This association indicates that the model which is declaring the association can be matched with zero or more instances of the other model by proceeding through a third model.

For example: Consider an application where a user has_many projects and a project has_many users. In such a case, to define a relationship between the two tables, we will create an intermediate table called project_users. Hence, the associations in Elixir between users and table will be as shown below:

defmodule MyApp.Schema.Project do
  use Ecto.Schema
  alias MyApp.Schema.User
  alias MyApp.Schema.ProjectUser
  schema "projects" do
    many_to_many :users, User, join_through: ProjectUser
  end
end
defmodule MyApp.Schema.User do
  use Ecto.Schema
  alias MyApp.Schema.Project
  alias MyApp.Schema.ProjectUser
  schema "users" do
    many_to_many :projects, Project, join_through: ProjectUser
  end
end
defmodule MyApp.Schema.ProjectUser do
  use Ecto.Schema
  alias MyApp.Schema.User
  alias MyApp.Schema.Project
 
  schema "project_users" do
    belongs_to :user, User
    belongs_to :project, Project
  end
end

How to write SQL Inner Join Query for Has Many Through Association in Elixir?
To write an SQL Inner Join query, we will use the join clause of Ecto.Query module in Elixir. There is an intermediate table between Project and User called ProjectUser to satisfy the has many through association. We will have to apply join on and in the following order:

1. Project and ProjectUser
2. ProjectUser and User

The ProjectUser table’s association with User and Project is belongs_to. Therefore it has project_id and user_id columns in it. These columns will be used to create inner join with id column of User and Project table.

For example: If we want to find out all the projects of a user whose name is Vikram . We will fire below query in Elixir.

defmodule MyApp.Model.Project do
  import Ecto.Query
  alias MyApp.Repo
  def user_projects_by_name do
    query =
      from project in Project,
        join: project_user in ProjectUser,
        on: project.id == project_user.project_id,
        join: user in User,
        on: user.id == project_user.user_id,
        where: user.name == "Vikram"
    Repo.all(query)
  end
end

❤️ Like, Share or Leave A Comment!

If you enjoyed this post, don’t forget to give it a 👏🏼, share it with a friend you think might benefit from it and leave a comment! Stay tuned for more exciting blogs on Flutter, Elixir, React, Angular, Ruby, etc.

Thanks !!