CODEWITHSUNDEEP
elixirphoenix-frameworkecto
Arijoon
Arijoon

Reputation: 2300

Ecto join on multiple conditions

Assuming I have three models:

user: has_one wallet
wallet: belongs_to user
transactions: belongs_to wallet

I am trying to get all the transactions for a given user. The SQL I can use is the following:

SELECT
   * 
FROM
   transactions AS t 
   JOIN
      wallets AS w 
      ON w.user_id = 1 
      AND w.id = t.wallet_id

Now in Ecto the following works but it does not replicate the query above:

wallet = assoc(user, :wallet)
q = from t in Transaction,
join: w in ^wallet,
where: t.wallet_id == w.id,
order_by: [desc: t.id],
select: t

I can't find any docs for creating assocs with AND cases. I've tried:

join: w in (^wallet and assoc(t, :wallet)),

but that results in a compile error. The goal would be to write this query on assoc only without manual id joins to let the relational abstraction stay within the models.

EDIT

following @daniel's suggestion and looking at dynamic/2 docs, I managed to build the query with multiple join conditions, by supplying extra conditions in the on:

id = user.id
q = from t in Transaction,
join: w in assoc(t, :wallet),
on: w.user_id == ^id,
order_by: [desc: t.id],
select: t

that snipped produces the following:

SELECT t0."id" 
  FROM "transactions" AS t0 
  INNER JOIN "wallets" AS w1 
    ON (w1."id" = t0."wallet_id") 
    AND (w1."user_id" = $1)

Upvotes: 4

Views: 2386

Answers (1)

Daniel
Daniel

Reputation: 2554

I will assume:

schema "transactions" do
  belongs_to :wallet, Wallet
  timestamps()
end

schema "wallet" do
  belongs_to :user, User
  has_many :transactions, Transaction
  timestamps()
end

schema "user" do
  has_one :wallet, Wallet
  timestamps()
end

Your query should look the following:

def user_transactions_query(id) do
  from tr in Transaction,
    join: wallet in assoc(tr, :wallet),
    join: user in assoc(wallet, :user),
    where: user.id == ^id
end

Upvotes: 4

Related Questions