Ecto.Query need help writing a query with expressions and dynamic queries

Questions / Help
,
1

I’m writing a query to filter out payments. We can filter on payment name, status, date from, date to, amount from, and amount to. I’ve gotten the queries working for all but amount from and amount to, as they are a bit more complex and involve using a join/subquery.

A payment relates to a payment_method which contains the information for the amount that was sent. It’s a one to one relationship, with payment referencing a payment_method's id through a funding_id.

I need to be able to filter all payments that fall within an amount range.

Here’s the part of the query I’m in need of help on:

def search(user_id, params) do
#MAIN QUERY
q = Payments.Schema
      |> where([user_id: ^user_id])
      |> where(^filter_name(params[:name]))
      |> where(^filter_status(params[:status]))
      |> where(^filter_date_from(params[:date_from]))
      |> where(^filter_date_to(params[:date_to]))
      |> join(:inner, ^filter_amount(params[:amount_from], params[:amount_to]))

#THIS FUNCTION DOESN'T CURRENTLY WORK  
defp filter_amount(amount_from, amount_to) when is_integer(amount_from) and is_integer(amount_to) do
    dynamic([t], t.funding_id in subquery(
        p = PaymentMethods.Schema
          |> where(p.amount >= ^amount_from)
          |> where(p.amount <= ^amount_to)
    ), t.funding_id = p.id)
  end
defp filter_amount(_amount_from, _amount_to), do: true

I get this error currently in the terminal:

== Compilation error in file lib/users/payments/payments.ex ==
** (Ecto.Query.CompileError) unbound variable `p` in query

Any help would be appreciated!

UPDATE

I’ve changed the code to the below:

def search(user_id, params) do
    #MAIN QUERY
    q = Payments.Schema
          |> where([user_id: ^user_id])
          |> where(^filter_name(params[:name]))
          |> where(^filter_status(params[:status]))
          |> where(^filter_date_from(params[:date_from]))
          |> where(^filter_date_to(params[:date_to]))
          |> join(:inner, [t], p in subquery(^filter_amount(params[:amount_from], params[:amount_to]), t.funding_id == p.id))


defp filter_amount(amount_from, amount_to) when is_integer(amount_from) and is_integer(amount_to) do
     PullTransactions.Schema
         |> where(pt.amount >= ^amount_from)
         |> where(pt.amount <= ^amount_to)
end
defp filter_amount(_amount_from, _amount_to), do: true

The error I’m getting in the terminal is this:

cannot use ^filter_amount(params[:amount_from], params[:amount_to]) outside of match clauses

1 Like
2

It’s a one to one relationship, with payment referencing a payment_method’s id through a funding_id.

If it’s one to one then you can just join it normally and then write another where for further filtering (you’d have both table bindings then).

1 Like
3

you should use without ^
|> join(:inner, [t], p in subquery(filter_amount(params[:amount_from], params[:amount_to]), t.funding_id == p.id))

also

defp filter_amount(amount_from, amount_to) when is_integer(amount_from) and is_integer(amount_to) do
     PullTransactions.Schema
         |> where([pt], pt.amount >= ^amount_from)
         |> where([pt], pt.amount <= ^amount_to)
end
4

@dokuzbir

Thanks for the help - I tried what you suggested and I get the following in the terminal.

These two warnings:

warning: variable "t" does not exist and is being expanded to "t()", please use parentheses to remove the ambiguity or change the variable name

warning: variable "p" does not exist and is being expanded to "p()", please use parentheses to remove the ambiguity or change the variable name

And I get this compile error:

Compilation error in file lib/users/payments/payments.ex ==
** (CompileError) lib/teller/users/payments/payments.ex:42: undefined function p/0
1 Like
5

as @yurko commented try that simpler way

|> join(:inner, [t], q in PullTransactions.Schema, t.funding_id == q.id) 
|> where([p,pt], pt.amount >= ^params[:amount_from] and pt.amount <= ^params[:amount_to] )
1 Like
6

My preference is to write things like this because it makes compossible filters and requires much less of the odd dynamic functions and looks more like the SQL I would write:

def search(user_id, params) do
  __MODULE__
  |> filter_by_user(user_id)
  |> filter_by_name(params[:name])
  |> filter_by_amount(params[:amount_from], params[:amount_to])
end

def filter_by_user(query, user_id) do
  from(p in query, where: p.user_id == ^user_id)
end

def filter_by_name(query, name) do
  from(p in query, where: p.name == ^name)
end

def filter_by_amount(query, amount_from, amount_to) do
  from(p in query, 
    inner_join: pm in PaymentMethods.Schema,
    on: p.funding_id = pm.id 
      and pm.amount >= ^amount_from 
      and pm.amount <= ^amount_to)
end
3 Likes