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!!

shankardevy · Accepted Answer · 2015-05-19T08:51:57.913

22

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

edited May 19 '15 at 08:51

answered May 19 '15 at 08:22

shankardevy

4,290
5
32
49

Do you mean `start_date <= ^end_date`? – Ramon Snir May 19 '15 at 08:50
9

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`. – Dustin Jan 26 '17 at 01:12

score 14 · Answer 2 · answered Aug 16 '19 at 23:51

14

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

answered Aug 16 '19 at 23:51

Horvo

141
1
3

score 2 · Answer 3 · answered Oct 18 '22 at 08:31

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)

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

3 Answers3