Searching through multiple tables in Ecto

Hello, please i need assistance on how to search records within multiple tables.

I have a transaction table, account table and customer table.

A customer has many accounts and account has many transaction.

I want to be able to search transactions table by either customer name or account number.

Please kindly see below the schema for the tables


# Transaction Table
schema "transactions" do
		belongs_to :account, App.Clients.Account
		field :amount, :string
		field :transaction_date, :date

# Account Table
schema "accounts" do
		field :account_number, :string
		belongs_to :customer, App.Clients.Customer
		has_many :transactions, App.Banking.Transaction

# Customer Table
schema "customers" do
		field :firstname, :string
		field :surname, :string
		field :middlename, :string
		has_many :accounts, App.Clients.Account


Since Ecto is just an interface to SQL, the question then becomes, how would you do it in SQL itself? Once you write the SQL then it is usually trivial to convert it to auto-typing Ecto. :slight_smile:

Thanks for your response. I was able to figure out how it should be done. Although still a work in progress. Below is a sample of what the code looks like.

def search(query \\ __MODULE__, customer_surname) do
		from t in query,
		join: a in assoc(t, :account),
		join: c in assoc(a, :customer),
 		where: ilike(c.surname, ^"%#{customer_surname}%"), order_by: []
1 Like