query |> where([x], x.created_by == ^"user") |> order_by([x], x.id) |> limit(3)

Ecto.Query.API based solution

Here goes an example script that shows how you can filter by associations. It should be the most optimal way.

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(:creator_id, references(:users))
      add(:name, :string)
      timestamps()
    end

    create table("user_projects", primary_key: false) do
      add(:project_id, references(:projects))
      add(:user_id, references(:users))
    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(:creator, User)
    field(:name, :string)
    many_to_many(:contributors, User, join_through: "user_projects")
    timestamps()
  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: "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
    Project
    |> Query.from(as: :project)
    # prevent duplicates if creator is also a contributor
    |> Query.distinct([project: project], project.name)
    # join assocs
    |> Query.join(:inner, [project: project], assoc(project, :creator), as: :creator)
    |> Query.join(:inner, [project: project], assoc(project, :contributors), as: :contributors)
    # filter projects based on its assocs
    |> Query.where(
      [contributors: contributors, creator: creator],
      contributors.name == "Foo" or creator.name == "Foo"
    )
    # limit number of projects
    |> Query.limit(2)
    |> Repo.all()
    |> IO.inspect()
  end

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

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

Please keep in mind that not all databases support distinct like it was used in code above, for example SQLite. I send a PostgreSQL based example as this is a default choice for an Elixir/Phoenix database.

Enum based solution

If for some reason you can’t use ecto’s query API like mentioned by others already then my example may be interesting for you:

defmodule Example do
  def sample(list, func \\ &Function.identity/1, max \\ :infinity)

  def sample(list, func, :infinity) when is_list(list) and is_function(func, 1) do
    Enum.filter(list, func)
  end

  def sample(list, func, max)
      when is_list(list) and is_function(func, 1) and is_integer(max) and max > 0 do
    list
    |> Enum.reduce_while({0, []}, fn element, {count, acc} ->
      case {count + 1, func.(element)} do
        {_count, result} when result in [nil, false] -> {:cont, {count, acc}}
        {^max, _result} -> {:halt, {count, [element | acc]}}
        {count, _result} -> {:cont, {count, [element | acc]}}
      end
    end)
    |> then(fn {_count, acc} -> Enum.reverse(acc) end)
  end
end

The code above is a bit big, but that’s because it’s really flexible. In short it’s a combination of Enum.filter/2 + Enum.take/2 in one function. With it you can filter only first n matching elements which is especially useful when working with big lists.

... |> Enum.take(3)

Here you go!

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]
    }
  ]
}

I don’t have any right to ask what I’m about to ask because I don’t know really anything about what you’re actually doing… but I’m not going to let that stop me.

Why not do the filtering in database query? It seems to me that you’re going to incur unnecessary latency transferring data between the database and the application; especially if they’re different servers and the data has to cross a network; and you’ll be processing the data (filtering) in a less efficient manner given that database servers are carefully designed to perform well with that workload. There are other reasons to do as much data processing in the database during retrieval but they kinda fall into the category of dealing with more data than necessary across the steps of the process.