How to search database with using uuid?

Question

I am using phoenix-framework with postgresql.

For implementing search functionality, I used ilike ecto query and all of search functions work fine. For example, I have a function for searching products with product name and it works great.

def item_searh_results(language_id, name) do
  from(p in ItemLanguage, where: p.language_id == ^language_id 
                                and ilike(p.name, ^("%#{name}%")),
                        select: %{id: p.item_id,
                                  name: p.name}) |> Repo.all
end

I have a model, Sales and it uses uuid for its primary key.

I am trying to search sales by using ilike ecto query api like above function. So I made similar query.

from(p in Sales, where: ilike(p.id, ^("%#{id}%")))|> Repo.all

However, it returns an error that ** (Postgrex.Error) ERROR 42883 (undefined_function): operator does not exist: uuid ~~* unknown Is it possible to use ilike query to search ? If it is, what am I doing wrong here?

Thank you in advance.

Answer 1

I'm assuming you want to do a substring search in the hex representation of a UUID (e.g. "%a%" should match and "%e%" should not match for 015cd1d7-2794-4247-a24f-16b84ca9a3ac). You can do this by explicitly converting the UUID value to text and then doing ilike:

from(p in Sales, where: ilike(fragment("?::text", p.id), ^"%#{id}%")) |> Repo.all

(I also removed a redundant set of parentheses around the search string.)