How to use Elixir function in ecto query

Questions / Help
1

Hi Guys.

Am trying to fetch data from database where date string is 2days old and I have no Idea how to use the function I have written inside the ecto query.

function

def date_difference(date_string) do
    duration =
      date_string
      |> Timex.parse!("%Y-%m-%d", :strftime)
      |> Timex.to_datetime()
      |> DateTime.to_unix(:milliseconds)
      |> Timex.Duration.from_microseconds()

    Duration.diff(duration, Duration.zero(), :days)
  end

I hoped to use it like this.

  where: date_difference(p.payment_schedue) == 2

PS: Am a noobe :grinning:

2

I’m certainly not an Ecto expert, but I’m pretty sure that you can’t define custom functions in Elixir and use them inside Ecto queries (feel free to correct me if I’m wrong though).

What I would do for now is get your results and use a filter function to do what you want. Something like this

query = ...
Enum.filter(query, fn p -> date_difference(p.payment_schedule) == 2 end)

That way, you will get all of the results initially from the Ecto query, and then you will use Elixir to filter those based on your custom Elixir function. This might not be the best way to do it but I am fairly sure it would work.

1 Like
3

@jswny
Thank you for your answer.

After more research I have discovered it is not possible to use functions inside the query unless we use the Postgres defined types.

This is a great solution but I have chosen to refactor my db to use date and then use the interval type .
ie

  where:  fragment("now() - ? >= interval '-1 days' and now() - ? <= interval '1 days'",  p.payment_schedule, p.payment_schedule)
3 Likes
4

If you want interval’s I have a type library here:

/me loves intervals

5 Likes
5

What if I want to call a custom function?, for example; I want to check for 3 conditions when I’m joining two tables. Since this is lots of typing, I want to put that in a function.

However, the compiler complains that the binding variable is undefined.

  def filter(...) do
      # ...
      |> join(:inner, [tr, tt: tt], t in Tag,
        as: :tag,
        on: ^is_matching_tag(tt, t)
      )

    # ...
  end

  defp is_matching_tag(trt, tag) do
    Enum.map(2..0, fn level -> is_matching_tag(trt, tag, level) end)
    |> Enum.any?()
  end

  defp is_matching_tag(a,b,c) do
    # ...
  end

But I get the following error where the call occrus (the ecto join)

** (CompileError) ../trs.ex:62: undefined function t/0
    (elixir) src/elixir_locals.erl:108: :elixir_locals."-ensure_no_undefined_local/3-lc$^0/1-0-"/2
    (elixir) src/elixir_locals.erl:109: anonymous fn/3 in :elixir_locals.ensure_no_undefined_local/3
    (stdlib) erl_eval.erl:680: :erl_eval.do_apply/6

Can this be accomplished using Ecto?

thanks! (newbie here)

6

You‘ll need to provide this logic in sql. You cannot use elixir code within an ecto query. A query will be converted to sql and sent to the db.

7

Hi @shakram02

This not possible.

Am not sure what those conditions are but I would suggest using and or or to join those conditions. ie

 from v in query,
          join: vs in assoc(v, :visitor),
          join: vc in assoc(v, :visitor_company),
          join: h in assoc(v, :host),
          where:
            ilike(vs.first_name, ^"%#{term}%") or
              ilike(vs.second_name, ^"%#{term}%") or
              ilike(vs.email, ^"%#{term}%") or
              ilike(vs.phone, ^"%#{term}%") or
              ilike(h.first_name, ^"%#{term}%") or
              ilike(vc.name, ^"%#{term}%")

Hope that helps.

1 Like
8

For note, a large set of ilike's like that is going to be very slow especially as the data set grows. Converting it to using TS indexes or so would be a lot faster and various other lookup patterns for the others.

2 Likes
9

Hi @OvermindDL1

Thanks for the tip.

Do, you mean creating index with Ecto.Migration.index/2:.

10

Look at the tsvector/tsquery/etc types in PostgreSQL for efficient word style searches. :slight_smile:

I think someone made a library on hex.pm that simplifies it as well, but it’s optional, you can do it in straight Ecto too, but might be worth finding it. :slight_smile:

1 Like
11

Thanks a lot, lemme look at that.

1 Like
13

One solution would be to use a custom Ecto.Type on the :total field in your schema to convert the data when loading & casting the field.

14

Hi Sorry. I did not notice I deleted it by mistake. I was trying to edit it. But I’ll post again

I had a simple requirement I want to use the Enum function in a query. I have a lot of data where I need to add some of the records after fetching from the database.

One of those requirements was this I was getting this result and I needed to add all the values from this list of map’s

[
%{
   total: %{a: 1, b: 2}
},
%{
  total: %{c: 1, d: 2}
}
]

Thank you for suggesting Ecto.Type. I just take a look and I think it can work here. Thanks for the suggestion