Where like syntax outside from()

hi, i have syntax like :

result =
  Enum.map(
    from(Schema, where: ^whitelisted_params)
    |> where(like("nama", ^"#{nama}%"))
    |> limit(5)
    |> Repo.all(),
    fn elem ->
      elem |> Map.from_struct() |> Map.delete(:__meta__)
    end
  )
  conn |> json(result) 

the whitelisted_params is come from Enum.map(fn {k, v} -> {String.to_atom(k), String.replace(v, ~r/[^\w\s]/, "")} end) and i want to add where like query to it,

with that code, the ecto query is :

[debug] QUERY OK source="table" db=1.7ms queue=0.1ms SELECT B0.id, B0.nama, FROMtableAS B0 WHERE (B0.id= ?) AND ('nama' LIKE ?) LIMIT ? [12570001, "JOHN K%", 25]

the ‘nama’ is not B0.nama , how to make it same like B0.nama

The where function receive the bindings as the second parameter. So in your example you should replace

where(like("nama"

with

where([schema], like(schema.nama

https://hexdocs.pm/ecto/Ecto.Query.html#where/3-expressions-example

1 Like

i can’t fit that syntax in my current code, what is [schema] ?, not like from(Schema) right ?

Huh? The syntax they gave looks correct sans needing a couple more )) at the end. What error where you getting?

from not valid query expression, etc
but, i need example usage of where like with my current code :confused:

Should be able to just literally put in what they put:

    from(Schema, where: ^whitelisted_params)
    |> where([schema], like(schema.nama, ^"#{nama}%"))
    |> limit(5)
    |> Repo.all()

What is the ‘precise’ error that you are having? from not valid query expression doesn’t make sense in this specific case?

is [schema] mean == [Schema] ?, undefined function nama & binding list should contain only variable

Heh, [schema] absolutely does not equal [Schema], you need to use the lowercase version since you are binding an existing table (you can name it anything, doesn’t need to be schema, could name it s if you want). You need to be very careful about capitalization in Elixir as it is both a case-sensitive language as well as prefix-uppercase means it’s a module atom and prefix-lowercase means it’s a binding.

1 Like

my bad, thank you

1 Like