Error while compiling a where clause in ecto

Questions / Help
1

I’ve been working on some code, it’s like as follows -

defmodule SomeMod do
  defmacro check_and_replace(username) do
    quote do
      unquote(String.replace(username, ~r/\./, ""))
    end
  end

  def someFn(username) do
    query =
      from u in User,
        where: ([u], check_and_replace(u.username) == check_and_replace(^username)),
        select: u.username
      

    case Repo.one(query) do
      nil -> true
      _ -> false
    end
  end
end

I’m getting an syntax error as follows …

line 13:
  SyntaxError: syntax error before: ')'

Where am I going wrong in above code snippet

2

The where line here seems like it’s trying to be the macro form but it’s punctuated like the function form. The ([u], ...) part is what the compiler’s complaining about.

HOWEVER

This part isn’t going to work as desired either:

  defmacro check_and_replace(username) do
    quote do
      unquote(String.replace(username, ~r/\./, ""))
    end

String.replace is an Elixir function, not a SQL one.

What query are you trying to produce?

3

hey @al2o3cr I’m checking whether the username already exists in db. How do I fix this one? can you please give me a hint?

4

How do I use String functions in where clause?

5

:frowning:
Still couldn’t figure out

6

There’s a good reason for that. You cannot do that. Ecto queries are a DSL for SQL queries, which are sent to and executed in the database. The database has no idea about elixir. Either figure out how to do what you need to do with sql in the db or postprocess results after the query ran with Elixir.

3 Likes
7

No idea how to fix your orginal code in the format you are trying to use, but if you just want to check for existing matching values you can do below btw:

def search_users(query) do
  Repo.one(
    from u in User,
    where: fragment("lower(?) = lower(?)", u.username, ^query)
  )
end

This fragment basically converts your query to lowercase, and looks for a matching lowercase u.username

where: fragment("lower(?) = lower(?)", u.username, ^query)

lower(u.username) = lower(query)

Do you actually want to replace names as well? If so, why not just query the name to return the ID, then update the name for the matching ID? I’m a bit lost as to why you are trying to search and replace.

1 Like
8

@GazeIntoTheAbyss Thanks for your answer. I’ll try to explain the issue briefly -

For example, when you open this link - Redirecting... it redirects to Enrique Iglesias page… I’m trying to achieve the same in my application.

9

At best you’d normalize the username on insert and put that in a separate column next to the unaltered one. Then you don’t need to do that over and over again whenever you’re comparing to the username you got on a request. Normalizing the requested username can be done in elixir before placing it on the query.

1 Like
10

MIght need some more explanation, as your example just looks like 2 links with different names.

If your username is “enrique” the below will take you to website.com/enrique, but the link will be displayed as “Bob”.

<.link navigate={~p"/website.com/#{@user.username}"}>
    <%= "Bob" %>
</.link>
11

I think this is the best option… I’ll go with this answer

12

@GazeIntoTheAbyss yes, they are two different links, but pointing to same page. I’m building an API (i.e., I’m not using phoenix live view or templates). The frontend of the app resides on a different server.