Hello!
I am trying to implement a filter over multiple columns using Ecto.
For example, I have a schema similar to this:
schema "bookings" do
field :name, :string
field :email, :string
field :company, :string
end
And I’m trying to implement “infinite scroll” having optional filtering. In SQL I would do it like this when no filters are applied except id:
SELECT * FROM bookings WHERE id < :last_known_id ORDER BY id DESC LIMIT 20;
However, I also want to support filtering over multiple fields. In SQL it would look like this:
SELECT * FROM bookings WHERE id < :last_known_id AND (name ILIKE(:search_term) OR email ILIKE(:search_term) OR company ILIKE(:search_term)) ORDER BY id DESC LIMIT 20;
Where :search_term would be something like %foo%.
Now, I tried something similar with Ecto:
def search(last_id, search) do
from(b in Booking,
order_by: [desc: :id],
limit: 20,
where: b.id < ^last_id
)
|> name_filter(search)
|> email_filter(search)
|> company_filter(search)
end
defp name_filter(query, search)
when is_nil(search) do
query
end
defp name_filter(query, search) do
search_term = "%#{search}%"
query
|> or_where([b], ilike(b.name, ^search_term))
end
defp email_filter(query, search)
when is_nil(search) do
query
end
defp email_filter(query, search) do
search_term = "%#{search}%"
query
|> or_where([b], ilike(b.email, ^search_term))
end
defp company_filter(query, search)
when is_nil(search) do
query
end
defp company_filter(query, search) do
search_term = "%#{search}%"
query
|> or_where([b], ilike(b.company, ^search_term))
end
This however produces an SQL similar to this:
SELECT * FROM bookings WHERE id < :last_known_id AND name ILIKE(:search_term) OR email ILIKE(:search_term) OR company ILIKE(:search_term) ORDER BY id DESC LIMIT 20;
Notice the missing parentheses around OR statements making this act differently than desired.
I understand that I could change the code above to have only two search functions (one for when search parameter is not set) and then perform where([b], ilike(b.name, ^search_term) or ilike(b.email, ^search_term) ... ) however when filtering logic is more complicated then keeping separate functions would make code more readable in my opinion.
Is there a way to achieve WHERE something AND (something_else OR something_another_else) SQL easily in Ecto to solve the problem described above?
