Ecto LIKE query with expression / function call

Phoenix Forum Questions / Help
, ,
1

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?

2

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

3

I believe this should work:

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

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

ilike(
  fragment("to_char(?, 'yyyy-mm-dd')", m.col2),
  ^pattern
)
4

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

5

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?

6

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

7

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
8

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.

9

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

10

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
11

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.

3 Likes
12

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:

13

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.