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