Phoenix Ecto conditional where

Phoenix Forum Questions / Help
,
1

Hi elixir awesome members!

I am doing a simple search input field. In Ecto part, I am checking if the user input is decimal or string with below case do method.

Because the CashflowEntry.amount is a :decimal type. I gotta seperate the query, one for decimal and one for string.

this seem like fine, but I believe there are better ways to achieve this? Example, 1 query only, but with a conditional where ce.amount == ^amount_decimal? i don’t know, i cannot think of better way.

  def search(search_term) do
    case Decimal.parse(search_term) do
      {amount_decimal, _} ->
        # Input is decimal
        wildcard_search = "%#{search_term}%"

        from(ce in CashflowEntry,
          where: ce.amount == ^amount_decimal or ilike(ce.note, ^wildcard_search)
        )
        |> Repo.all()

      _ ->
        # Input is alphanumeric
        wildcard_search = "%#{search_term}%"

        from(ce in CashflowEntry,
          where: ilike(ce.note, ^wildcard_search)
        )
        |> Repo.all()
    end
  end

Newbie, coming from python, in love with elixir phoenix ecto

I am sorry if this question has been asked before. I am not sure what the term to search for such solution. :smiley:

2

The simplest thing you can do in this particular situation, is refactor the code a little to move out the expressions which you are doing in either branch of the code:

  def search(search_term) do
    wildcard_search = "%#{search_term}%"
    query = case Decimal.parse(search_term) do
      {amount_decimal, _} ->
        # Input is decimal
        from(ce in CashflowEntry,
          where: ce.amount == ^amount_decimal or ilike(ce.note, ^wildcard_search)
        )
      _ ->
        # Input is alphanumeric
        from(ce in CashflowEntry, where: ilike(ce.note, ^wildcard_search))
    end

  Repo.all(query)
  end

And at this point, you might as well move the case statement to a helper function:

  def search(search_term) do
  search_term
  |> search_query()
  |> Repo.all()
  end
  
  # You might consider making this function public to test it separately
  defp search_query(search_term) do
    wildcard_search = "%#{search_term}%"
    query = case Decimal.parse(search_term) do
      {amount_decimal, _} ->
        # Input is decimal
        from(ce in CashflowEntry,
          where: ce.amount == ^amount_decimal or ilike(ce.note, ^wildcard_search)
        )
      _ ->
        # Input is alphanumeric
        from(ce in CashflowEntry, where: ilike(ce.note, ^wildcard_search))
    end
  end

There is to my knowledge no simple possibility to move the check of whether the input is a string or a number into the query to turn this code into a single query. (At least not without writing a raw SQL fragment, which is usually not recommended).

1 Like
3

I think dynamic/2 can help:

primary_condition =
  case Decimal.parse(search_term) do
    {amount_decimal, _} -> dynamic([ce], ce.amount == ^amount_decimal)
    _ -> dynamic(false)
  end

wildcard_search = "%#{search_term}%"

where = dynamic([ce], ^primary_condition or ilike(ce.note, ^wildcard_search))

CashflowEntry
|> where(^where)
|> Repo.all()
3 Likes
4

Possibly like this. Indeed, as per @fuelen, use of dynamic/2 is the key.

import Ecto.Query

def search(term) when is_binary(term)
  CashFlowEntry |> search_where(term) |> Repo.all()
end

defp search_where(query, term) when is_binary(term) do
  case Decimal.parse(term) do
    {amount, _} -> where(query, [x], x.amount == ^amount or ilike(x.note, ^term))
    _ -> where(query, [x], ilike(x.note, ^term))
  end
end
5

The dynamic/2 is the good one. But I decide to temporarily go with this first, because I am new to Phoenix Ecto and this is more straight forward for me.

will move to dynamic/2 once i am more comfortable with phoenix ecto.

Thanks !

1 Like