Searching through multiple tables in Ecto

Questions / Help
,
1

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

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


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

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

		timestamps()
	end
2

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:

3

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: []
	end
1 Like