Applying "if" inside a query

I am currently trying to apply an “if” within this query ( “between” and “like” ).

If the value is nil, it does not run.

How would you do that, could you help?

  def list_filter(params) do

    date_start = get_in(params, ["date_start"])
    date_end = get_in(params, ["date_end"])
    status = to_string(get_in(params, ["status"]))

    Ecdr
    |> where([c], fragment("? BETWEEN ? AND ?", c.calldate, ^date_start, ^date_end))
    |> where([c], like(c.disposition, ^status))
    |> order_by([c], desc: c.calldate)
    |> Repo.paginate(params)

  end

If which value is nil?


Aside of that, this code feels unidiomatic:

Usually the lense like accessors arent used for such a simple access, you could use params["date_start"] instead, or even more idiomatic match in the function head.

Just to demonstrate the general approach (it is not clear what your “if” is supposed to accomplish).

def list_filter(params) do
  date_start = get_in(params, ["date_start"])
  date_end = get_in(params, ["date_end"])
  status = to_string(get_in(params, ["status"]))

  query =
    Ecdr
    |> where([c], fragment("? BETWEEN ? AND ?", c.calldate, ^date_start, ^date_end))
    |> order_by([c], desc: c.calldate)

  query =
    if not is_nil(status) do
      where(query, [c], like(c.disposition, ^status))
    else
      query
    end

    Repo.paginate(query, params)
end

If the value is nil, it does not run.

  • What value is nil?
  • What doesn’t or isn’t supposed to run?

Applying some of @NobbZ’s suggestions:

defp where_status(query, %{"status" => status}) when not is_nil(status),
  do: where(query, [c], like(c.disposition, ^to_string(status)))

defp where_status(query, _no_status),
  do: query

def list_filter(%{"date_start" => date_start, "date_end" => date_end} = params) do
    Ecdr
    |> where(query,[c], fragment("? BETWEEN ? AND ?", c.calldate, ^date_start, ^date_end))
    |> where_status(params)
    |> order_by([c], desc: c.calldate)
    |> Repo.paginate(params)
end

and frankly BETWEEN isn’t a good reason for a fragment either

  def query_date(utc_from, utc_to) do
    {from_datetime, to_datetime} =
       if(utc_to < utc_from, do: {utc_to, utc_from}, else: {utc_from, utc_to})

    from(a in Album,
      where: a.inserted_at >= ^from_datetime and a.inserted_at <= ^to_datetime
    )

    # "inserted_at" is added by Ecto.Schema.timestamps/1
    # and defaults to NativeDateTime on the Elixir side
    # while being stored in PostgreSQL as "timestamp(0) without time zone"
    # (UTC TZ is implied)
    #
    #   a BETWEEN x AND y
    # is equivalent to
    #   a >= x AND a <= y
    #
    # Ecto to Elixir types
    # https://hexdocs.pm/ecto/Ecto.Schema.html#module-primitive-types
    #
  end

  def play do
    with {:ok, from_date} = NaiveDateTime.from_iso8601("2019-05-03 00:00:00"),
         {:ok  to_date} = NaiveDateTime.from_iso8601("2019-05-04 00:00:00") do
      IO.inspect(Repo.all(query_date(from_date, to_date)))
    end

    :ok
  end


or even

# TODO: convert strings to the appropriate Elixir date types
# so we can use straight Ecto expressions to fence "calldate"
# and take advantage of Ecto's typing
#
defp where_calldate(query, %{"date_start" => date_start, "date_end" => date_end}),
  do: where(query,[c], fragment("? BETWEEN ? AND ?", c.calldate, ^date_start, ^date_end))

defp where_status(query, %{"status" => status}) when not is_nil(status),
  do: where(query, [c], like(c.disposition, ^to_string(status)))

defp where_status(query, _no_status),
  do: query

def list_filter(params) do
    Ecdr
    |> where_calldate(params)
    |> where_status(params)
    |> order_by([c], desc: c.calldate)
    |> Repo.paginate(params)
end
5 Likes

Very good explanation,

this example helped me a lot, that’s right.

In case, all of these variables (date_start/date_end/status) is null and it executes another query.

Leaving thus:

controller

  def index(conn, params) do

 ######   cdr = Structure.list_cdr()

    page = Structure.list_cdr
      |> order_by([c], desc: c.calldate)
      |> Repo.paginate(params)

    filter = Structure.list_filter(params)

    render(conn, "index.html", cdr: page.entries, page: page, cdr: filter)

  end

structure.ex

def list_filter(params) do
  date_start = get_in(params, ["date_start"])
  date_end = get_in(params, ["date_end"])
  status = to_string(get_in(params, ["status"]))

  query =
    Ecdr
    |> where([c], fragment("? BETWEEN ? AND ?", c.calldate, ^date_start, ^date_end))
    |> order_by([c], desc: c.calldate)

  query =
    if not is_nil(status) do
      where(query, [c], like(c.disposition, ^status))
    else
    Ecdr
        |> order_by([c], desc: c.calldate)
    end

  query =
    if not is_nil(date_start) do
      where(query, [c], fragment("? BETWEEN ? AND ?", c.calldate, ^date_start, ^date_end))
    else
      Ecdr
         |> order_by([c], desc: c.calldate)
    end


    Repo.paginate(query, params)
end

Your code isn’t composing the query correctly - sometimes it’s replacing the query.

Please look at Composition

  # It's OK put the "ORDER BY" first
  # Ecto will know where to put it.
  # It doesn't actually "build" the query until it actually needs it.
  # Until then it just collects the bits and pieces for later use.
  #
  query = order_by(Ecdr, [c], desc: c.calldate)

  query =
    if not is_nil(status) do
      where(query, [c], like(c.disposition, ^status))
    else
      query
    end

  query =
    if not is_nil(date_start) do
      where(query, [c], fragment("? BETWEEN ? AND ?", c.calldate, ^date_start, ^date_end))
    else
      query
    end

or better

# structure.ex

# TODO: convert strings to the appropriate Elixir date types
# so we use straight ecto expressions to fence the "calldate"
# and take advantage of Ecto's typing
#
defp where_calldate(query, %{"date_start" => date_start, "date_end" => date_end})
  when not (is_nil(date_start) or is_nil(date_end)) do
  where(query,[c], fragment("? BETWEEN ? AND ?", c.calldate, ^date_start, ^date_end))
end

defp where_calldate(query, _no_dates) do
  query
end

defp where_status(query, %{"status" => status}) when not is_nil(status),
  do: where(query, [c], like(c.disposition, ^to_string(status)))

defp where_status(query, _no_status),
  do: query

def list_filter(params) do
    Ecdr
    |> where_calldate(params)
    |> where_status(params)
    |> order_by([c], desc: c.calldate)
    |> Repo.paginate(params)
end
7 Likes

You helped me a lot,
thank you.

Everything was very clear.

1 Like