Ecto - Tips


(how to) get query SQL

  1. https://hexdocs.pm/ecto/Ecto.Adapters.SQL.html#to_sql/3
> import Ecto.Query
> query = from p in User
#Ecto.Query<from u in User>
> Ecto.Adapters.SQL.to_sql(:all, Repo, query)
{"SELECT u0.\"id\", u0.\"name\" FROM \"users\" AS u0", []}

(how to) perform bulk updates

  1. https://adamdelong.com/bulk-update-ecto

Repo.update_all/3

performing bulk updates with Repo.update_all/3 is not super fast - say, it took about 5 minutes to run this query against 2.3M rows:

(from s in MyApp.Shop, where: s.status == ^:fetched)
|> MyApp.Repo.update_all([set: [status: :linked]], [timeout: 300_000])

(how to) use atoms in queries

https://github.com/elixir-ecto/ecto/pull/954#issuecomment-139908835

Thank you but if people want to have atoms that become strings behind the scenes, or even an integer, reproducing enum types, they can interpolate the atom: ^:foo. I wouldn’t have it as a literal or as part of the query syntax exactly because it is not supported by any type by default. :)

import Ecto.Query

def pending do
  Shop
  |> where(status: ^:pending)
  |> Repo.all()
end

(how to) update record by ID

  1. https://elixirforum.com/t/ecto-why-repo-get-before-repo-update/12043

there is no way to do it:

(how to) use nested queries

nested query = subquery = inner query.

nested_query =
  from p in Post,
    left_join: c in assoc(p, :comments),
    group_by: p.id,
    select: %{id: p.id, comment_count: count(c.id)}

query =
  from p in subquery(nested_query),
    where: p.comment_count > 10

(how to) select difference in dates

say, Campaign schema has data JSONB field which always contains start_time object field while end_time field might be missing.

it’s necessary to calculate difference in days between these object fields - if end_time field is not specified, assume it to be equal to present moment.

now = DateTime.utc_now() |> DateTime.to_iso8601()

from c in Campaign,
  select: %{
    id: c.id,
    duration: type(
      fragment(
        "DATE_PART('day', (?)::timestamp - (?)::timestamp)",
        coalesce(fragment("?->>?", as.data, "end_time"), ^now),
        fragment("?->>?", as.data, "start_time")
      ),
      :integer
    )
  }

it’s also possible to cast JSONB object fields to timestamp type (timestamp without time zone) with Ecto.Query.API.type/2 but it looks a little bit more verbose:

from c in Campaign,
  select: %{
    id: c.id,
    duration: type(
      fragment(
        "DATE_PART('day', ? - ?)",
        type(
          coalesce(
            fragment("(?->>?)", as.data, "end_time"),
            :utc_datetime
          ),
          ^DateTime.utc_now()
        ),
        coalesce(
          fragment("(?->>?)", as.data, "start_time"),
          :utc_datetime
        )
      ),
      :integer
    )
  }

(how to) create/drop not-null constraint for foreign key column

  1. https://hexdocs.pm/ecto_sql/Ecto.Migration.html#modify/3
  2. https://www.postgresql.org/docs/11/ddl-constraints.html#id-1.5.4.5.6

gotchas:

def up do
  drop constraint(:yd_reports, "yd_reports_yd_client_id_fkey")

  alter table(:yd_reports) do
    modify :yd_client_id,
            references(:yd_clients, on_delete: :delete_all),
            null: false
  end
end

def down do
  drop constraint(:yd_reports, "yd_reports_yd_client_id_fkey")

  alter table(:yd_reports) do
    modify :yd_client_id,
            references(:yd_clients, on_delete: :delete_all),
            null: true
  end
end