Ecto - dynamic order by sum in join table

Questions / Help
1

Hello! I have an application where a Sitemap has many WebPage associated to it. Each WebPage has integer columns for errors_count, warnings_count, muted_errors_countand muted_warnings_count.

The Sitemap has also columns for these counts but they’re virtual, and also has total_errors_count and total_warnings_count for the sum of errors/warnings and the muted errors/warnings:

  schema "web_pages" do
    field(:url, :string)

    field(:errors_count, :integer)
    field(:warnings_count, :integer)
    field(:muted_errors_count, :integer)
    field(:muted_warnings_count, :integer)

    belongs_to(:sitemap, Sitemap)
    belongs_to(:user, User)
  end

  schema "sitemaps" do
    field(:url, :string)

    field(:num_web_pages, :integer, virtual: true)

    field(:errors_count, :integer, virtual: true)
    field(:warnings_count, :integer, virtual: true)
    
    field(:muted_errors_count, :integer, virtual: true)
    field(:muted_warnings_count, :integer, virtual: true)

    field(:total_errors_count, :integer, virtual: true)
    field(:total_warnings_count, :integer, virtual: true)

    belongs_to(:user, User)
  end

OK, so in the sitemaps list for the user I want to be able to sort them by different columns, for example by total_errors_count or total_warnings_count, ASC or DESC.

I currently have this working to sort by the total errors count:

  def list_sitemaps(user, params \\ %{}) do
    from(
      s in sitemap_search_scope(params),
      join: w in WebPage,
      on: w.sitemap_id == s.id,
      where: [user_id: ^user.id],
      group_by: [s.id],
      select: %Sitemap{
        id: s.id,
        url: s.url,

        num_web_pages: count(w.id),
        errors_count: sum(w.errors_count),
        warnings_count: sum(w.warnings_count),

        muted_errors_count: sum(w.muted_errors_count),
        muted_warnings_count: sum(w.muted_warnings_count),

        total_errors_count: sum(w.errors_count) + sum(w.muted_errors_count),
        total_warnings_count: sum(w.warnings_count) + sum(w.muted_warnings_count),
      },
      order_by: [desc: fragment("?", sum(w.errors_count) + sum(w.muted_errors_count))]
    )
    |> Repo.paginate(params)
  end

  def sitemap_search_scope(%{"search" => search_term}) do
    wildcard_search = "%#{search_term}%"

    from(s in Sitemap, where: ilike(s.url, ^wildcard_search))
  end

  def sitemap_search_scope(_), do: Sitemap

But I’m not able to make the order_by dynamic depending on params["order_by"].

What’s the best approach to implement this?

2

Hi @jaimeiniesta! :wave:

Roughly this:

 def list_sitemaps(user, params \\ %{}) do
    from(
      s in sitemap_search_scope(params),
      join: w in WebPage,
      on: w.sitemap_id == s.id,
      where: [user_id: ^user.id],
      group_by: [s.id],
      select: %Sitemap{
        id: s.id,
        url: s.url,

        num_web_pages: count(w.id),
        errors_count: sum(w.errors_count),
        warnings_count: sum(w.warnings_count),

        muted_errors_count: sum(w.muted_errors_count),
        muted_warnings_count: sum(w.muted_warnings_count),

        total_errors_count: sum(w.errors_count) + sum(w.muted_errors_count),
        total_warnings_count: sum(w.warnings_count) + sum(w.muted_warnings_count),
      }
    )
    |> choose_order_by(params)
    |> Repo.paginate(params)
  end

  defp choose_order_by(query, params) do
    case params["order_by"] do
      "foo" ->
        # one type of order by here
      _ -> 
        # default
        from [s, w] in query,
          order_by: [desc: fragment("?", sum(w.errors_count) + sum(w.muted_errors_count))]
    end
  end
3 Likes
3

Awesome, this works great @josevalim :purple_heart: