Capturing Ecto query state across Enum.reduce() accumulator

Questions & Help Questions
,
1

I’m trying to filter a list of users by their org[anization] with filter_org here. So far with :paginate and :sort I haven’t had any problems because I didn’t need to do any table joins. But with :filter I need to join Users with Orgs and OrgUsers. I’m trying to accumulate the query with Enum.reduce()

When doing the join(if shortname is not "all"), the problem appears to be that I can’t capture the user, ou and org in from(user in User, join: ou in OrgsUsers, join: org in Org) so then I need to re-capture new vars under
{:filter, %{filter_org: shortname}}, query ->

I’m not really sure what to do here, it’s a bit beyond my grasp. Any suggestions? – Michael

Criteria looks like:

criteria: [ paginate: %{page: 1, per_page: 1000}, sort: %{sort_by: :id, sort_order: :asc}, filter: %{filter_org: "vbc"} ]

  def list_users(criteria) when is_list(criteria) do
    IO.inspect(criteria, label: "list_users criteria")
    query = if Enum.find(criteria, fn x -> x == {:filter, %{filter_org: "all"}} end) do
      from(u in User)
    else
      from(user in User, join: ou in OrgsUsers, join: org in Org)
    end

    # query = from(u in User)
    IO.inspect(query, label: "list_users query")

    Enum.reduce(criteria, query, fn
      {:paginate, %{page: page, per_page: per_page}}, query ->
        from q in query,
          offset: ^((page - 1) * per_page),
          limit: ^per_page

      {:sort, %{sort_by: sort_by, sort_order: sort_order}}, query ->
        from q in query, order_by: [{^sort_order, ^sort_by}]

      {:filter, %{filter_org: "all"}}, query ->
        query

      {:filter, %{filter_org: shortname}}, query ->
        IO.inspect(query, label: "0 query filter_org: shortname")
        from q in query,
          join: u in User,
          join: ou in OrgsUsers,
          join: org in Org,
          where: org.shortname == ^shortname,
          where: ou.org_id == org.id,
          where: u.id == ou.user_id
        IO.inspect(query, label: "1 query filter_org: shortname")
        query
    end)
    |> Repo.all()
  end
2

This is what named bindings are for:

3

That definitely put me in the right direction, thanks.

  def list_users(criteria) when is_list(criteria) do
    query = from(user in User, as: :user)

    Enum.reduce(criteria, query, fn
      {:paginate, %{page: page, per_page: per_page}}, query ->
        from q in query,
          offset: ^((page - 1) * per_page),
          limit: ^per_page

      {:sort, %{sort_by: sort_by, sort_order: sort_order}}, query ->
        from q in query, order_by: [{^sort_order, ^sort_by}]

      {:filter, %{filter_org: "all"}}, query ->
        query

      {:filter, %{filter_org: shortname}}, query ->
        query
        |> join(:left, [user: u], ou in OrgsUsers, on: u.id == ou.user_id)
        |> join(:left, [u, ou], rg in Org, on: ou.org_id == rg.id)
        |> where([u, ou, rg], rg.shortname == ^shortname)
    end)
    |> Repo.all()
  end