💧🔎 Sintaxes de consulta em Elixir: pipe X palavras-chave
Tags: elixir, queries, database, braziliandevs
Published: 2022-05-10 23:54:46

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](https://hexdocs.pm/ecto/Ecto.Query.html#content) mas, neste post; vamos abordar exemplos em uma pequena API que criaremos. 

* [⚙️ Criando o Projeto](#criando-o-projeto)
  - [🗂 Criando as tabelas](#criando-as-tabelas)
  - [➕ Inserindo os dados](#inserindo-os-dados)
* [🔎 Consultando usuários ("users" X MyBlog.User)](#consultando-usuarios)
  - [👍 Like](#like)
  - [🧩 Fragment com coalesce](#fragment-coalesce)
* [🔎 Consultando posts do usuário (join)](#consultando-posts-do-usuario)
 - [👤 Posts sem usuário (left join)](#posts-sem-usuario)
 - [🔛 on X assoc()](#on-x-assoc)
* [🎯 Conclusão](#conslusão)

[![A Jornada do Autodidata em Inglês](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pkoqkmh9hic17tnddjf6.png)](https://go.hotmart.com/R93865620U)

# ⚙️ Criando o Projeto <a name="criando-o-projeto"></a>

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

Criando o projeto:
```bash
$ mix phx.new learning_queries --app my_blog
```
Entrando na pasta do projeto:
```bash
$ cd learning_queries
```

# 🗂 Criando as tabelas <a name="criando-as-tabelas"></a>

Vamos criar as seguintes tabelas:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6qs9gbptre75auhptf98.png) 

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

```bash
$ mix phx.gen.schema User users first_name last_name 
```

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

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

# ➕ Inserindo os dados <a name="inserindo-os-dados"></a>

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ist6iz9u10bkue80xnsr.png)  

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:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6fzz7ew0bj9l9mfrhpsp.png)
 

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:
```bash
$ mix ecto.setup
```



# 🔎 Consultando usuários ("users" X MyBlog.User) <a name="consultando-usuarios"></a>

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):

```elixir
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):

```elixir
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:
```bash
$ iex -S mix
```

Resultado usando "users" com select:
```elixir
iex> MyBlog.UsersQueryWithKeyword.find_all_by_first_name "Mike"
...
["Mago", "Shinoda", "Candys", "Tyson"]
```

Resultado usando `MyBlog.User` **com** select:
```elixir
iex> MyBlog.UsersQueryWithKeyword.find_all_by_first_name "Mike"
...
["Mago", "Shinoda", "Candys", "Tyson"]
```

Resultado usando `MyBlog.User` **sem** select (retorna schema):
```elixir
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 <a name="like"></a>

keyword:
```elixir
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:
```elixir
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):
```elixir
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:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p5l8y85ds3dkm4rtdboy.png)

Resultado (lista de mapas):
```elixir
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 <a name="fragment-coalesce"></a>

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`](https://hexdocs.pm/ecto/Ecto.Query.html#module-fragments) 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:
```elixir
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:
```elixir
def find_all_and_if_null_replaces() do
    MyBlog.User
    |> select([u], fragment("coalesce(?, ?)", u.last_name, "não cadastrado"))
    |> Repo.all()
  end
```

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

 
# 🔎 Consultando posts do usuário (join) <a name="consultando-posts-do-usuario"></a>

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

Em `lib/my_blog/posts_query_with_keyword.ex`:

keyword:
```elixir
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:
```elixir
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:
```elixir
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) <a name="posts-sem-usuario"></a>

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:
```elixir
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:
```elixir
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() <a name="on-x-assoc"></a>

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

keyword:
```elixir
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: 
```elixir
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:
```elixir
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:
```elixir
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:
```elixir
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:
```elixir
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:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lr6yngtcyqm45dmjgtl5.png)

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

pipe:
```elixir
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
```
 
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bpvnn5gq1tiwxyv01pq9.png)

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

pipe:
```elixir
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
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xgz0uko3dss9tlizpge1.png)
 
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`:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/emymk304q0upqq16nchr.png)
 
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 <a name="conslusão"></a>


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](https://hexdocs.pm/ecto/Ecto.Query.html#content). 

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

* https://curiosum.com/blog/composable-elixir-ecto-queries-modules

* https://medium.com/flatiron-labs/how-to-compose-queries-in-ecto-b71311729dac