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