Querying deeply nested associations

Phoenix Forum Questions / Help
,
1

Hello, I’m new to Phoenix and Ecto and having hard time making my own queries, hope you can help me with this one.

My situation is pretty common: I’ve a User which have Relationship(s)

tdefmodule Livechat.Accounts.User do
  use Ecto.Schema
  import Ecto.Changeset

  alias Livechat.Accounts.User
  alias Livechat.Relationship
  alias Livechat.Chat.Message

  schema "users" do
    field :email, :string
    field :password, :string, virtual: true, redact: true
    field :hashed_password, :string, redact: true
    field :confirmed_at, :naive_datetime

    has_many :sent_messages, Message, foreign_key: :from_id
    has_many :received_messages, Message, foreign_key: :to_id

    many_to_many :relationships, User,
      join_through: Relationship,
      join_keys: [user_id: :id, relation_id: :id]

    timestamps()
  end

defmodule Livechat.Relationship do

  use Ecto.Schema
  import Ecto.Changeset

  use Ecto.Schema

  schema "relationships" do
    field :user_id, :id
    field :relation_id, :id
    timestamps()
  end

end

Users send each other Message(s)

defmodule Livechat.Chat.Message do
  use Ecto.Schema
  import Ecto.Changeset
  alias Livechat.Accounts.User

  schema "messages" do
    field :from_id, :integer
    field :to_id, :integer
    field :message, :string
    field :status, :string
    
    belongs_to :from, User, define_field: false
    belongs_to :to, User, define_field: false

    field :messages_count, :integer, virtual: true

    timestamps()
  end
end

I try to retrieve a list of relationships of a given user and the count of unread messages from those relationship to this user

I know how to retrieve the user and its relationships with all sent messages, but don’t know how to build a complex query.

this return the relationships with all sent messages, including messages sent to other users lol, This is not the way to do it, of that I’m sure

Repo.get(User, id)
    |> Repo.preload([relationships: :sent_messages])
    |> Map.get(:relationships, [])

Any idea how to approach this?
thnk you.

2

Two big questions:

  • what shape do you want the output to be? I see a messages_count virtual field on Message, but I’m not sure what it’s for.

  • can you produce the desired result in plain SQL? It can be helpful to work in the opposite direction (SQL → Ecto) for complicated queries.

A bonus question: how often will your app be computing this data? If the unread counts are accessed much more often than messages are actually marked read, then consider caching the “unread count” on the relationships schema.

3 Likes
3

Good questions,

1/ I tested making to distinct queries like so:

# this returns the user relationships
relationships = Repo.get(User, user_id)
      |> Repo.preload([:relationships])
      |> Map.get(:relationships, [])

# this returns the count of unread messages for the user (to_id) grouped_by the sender id (from_id)
# thats why you see messages_count virtual field   
unread_messages = (from m in Message,
      where: m.to_id == ^user_id and m.status == "unread",
      group_by: m.from_id,
      select: %{
        from_id: m.from_id, messages_count: count(m.from_id)
      }
    ) |> Repo.all()

As you see these are two queries, all I have to do now is to map relationships and attach the corresponding messages count if present.

2/ If written in SQL it would be two joins to make a single query, I’ve used Laravel’s ORM which let me load the association and a callback to query that association easily and avoid making two queries to the db.

3/ This used by a component in a chat app to display contacts with the number of unread messages, it will be updated on every message received or sent. I’ dont know anything about caching in schemas

4

Ecto.Query is what you’re going to want to use when building complex queries. The following should work for your use case.

import Ecto.Query

# from a %User{}
query =
  from r in Ecto.assoc(user, :relationships),
    left_join: m in Message,
    on: m.from_id == r.id and m.to_id == ^user.id and m.status == "unread",
    group_by: r.id,
    select: %{user: r, message_count: count(m.id)}


# from a user_id
query =
  from u in User,
    inner_join: r in assoc(u, :relationships),
    left_join: m in Message,
    on: m.from_id == r.id and m.to_id == u.id and m.status == "unread",
    where: u.id == ^user_id,
    group_by: r.id,
    select: %{user: r, message_count: count(m.id)}


Repo.all(query)
[
  %{
    message_count: 7,
    user: #Livechat.Accounts.User<
      __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
      id: 62,
      email: "friend1@livechat.test",
      confirmed_at: nil,
      sent_messages: #Ecto.Association.NotLoaded<association :sent_messages is not loaded>,
      received_messages: #Ecto.Association.NotLoaded<association :received_messages is not loaded>,
      relationships: #Ecto.Association.NotLoaded<association :relationships is not loaded>,
      inserted_at: ~N[2023-06-29 14:28:28],
      updated_at: ~N[2023-06-29 14:28:28],
      ...
    >
  },
  #...
]

As a side note: depending on how often the query gets called, it may be better to use a “counter cache” for performance. There are some postgres trigger based solutions I’ve enjoyed using in the past.

2 Likes
5

This is what I’m was looking, I’m still trying to wrap my head about Ecto. The best way to approach any Ecto query is to write it in SQL as al2o3cr pointed out then make it the Ecto way. Thank you all, Answers were useful.