Trying to add "ilike" to a dynamic "where" statement in Ecto

Phoenix Forum Questions / Help
,
1

I’ve had this function which dynamically build where term:

      defp filter_by_search_term(query, %{search_term: search_term}) when is_binary(search_term) do
        where(query, my_field: ^search_term)
      end

Now I want to replace it with ilike - the one that’ll compare any part of my_field with search_term. Hence, double %.

But none of this won’t work due to an error in syntax:

      defp filter_by_search_term(query, %{search_term: search_term}) when is_binary(search_term) do
        # where(query, my_field: ^search_term)
        
        # 1
        where(query, fragment("my_field ilike '%?%'", ^search_term))

        # 2
        where(query, fragment("my_field ilike %?%", ^search_term))
      end

What would be the correct version?

2

Why not use ilike/2?

1 Like
3

How would I use it in my case?

    where(query, ilike(address, "%?%"))

===>

    ** (Ecto.Query.CompileError) unbound variable `address` in query. If you are attempting to interpolate a value, use ^var
    (ecto 3.9.4) expanding macro: Ecto.Query.where/2
4

Something like this

defp filter_by_search_term(query, %{search_term: search_term}) when is_binary(search_term) do
  search = "%#{search_term}%"
  where(query, [t], ilike(t.my_field, ^search))
end
1 Like
5

That works.

Here now comes a new part.

If I query data from the DB manually, this query will return some data, as expected:

select * from my_table where my_field ilike '%TZ9%';

whereas what Ecto will generate – won’t. Here’s a simplified version of it:

SELECT c0."id", c0."a2".... FROM "my_table" AS c0 WHERE (c0."my_field" ILIKE $1) ["TZ9"];

Why not? Aren’t they identical?

The % .... % are missing, though. But why are they? If I insert %TZ9% into a textbox on html page in my application, some data now will be returned and this will be the expected result. Which means that Ecto won’t use % .. %

6

Did you add this?

7

You’re missin the percent symbols:

8

Show us your updated function

9

No. Where into? And why, provided that I’m now using ilike the way that you’ve shown me

My updated function is identical to what you’ve shown me

10

We need to see your actual function code to be able to compare the sql results. I suspect you bind search to what i said but probably still using search_term in your ilike

11

You’re asking us the same question a 2nd time.

We’ve already shown you the function. It’s in our first post. The updated version is in your own one.

12

I asked 2 times because you did not respond the first time. Saying your code is the exact same and it being the same is not always the case. I asked to paste it here so we can see and make sure. It should work if you pasted it the way I had it, so that is why I want to SEE for myself.

I am trying to help you out here…

If you don’t want to share, there’s not much more I can do for you.

13 
defp filter_by_search_term(query, %{search_term: search_term}) when is_binary(search_term) do
  search = "%#{search_term}%"
  where(query, [t], ilike(t.my_field, ^search))
end
14

How did you do this? You’ve secretely added search = "%#{search_term}%" in your original answer.

15

I did not secretly add anything. If you look at the replies right after you say it does not, I and @mayel ask if you added this. Also my reply has no edit counts.

1 Like
16

Okay. Then - it’s me who did notice it!