Virtual assoc field with preload_messages?

Phoenix Forum Questions / Help
1

I have a schema like this:

  schema "tags" do
    timestamps(type: :utc_datetime)
    has_many(:outbound_messages, Message,
      foreign_key: :from_tag_id,
      where: [direction: "outbound"]
    )

    has_many(:inbound_messages, Message,
      foreign_key: :to_tag_id,
      where: [direction: "inbound"]
    )

    field(:messages, :any, virtual: true)
  end

What I want, is to be able to load the messages field, which is a outbound_messages and inbound_messages together.

At present, I use this:

  def preload_messages(%Tag{id: id, messages: nil} = tag) do
    from(m in Message,
      where:
        (m.from_tag_id == ^id and m.direction == "outbound") or
          (m.to_tag_id == ^id and m.direction == "inbound"),
      order_by: [desc: m.inserted_at]
    )
    |> Repo.all()
    |> then(fn msgs ->
      %{tag | messages: msgs}
    end)
  end

But I’d like to also have preload_messages accepting an ecto query.

I tried this:

  def preload_messages(%Ecto.Query{} = query) do
    message_query = from(m in Message,
      where:
        (m.from_tag_id == parent_as(:tags).id and m.direction == "outbound") or
          (m.to_tag_id == parent_as(:tags).id and m.direction == "inbound"),
      order_by: [desc: m.inserted_at]
    )
    from(t in query,
      as: :tags,
      preload: [messages: ^message_query]
    )
  end

but it does not work as it is not an assoc. I tried using has_many but it also doesn’t work because ecto automatically try to use a foreign_key and I want both foreign_keys to be ORed.

I also tried embed_many but it complains with ** (ArgumentError) cannot preload embedded field :messages without also preloading one of its associations as it has no effect.

2

There are indeed no virtual assoc features in ecto. When using assocs ecto expects you to play by the rules of what it thinks assocs are.

The question is do you actually need to use preload though, or would a query with select and subquery solve your problem?

3

No I do not need preload, but I found no way to populate the virtual field when using query.

As I said, I can work around it with my first code snippet using Repo.all().

I was wondering if there way a way to achieve the same query composability with a function that take a query and return a query.

4

You could possibly use a custom select keyword in your query:

from t in Tag,
  join: im in Message,
  # as: :inbound_message,
  on: t.from_tag_id == ^message_id,
  join: om in Message,
  # as: :outbound_message,
  on: t.to_tag_id == ^message_id,
  select: %{
    tag: t,
    messages: im ++ om
  }

You might also be able to use your virtual field and select directly into it (just a guess).

From the above query:

  select: %Tag{
    t | messages: im ++ om
  }
5 
  field :tag_id, :id, virtual: true
  has_many :messages, Message

  def preload_messages(%Ecto.Query{} = query) do
    message_query = from(m in Message,
      where:
        (m.from_tag_id == parent_as(:tags).id and m.direction == "outbound") or
          (m.to_tag_id == parent_as(:tags).id and m.direction == "inbound"),
      order_by: [desc: m.inserted_at],
      select_merge: %{tag_id: parent_as(:tags).id}
    )
    from(t in query,
      as: :tags,
      preload: [messages: ^message_query]
    )
end
6

I tried using select and select_merge but it doesn’t seem to be supported to populate a list.

7

Using the tag_id virtual field trick doesn’t work either.

[error] Task #PID<0.5921.0> started from #PID<0.5907.0> terminating
** (Ecto.QueryError) deps/ecto/lib/ecto/association.ex:888: field `tag_id` in `where` is a virtual field in schema Taggy.Conversations.Message in query:

from m0 in Taggy.Conversations.Message,
  where: (m0.from_tag_id == parent_as(:tags).id and m0.direction == "outbound") or
  (m0.to_tag_id == parent_as(:tags).id and m0.direction == "inbound"),
  where: m0.tag_id == ^2,
  order_by: [asc: m0.tag_id],
  order_by: [desc: m0.inserted_at],
  select: {m0.tag_id, merge(m0, %{tag_id: parent_as(:tags).id})}
8

I think it’s your usage of that field in the query:

where: parent_as(:tags).id == ^tag_id,
select: {parent_as(:tags).id, merge(m, %{tag_id: parent_as(:tags).id})
9

Oh, I’m guessing that where clause and the order by clause also referencing m0.tag_id were added by Ecto itself. Places where I’m doing this kind of trickery, I’m using a preload function instead of query:

  def preload_messages(%Ecto.Query{} = query) do
    message_fun = fn tag_ids ->
      from(m in Message,
        inner_join: t in Tag, on: t.id in ^tag_ids and
          ((m.from_tag_id == t.id and m.direction == "outbound") or
            (m.to_tag_id == t.id and m.direction == "inbound")),
        order_by: [desc: m.inserted_at],
        select_merge: %{tag_id: t.id}
      )
    end

    from(t in query,
      as: :tags,
      preload: [messages: ^message_fun]
    )
  end
1 Like
10

Ah the function trick is working it is exactly what I was looking for!

Thanks.

It is just missing |> Repo.all() at the end of the function.