Dev Maiqui 🇧🇷

Posted on May 10, 2022 • Updated on Jul 7

💧🔎 Sintaxes de consulta em Elixir: pipe X palavras-chave

#elixir #queries #database #braziliandevs

Existem duas sintaxes de consulta em banco de dados usadas na linguagem de programação Elixir. Neste post vamos comparar a sintaxe baseada em pipe (macro) e a sintaxe baseada em palavras-chave. Se você não souber ainda fazer as consultas, fique tranquilo, você vai conseguir aprender com os exemplos. Você pode encontrar sobre isso na documentação mas, neste post; vamos abordar exemplos em uma pequena API que criaremos.

⚙️ Criando o Projeto
- 🗂 Criando as tabelas
- ➕ Inserindo os dados
🔎 Consultando usuários ("users" X MyBlog.User)
- 👍 Like
- 🧩 Fragment com coalesce
🔎 Consultando posts do usuário (join)
- 👤 Posts sem usuário (left join)
- 🔛 on X assoc()
🎯 Conclusão

⚙️ Criando o Projeto

Link do projeto no github: https://github.com/maiquitome/learning_queries

Criando o projeto:

$ mix phx.new learning_queries --app my_blog

Entrando na pasta do projeto:

$ cd learning_queries

🗂 Criando as tabelas

Vamos criar as seguintes tabelas:

Para cada tabela, vamos criar o schema e a migration em cada comando abaixo:

$ mix phx.gen.schema User users first_name last_name

$ mix phx.gen.schema Post posts title body:text user_id:references:users

$ mix phx.gen.schema Comment comments post_id:references:posts body:text

➕ Inserindo os dados

Para inserir as chaves estrangeiras corretamente lembre de adicioná-las a função cast, senão esses campos ficarão sempre nulos:

No Post não coloque o user_id no validate_required, pois precisamos de um user_id nulo para testarmos o left join mais para frente, ou seja, um Post sem usuário atrelado.

Vamos também remover a obrigatoriedade do last_name, para testarmos o coalesce mais pra frente:

Copie para o seu arquivo seeds: https://github.com/maiquitome/learning_queries/blob/main/priv/repo/seeds.exs

Execute o comando abaixo para criar o banco de dados, rodar as migrations (criar as tabelas no banco) e inserir os dados do arquivo seeds:

$ mix ecto.setup

🔎 Consultando usuários ("users" X MyBlog.User)

Vamos criar as consultas em dois arquivos separados:

Em lib/my_blog/users_query_with_keyword.ex vamos colocar as consultas usando a sintaxe de palavra-chave (keyword):

defmodule MyBlog.UsersQueryWithKeyword do
  import Ecto.Query

  alias MyBlog.Repo

  def find_all_by_first_name(first_name) do
    # DICA: Podemos usar "users" ou MyBlog.User.

    # query =
    #   from u in "users",
    #     where: u.first_name == ^first_name,
    #     select: u.last_name

    query =
      from u in MyBlog.User,
        where: u.first_name == ^first_name

    Repo.all(query)
  end
end

Com "users": é obrigatório usar o select.
Com MyBlog.User: não é obrigatório usar o select, e sem o select retorna os schemas.

Em lib/my_blog/users_query_with_pipe.ex vamos colocar as consultas usando a sintaxe de pipe (macro):

defmodule MyBlog.UsersQueryWithPipe do
  import Ecto.Query

  alias MyBlog.Repo

  def find_all_by_first_name(first_name) do
    # "users"
    # |> where([u], u.first_name == ^first_name)
    # |> select([u], u.last_name)
    # |> Repo.all()

    MyBlog.User
    |> where([u], u.first_name == ^first_name)
    |> Repo.all()
  end
end

Vamos testar usando o iex:

$ iex -S mix

Resultado usando "users" com select:

iex> MyBlog.UsersQueryWithKeyword.find_all_by_first_name "Mike"
...
["Mago", "Shinoda", "Candys", "Tyson"]

Resultado usando MyBlog.User com select:

iex> MyBlog.UsersQueryWithKeyword.find_all_by_first_name "Mike"
...
["Mago", "Shinoda", "Candys", "Tyson"]

Resultado usando MyBlog.User sem select (retorna schema):

iex> MyBlog.UsersQueryWithKeyword.find_all_by_first_name "Maiqui"
...
[
  %MyBlog.User{
    __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
    first_name: "Maiqui",
    id: 1,
    inserted_at: ~N[2022-05-08 00:15:03],
    last_name: "Tomé",
    updated_at: ~N[2022-05-08 00:15:03]
  }
]

👍 Like

keyword:

def find_all_by_letter(letter) do
    like = "%" <> letter <> "%"

    from(u in MyBlog.User,
      where: like(u.first_name, ^like),
      or_where: like(u.last_name, ^like),
      select: [u.first_name, u.last_name]
    )
    |> Repo.all()
  end

pipe:

def find_all_by_letter(letter) do
    like = "%" <> letter <> "%"

    MyBlog.User
    |> where([u], like(u.first_name, ^like))
    |> or_where([u], like(u.last_name, ^like))
    |> select([u], [u.first_name, u.last_name])
    |> Repo.all()
  end

Resultado (lista de listas):

iex> MyBlog.UsersQueryWithPipe.find_all_by_letter "a"   
...
[["Maiqui", "Tomé"], ["Mike", "Mago"], ["Mike", "Shinoda"], ["Mike", "Candys"]]

Também podemos ter o resultado de uma lista de mapas:

Resultado (lista de mapas):

iex> MyBlog.UsersQueryWithPipe.find_all_by_letter "a"
...
[
  %{first_name: "Maiqui", last_name: "Tomé"},
  %{first_name: "Mike", last_name: "Mago"},
  %{first_name: "Mike", last_name: "Shinoda"},
  %{first_name: "Mike", last_name: "Candys"}
]

🧩 Fragment com coalesce

Vamos pesquisar todos os últimos nomes dos usuários, mas para o usuário que o last_name estiver vazio, vamos substituir por "não cadastrado".

No Postgres a função que faz isso se chama coalesce, no MySQL é o ifnull e no Oracle nvl. Para usar essas funções existe o fragment do Ecto. O fragment é usado sempre para injetar fragmentos SQL (e não SQL) nas consultas. Como estou usando o Postgres vou usar o coalesce neste exemplo:

O sinal de interrogação ? é substituído pelos valores passados após separados por ,.

keyword:

def find_all_and_if_null_replaces() do
    from(u in MyBlog.User,
      select: fragment("coalesce(?, ?)", u.last_name, "não cadastrado")
    )
    |> Repo.all()
  end

pipe:

def find_all_and_if_null_replaces() do
    MyBlog.User
    |> select([u], fragment("coalesce(?, ?)", u.last_name, "não cadastrado"))
    |> Repo.all()
  end

resultado:

iex> MyBlog.UsersQueryWithKeyword.find_all_and_if_null_replaces
...
["Tomé", "Mago", "Shinoda", "Candys", "Tyson", "não cadastrado"]

🔎 Consultando posts do usuário (join)

Vamos mostrar os posts de um usuário pelo último nome dele.

Em lib/my_blog/posts_query_with_keyword.ex:

keyword:

defmodule MyBlog.PostsQueryWithKeyword do
  import Ecto.Query

  alias MyBlog.{Post, Repo, User}

  def find_all_by_user_last_name(user_last_name) do
    from(p in Post,
      join: u in User, on: p.user_id == u.id,
      where: u.last_name == ^user_last_name
    )
    |> Repo.all()
  end
end

Em lib/my_blog/posts_query_with_pipe.ex:

pipe:

def find_all_by_user_last_name(user_last_name) do
    Post
    |> join(:inner, [p], u in User, on: p.user_id == u.id)
    |> where([p, u], u.last_name == ^user_last_name)
    |> Repo.all()
  end

Resultado:

iex> MyBlog.PostsQueryWithKeyword.find_all_by_user_last_name "Tomé"
...
[
  %MyBlog.Post{
    __meta__: #Ecto.Schema.Metadata<:loaded, "posts">,
    body: "Olá, esse é o primeiro post do Maiqui Tomé...",
    id: 1,
    inserted_at: ~N[2022-05-08 03:14:21],
    title: "Primeiro Post do Maiqui Tomé",
    updated_at: ~N[2022-05-08 03:14:21],
    user_id: 1
  },
  %MyBlog.Post{
    __meta__: #Ecto.Schema.Metadata<:loaded, "posts">,
    body: "Olá, esse é o segundo post do Maiqui Tomé...",
    id: 2,
    inserted_at: ~N[2022-05-08 03:14:21],
    title: "Segundo Post do Maiqui Tomé",
    updated_at: ~N[2022-05-08 03:14:21],
    user_id: 1
  }
]

👤 Posts sem usuário (left join)

Vamos escrever duas funções, uma para trazer todos os posts somente que contenham usuário atrelado (usando o inner) e uma função para trazer todos os posts incluindo sem usuário (usando o left).

keyword:

def find_all_with_user_only() do
    # DICA: inner_join == join

    from(p in Post,
      inner_join: u in User, on: p.user_id == u.id
    )
    |> Repo.all()

    # from(p in Post,
    #   join: u in User, on: p.user_id == u.id
    # )
    # |> Repo.all()
  end

  def find_all_even_no_user() do
    from(p in Post,
      left_join: u in User, on: p.user_id == u.id
    )
    |> Repo.all()
  end

pipe:

def find_all_with_user_only() do
    Post
    |> join(:inner, [p], u in User, on: p.user_id == u.id)
    |> Repo.all()
  end

  def find_all_even_no_user() do
    Post
    |> join(:left, [p], u in User, on: p.user_id == u.id)
    |> Repo.all()
  end

🔛 on X assoc()

Vamos usar o assoc() ao invés do on e vamos tentar buscar todos os posts apenas que tenham usuário atrelado:

keyword:

def find_all_with_user_only() do
    # from(p in Post,
    #   join: u in User, on: p.user_id == u.id
    # )
    # |> Repo.all()

    from(p in Post,
      join: u in assoc(p, :users)
    )
    |> Repo.all()
  end

pipe:

def find_all_with_user_only() do
    # Post
    # |> join(:inner, [p], u in User, on: p.user_id == u.id)
    # |> Repo.all()

    Post
    |> join(:inner, [p], u in assoc(p, :users))
    |> Repo.all()
  end

Ao testarmos receberemos um erro nos avisando que não foi possível encontrar a associação com users no schema MyBlog.Post:

iex> MyBlog.PostsQueryWithKeyword.find_all_with_user_only
** (Ecto.QueryError) lib/my_blog/posts_query_with_keyword.ex:17: could not find association `users` on schema MyBlog.Post in query:

from p0 in MyBlog.Post,
  join: u1 in assoc(p0, :users),
  select: p0

    (ecto 3.8.2) lib/ecto/repo/queryable.ex:205: Ecto.Repo.Queryable.execute/4
    (ecto 3.8.2) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3

E se invertêssemos? Vamos buscar todos os usuários apenas que tenham posts:

keyword:

def find_all_with_user_only() do
    # from(p in Post,
    #   join: u in User, on: p.user_id == u.id
    # )
    # |> Repo.all()

    # from(p in Post,
    #   join: u in assoc(p, :users)
    # )
    # |> Repo.all()

    from(u in User,
      join: p in assoc(u, :posts)
    )
    |> Repo.all()
  end

pipe:

def find_all_with_user_only() do
    # Post
    # |> join(:inner, [p], u in User, on: p.user_id == u.id)
    # |> Repo.all()

    # Post
    # |> join(:inner, [p], u in assoc(p, :users))
    # |> Repo.all()

    User
    |> join(:inner, [u], p in assoc(u, :posts))
    |> Repo.all()
  end

Ao testarmos receberemos um erro avisando que não foi possível encontrar a associação com posts no schema MyBlog.User:

iex> MyBlog.PostsQueryWithKeyword.find_all_with_user_only
** (Ecto.QueryError) lib/my_blog/posts_query_with_keyword.ex:22: could not find association `posts` on schema MyBlog.User in query:

from u0 in MyBlog.User,
  left_join: p1 in assoc(u0, :posts),
  select: u0

    (ecto 3.8.2) lib/ecto/repo/queryable.ex:205: Ecto.Repo.Queryable.execute/4
    (ecto 3.8.2) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3

Então vamos adicionar as associações:

Ao tentarmos buscar todos os usuários apenas que tenham posts vamos receber os dados certinho:

pipe:

def find_all_with_user_only() do
    # Post
    # |> join(:inner, [p], u in User, on: p.user_id == u.id)
    # |> Repo.all()

    # Post
    # |> join(:inner, [p], u in assoc(p, :users))
    # |> Repo.all()

    User
    |> join(:inner, [u], p in assoc(u, :posts))
    |> Repo.all()
  end

Mas ao tentarmos buscar todos os posts apenas que tenham usuário continuamos com o mesmo erro:

pipe:

def find_all_with_user_only() do
    # Post
    # |> join(:inner, [p], u in User, on: p.user_id == u.id)
    # |> Repo.all()

    Post
    |> join(:inner, [p], u in assoc(p, :users))
    |> Repo.all()

    # User
    # |> join(:inner, [u], p in assoc(u, :posts))
    # |> Repo.all()
  end

E agora? Como vamos solucionar esse problema? Na verdade deixamos um bug no nosso código, um pequeno detalhe. Como um Post pertence a um User, ao invés de :users precisamos tirar o s e deixar apenas :user:

Esse bug foi interesante para mostrar que podemos ficar confusos do porque não está funcionando. Precisamos estar atentos de como as associações estão construidas ao usar o assoc().

🎯 Conclusão

Conseguimos abordar vários exemplos e claro que existem muito mais, mas acredito que conseguimos pegar a essência neste post. Ao se deparar com uma consulta que não esteja aqui neste post, acredito que facilmente você encontre a solução na documentação.

Agora podemos partir para o estudo das composições das consultas e, para isso, deixarei duas dicas de leitura: