How to create a SQL using 'between' in Elixir Ecto

Question

I want to create a SQL using the keywork 'between' in Elixir Ecto.

I know how to create a sql using like

where: like(t.descript, ^some_description)

But when I try to do it in the same way as like

where: between(t.start_date, ^start_date, ^end_date),

I got the "not valid" error msg

** (Ecto.Query.CompileError) `between(t.start_date(), ^start_date, ^end_date)` is not a valid query expression.**

How can I do it the right way?

Thanks in advance!!

Appaloosa

Answer 1

I don't think Ecto provides a between clause. You could achieve your task by using

where: t.start_date >= ^start_date,
where: t.start_date <= ^end_date

Highmuckamuck

Do you mean start_date <= ^end_date? – Lindsylindy

Just for others, you can combine these into one line with an and. where: t.start_date >= ^start_date and t.start_date <= ^end_date. – Shortcircuit

Answer 2

You can use fragment to do this.

where: fragment("? BETWEEN ? AND ?", t.date, ^start_date, ^end_date)

https://hexdocs.pm/ecto/3.1.4/Ecto.Query.API.html#fragment/1

Envelope

Answer 3

You can also make your own between macro using the fragment answer @Horvo gave:

@doc """
Checks if the first value is between the second and third value.
"""
defmacro between(value, left, right) do
  quote do
    fragment("? BETWEEN ? AND ?", unquote(value), unquote(left), unquote(right))
  end
end

and use it like you wish you could:

where: between(t.start_date, ^start_date, ^end_date)

Gonsalves