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.