dimitarvp

dimitarvp

Well I don’t have a lot of context on OP’s task but if they are worried about load spikes – or generally high load – then I’d definitely resort to “get what you need by a clever complex SQL / Ecto query and reshape it with Elixir to match your business code needs”.

Until we hear something more this is what it looks like they need.

1
Post #11
MatijaL

MatijaL OP

Yes, I believe this is what I need and that’s why I even started thinking about refactoring my queries. In your opinion, would using Enum.group_by be a better choice then using Enum.filter

query |> Enum.filter(fn x -> x.created_by == "user" end) |> Enum.take(3)

Well, I don’t really worry about load spikes right now but I want to understand those things now so I will be able to make better decisions when I will face those problems.

@sbuttgereit Thanks for taking the time to write such a thoughtful response, I will definitely take the time to read it carefully, try to fully understand it and learn from it.

0
Post #12
dimitarvp

dimitarvp

I am still not very clear on what you exactly do you need but no, don’t use Enum.take, use Ecto’s limit.

0
Post #13
Eiji

Eiji

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.

4
Post #14
MatijaL

MatijaL OP

Users can create many projects, also, they can contribute to many projects. Each project can have only one creator and only one contributor so there are two separate table fields (:created_by and :contributed_by).

project.ex
schema "projects" do
    fields ...

    belongs_to :created_by, User
    belongs_to :contributed_by, User
    timestamps()
end

Here I’m getting all user projects with one query and then use Enum functions to filter them

account_controller.ex
def index(conn, _params, current_user) do
   profile = Profiles.get_profile!(current_user.id)
   projects = Projects.list_user_projects(current_user)
   created_projects = Enum.filter(projects, fn x -> x.created_by_id == current_user.id end) |> Enum.sort_by(&(&1.inserted_at), Date) |> Enum.take(3)
   contributed_projects = Enum.filter(projects, fn x -> x.contributed_by_id == current_user.id end) |> Enum.sort_by(&(&1.inserted_at), Date) |> Enum.take(3)

   render(conn, "index.html", created_projects: created_projects, contributed_projects: contributed_projects)
end

projects.ex
def list_user_projects(user) do
    query = from p in Project, where: p.created_by_id == ^user.id, or_where: p.contributed_by_id == ^user.id, select: p

    Repo.all(query)
end

Alternative which I’ve created first is that I have two queries, one for created projects and one for contributed projects.

account_controller.ex
def index(conn, _params, current_user) do
   profile = Profiles.get_profile!(current_user.id)
   created_projects = Projects.list_created_projects(current_user)
   contributed_projects = Projects.list_contributed_projects(current_user)

   render(conn, "index.html", created_projects: created_projects, contributed_projects: contributed_projects)
end

projects.ex
def list_created_projects(user) do
    query = from p in Project, where: p.created_by_id == ^user.id, order_by: [desc: p.inserted_at], limit: 3, select: p
    Repo.all(query)
end
def list_contributed_projects(user) do
    query = from p in Project, where: p.contributed_by_id == ^user.id, order_by: [desc: p.inserted_at], limit: 3, select: p
    Repo.all(query)
end

In my template, I want to show 3 latest created projects and 3 latest contributed projects.

0
Post #15
dimitarvp

dimitarvp

I see. I’d go for your first approach plus add sorting by inserted_at in list_user_projects so you don’t have to sort in the Elixir code.

1
Post #16
dimitarvp

dimitarvp

Wait no, you should also add Ecto’s limit.

Hm, it seems you just need a rather more specialized function for this and not the generic list_user_projects:

def list_created_projects(user) do
  query =
      from p in Project,
      where: p.created_by_id == ^user.id,
      order_by: [asc: :inserted at],
      limit: 3,
      select: p

  Repo.all(query)
end

def list_contributed_projects(user) do
  query =
      from p in Project,
      where: p.contributed_by_id == ^user.id,
      order_by: [asc: :inserted at],
      limit: 3,
      select: p

  Repo.all(query)
end

This can probably be made with only one Ecto/SQL query but right now I can’t figure out how (just got up from a nap, lol).

0
Post #17
MatijaL

MatijaL OP

What if I create list_user projects() function where I make two queries, one for created projects, one for contributed projects and use union_all to combine the results?

1
Post #18
dimitarvp

dimitarvp

Problem is that I don’t remember now. I’d advise you to try really hard to do it with one query in general, yes.

1
Post #19
sbuttgereit

sbuttgereit

This is a good option and the first thing that comes to mind given all the info in the thread to this point. There are other things that could work too, but I don’t think you’d gain anything over the union. This results in a single database query and you get the benefits of keeping the filtering in the database.

1
Post #20