Ecto LIKE query with expression / function call

I need to perform a query, which in [Postgre]SQL would look similar to this:

SELECT * FROM mytable m WHERE m.col0 ILIKE '%asdf%' OR m.col1 ILIKE '%asdf%' OR cast_date_to_text(m.col2) ILIKE '%asdf%';

Now trying to convert this into Ecto syntax so that it can convert it back into SQL :slight_smile: Not much of a problem with col0 and col1:

		query |> where([m],
			ilike(m.col0, ^ilike) or
			ilike(m.col1, ^ilike) or

And now I need to put the cast_date_to_text(m.col2) ILIKE '%asdf%' part in place of “[…]” and that’s where I got stuck. How can I translate this part into valid Ecto?

You can use fragment to write raw SQL. It accepts placeholders.

I believe this should work:

  fragment("to_char(?, 'yyyy-mm-dd')", m.col2),

PS: ilike As a var name is a little confusing. How about pattern?

  fragment("to_char(?, 'yyyy-mm-dd')", m.col2),

Alright - so I understand that falling back to raw SQL is the only option in this case, right?

You can convert the date to whatever string format you like on the elixir side and use like.

Is this a string that contains a date or a date stored as a string?

It’s a date column and I need semi-fuzzy text search over it

In general you can use type/2 to cast between types but this case might be too specialized. For instance type(m.col2, :string) will try to cast col2 by doing m.col2::varchar in postgres. But not sure if that would work for a date type.

1 Like

In this particular case simple casting won’t work because index needs “IMMUTABLE” function/expression to work with. Or to be more precise, technically it would work but only doing Seq scans. I’ll remember the cast construct out of this though, tnx.

I am not sure I understand. Could you please elaborate a little?

Oh I see your problem. Yeah then you have to use fragment. Ecto doesn’t have any other way to call functions that are not specially built into the query API listed here: Ecto.Query.API — Ecto v3.8.4.

1 Like

Someone made a library for these kind of things btw: GitHub - hauleth/ecto_function: Simple macro for generating SQL's functions macro wrappers

I think it boils down to injecting fragments through macros. You might find it convenient to use their library or borrow their ideas to write your own macros.


fragment part is clear but to_char() won’t cut it. If you know how to make to_char() “IMMUTABLE” for date columns where there’s no locale stuff involved (like in the ‘YYYY-MM-DD’ format) then I’d gladly drop my custom function :slight_smile:

Thank you for the link - I’ll probably stay with raw SQL aka fragment for now. Don’t see it justified to pull-in another dependency or spend time implementing similar approach for a handful of DB functions I need to call on occasions. Basically wanted to be sure that there’s nothing in Ecto that would do the trick w/o falling back to SQL.