Thank you , sure moving forword will take the markdown factor into account.
and also edited my queries as per markdowns
matt212
1
OvermindDL1
Awesome! The formatting looks perfect!
Please ask if you have any other questions or conversions, I like the challenges. 
3
matt212
Hi ,
I have a query, I would like to make dynamic Sql statements for multiple where clauses in elixir and pass it across to raw queries.
Please note i use sql.format in my nodejs code to prevent “SQL injection”
my existing javascript code looks likes this
var interns = [{
"name": "donnie"
}, {
"age": 16
}, {
"gender": "male"
}]
var selector = ""
interns.forEach(function(item, index) {
var searchvalue = item[Object.keys(item)];
var searchkey = Object.keys(item)[0];
if (selector == "") {
selector = "and " + (searchkey) + " LIKE " + "'%" + (searchvalue) + "%'";
} else {
selector = selector + " and " + (searchkey) + " LIKE " + "'%" + (searchvalue) + "%'";
}
})
console.log(selector)
, how can we achieve the same in elixir .
my progress is kinda stuck in how to trigger else clause and append to z variable
user_params=[%{"age" => 19}, %{"name" => "matt213"}, %{"sex" => "male"}]
sqlwhere=1;
z="";
for norm <- user_params do
for {k, x} <- norm do
IO.inspect sqlwhere
if sqlwhere <=1 do
intern=" and #{k} = '#{x}'"
x = "and #{k}="
y = "'#{x}'"
z = x <> " " <> y
IO.inspect z
sqlwhere=sqlwhere+1;
else
IO.inspect "unable to get here !"
# j = "#{k}"
# l = "#{x}"
# k = j <> " " <> l
# IO.inspect k
#list=list+"#{list} where #{k} = '#{x}'"
end
end
end
1
michalmuskala
Using strings for building SQL is risky. Fortunately with ecto, we don’t have to do it:
user_params = [%{"age" => 19}, %{"name" => "matt213"}, %{"sex" => "male"}]
user_params
|> Enum.map(&Enum.to_list/1)
|> Enum.reduce(Foo, fn [{key, value}], query ->
field = String.to_existing_atom(key)
search = "%#{value}%"
from q in query, where: like(field(q, ^field), ^search)
end
where Foo is the schema you want to search. I would also probably employ some more advanced mechanism for filtering allowed fields than String.to_existing_atom/1, but for an example it’s good enough.
3
matt212
Hi,
Apologise for earlier post of in process codebase and case scenarios this is my updated codebase;
ui = [%{"age" => 19}, %{"fullname" => "todd"}]
lists=ui
|> Enum.map(&Enum.to_list/1)
|> Enum.reduce(User, fn [{key, value}], query ->
field = String.to_existing_atom(key)
search = "%#{value}%"
Repo.all from(q in query, where: like(field(q, ^field), ^search), select: {q.fullname, q.age})
end)
IO.inspect lists
However , I am getting below exception for the same . could you please provide any pointers on same
** (exit) an exception was raised:
** (Protocol.UndefinedError) protocol Ecto.Queryable not implemented for [{"
Todd", 19}]
(ecto) lib/ecto/queryable.ex:1: Ecto.Queryable.impl_for!/1
(ecto) lib/ecto/queryable.ex:9: Ecto.Queryable.to_query/1
1
benwilson512
Author of Craft GraphQL APIs in Elixir with Absinthe
You don’t want to do Repo.all inside the Enum.reduce callback. The idea is that reduce is reducing over a single query, building it up with each iteration. The return value of the Enum.reduce is the query that you want to execute.
user_params = [%{"age" => 19}, %{"name" => "matt213"}, %{"sex" => "male"}]
query =
user_params
|> Enum.map(&Enum.to_list/1)
|> Enum.reduce(Foo, fn [{key, value}], query ->
field = String.to_existing_atom(key)
search = "%#{value}%"
from q in query, where: like(field(q, ^field), ^search)
end
users = Repo.all(query)
Or simply:
users =
user_params
|> Enum.map(&Enum.to_list/1)
|> Enum.reduce(Foo, fn [{key, value}], query ->
field = String.to_existing_atom(key)
search = "%#{value}%"
from q in query, where: like(field(q, ^field), ^search)
end
|> Repo.all
3
matt212
Affirmative , now it seems quite clear the functional usage of repo, thanks !
one more query what modification is required in code if the incoming user_params look like this
user_params = %{"age" => 19, "fullname" => "matt213", "sex" => "male"}
1
benwilson512
Author of Craft GraphQL APIs in Elixir with Absinthe
This is a simple enough change I think it’s best if you gave it a shot on your own. If you have something like
user_params = %{"age" => 19, "fullname" => "matt213", "sex" => "male"}
what does doing
user_params |> Enum.map(fn thing -> IO.inspect(thing) end)
print?
What does this tell you about the shape of each item when you map over a map, and how might that fit into your Enum.reduce call?
1
matt212
hi ,
below is my modified codebase , it throws below error
** (exit) an exception was raised:
** (Protocol.UndefinedError) protocol Ecto.Queryable not implemented for
[#Ecto.Query from u in HelloPhoenix.User, where: like(u.age, ^“%19%”)>, #Ecto.Query
from u in HelloPhoenix.User, where: like(u.name, ^“%matt213%”)>]
Modified Codebase
query=user_params
|> Enum.map(&Enum.to_list/1)
|> Enum.reduce(User, fn thing, query ->
for {key, value} <- thing do
field = String.to_existing_atom(key)
search = "%#{value}%"
from q in query, where: like(field(q, ^field), ^search)
end
end)
user = Repo.all(query)
IO.inspect user
1
benwilson512
Author of Craft GraphQL APIs in Elixir with Absinthe
Hey there.
What is thing? Why do you still have the Enum.map(&Enum.to_list/1) line? Did you run the line I suggested to help you understand what calling Enum.map on a map results in?
The issue here is that I think you’re simply combining bits of code people are giving you without understanding what each piece is doing, and what the values are at each point. Start at the very very beginning
query = user_params
|> Enum.map(fn x -> IO.inspect(x) end)
Figure out what that does, and what it means. Until you do that all this other stuff isn’t gonna help.
1