How to get first x elements from the list?

Here you go! :smiling_imp:

Mix.install([:ecto_sql, :postgrex])

defmodule Repo do
  use Ecto.Repo, adapter: Ecto.Adapters.Postgres, otp_app: :my_app
end

defmodule Migration do
  use Ecto.Migration

  def change do
    create table("users") do
      add(:name, :string)
      timestamps()
    end

    create table("projects") do
      add(:contributed_by_id, references(:users))
      add(:created_by_id, references(:users))
      add(:name, :string)
      timestamps()
    end
  end
end

defmodule User do
  use Ecto.Schema

  schema "users" do
    field(:name)
    timestamps()
  end
end

defmodule Project do
  use Ecto.Schema

  schema "projects" do
    belongs_to(:contributed_by, User)
    belongs_to(:created_by, User)
    field(:name, :string)
    field(:type, :string, virtual: true)
    timestamps()
  end

  def new(tuple) do
    list = Tuple.to_list(tuple)
    :fields |> __schema__() |> Enum.zip(list) |> then(&struct(__MODULE__, &1))
  end
end

defmodule Example do
  alias Ecto.Query
  require Query

  def cleanup do
    Repo.stop()
  end

  def prepare do
    Application.put_env(:my_app, Repo,
      database: "example",
      # password: "postgres",
      pool_size: 10,
      show_sensitive_data_on_connection_error: true,
      username: System.get_env("USER")
      # username: "postgres"
    )

    Application.ensure_all_started(:ecto_sql)
    Application.ensure_all_started(:ecto_sqlite3)
    Repo.__adapter__().storage_down(Repo.config())
    Repo.__adapter__().storage_up(Repo.config())
    Repo.start_link()
    Ecto.Migrator.up(Repo, 1, Migration)
  end

  def sample do
    %{id: foo_id} = Repo.get_by!(User, name: "Foo")

    Project
    |> Query.from(as: :project)
    |> Query.join(:inner, [project: p], u in User,
      on: u.id == ^foo_id and (u.id == p.contributed_by_id or u.id == p.created_by_id),
      as: :user
    )
    |> Query.select(
      [project: project, user: user],
      {selected_as(
         fragment(
           "case ? when true then ? else ? end",
           project.created_by_id == user.id,
           "created projects",
           "contributed projects"
         ),
         :type
       ), fragment("array_agg(?)", project)}
    )
    |> Query.group_by(selected_as(:type))
    |> Repo.all()
    |> Map.new(fn {key, list} -> {key, Enum.map(list, &Project.new/1)} end)
    |> IO.inspect()
  end

  def seed do
    foo = Repo.insert!(%User{name: "Foo"})
    bar = Repo.insert!(%User{name: "Bar"})
    Repo.insert(%Project{contributed_by: foo, created_by: foo, name: "both"})
    Repo.insert(%Project{contributed_by: bar, created_by: foo, name: "creator"})
    Repo.insert(%Project{contributed_by: foo, created_by: bar, name: "contributor"})
    Repo.insert(%Project{contributed_by: bar, created_by: bar, name: "none"})
  end
end

Example.prepare()
Example.seed()
Example.sample()
Example.cleanup()

Above code prints:

%{
  "contributed projects" => [
    %Project{
      __meta__: #Ecto.Schema.Metadata<:built, "projects">,
      id: 3,
      contributed_by_id: 1,
      contributed_by: #Ecto.Association.NotLoaded<association :contributed_by is not loaded>,
      created_by_id: 2,
      created_by: #Ecto.Association.NotLoaded<association :created_by is not loaded>,
      name: "contributor",
      type: nil,
      inserted_at: ~N[2022-10-27 16:11:34.000000],
      updated_at: ~N[2022-10-27 16:11:34.000000]
    }
  ],
  "created projects" => [
    %Project{
      __meta__: #Ecto.Schema.Metadata<:built, "projects">,
      id: 1,
      contributed_by_id: 1,
      contributed_by: #Ecto.Association.NotLoaded<association :contributed_by is not loaded>,
      created_by_id: 1,
      created_by: #Ecto.Association.NotLoaded<association :created_by is not loaded>,
      name: "both",
      type: nil,
      inserted_at: ~N[2022-10-27 16:11:34.000000],
      updated_at: ~N[2022-10-27 16:11:34.000000]
    },
    %Project{
      __meta__: #Ecto.Schema.Metadata<:built, "projects">,
      id: 2,
      contributed_by_id: 2,
      contributed_by: #Ecto.Association.NotLoaded<association :contributed_by is not loaded>,
      created_by_id: 1,
      created_by: #Ecto.Association.NotLoaded<association :created_by is not loaded>,
      name: "creator",
      type: nil,
      inserted_at: ~N[2022-10-27 16:11:34.000000],
      updated_at: ~N[2022-10-27 16:11:34.000000]
    }
  ]
}
3 Likes