Merge Queries

Phoenix Forum Questions / Help
1

Hi there!

I’m quite new to Phoenix and relational databases, so what I’m trying to do might not be the best approach.

The idea is simple: I manage Clients and Employees using my system. Both can get optional user accounts to log in. My User model looks like this (the important parts):

defmodule App.User do
  use App.Web, :model

  schema "users" do
    field :owner, :map, virtual: true
    belongs_to :client, App.Client
    belongs_to :employee, App.Employee

    timestamps()
  end
end

Now I’d like to load users with its owner, which can be a Client or an Employee.

The correct output would be generated by “merging” the following two queries, is that possible?

def preload_owner(users) do
  from u in users,
     where: not is_nil(u.employee_id),
      join: e in assoc(u, :employee),
    select: %{u | owner: e}
end

def preload_owner(users) do
  from u in users,
     where: not is_nil(u.client_id),
      join: c in assoc(u, :client),
    select: %{u | owner: c}
end

If there are other ways, I’m happy to get some ideas :slight_smile:

1 Like
2

There is an issue to have someone make a PR to add this feature native to Ecto at: https://github.com/elixir-ecto/ecto/issues/1549

However you can do it yourself via fragments.

EDIT: A ‘UNION’ fragment specifically.

1 Like
3

Thanks for the hint, but I’m actually still not sure yet how to do it.

I’d love something like the following, can I do the or part with fragments?

def preload_owner(users) do
  from u in users,
      join: e in assoc(u, :employee),
      join: c in assoc(u, :client),
    select: %{u | owner: e or c} # Does something similar exist?
end
1 Like
4

or in that place makes no sense in this context, however since you are preloading owner there then you could always make owner a function or virtual field on the schema and use the existing employee and client references (if you do not have those, add them).

You can even make something like a view to get a union of the employee and clients and then reference into ‘that’ table.

2 Likes
5

I know, my intention was to put the value there which isn’t nil. However, I think my current solution is fine now:

alias __MODULE__

@doc """
Gets a user's owner.
"""
def get_owner(%User{} = user) do
  user.client || user.employee
end

@doc """
Preloads a user's owner, which can be a `%App.Client{}` or an
`%App.Employee{}`. Use `App.User.get_owner` to get it.
"""
def preload_owner(query) do
  from u in query, preload: [:client, :employee]
end

Thanks for your help :slight_smile:

1 Like