FULL OUTER JOIN with Rails 6 ActiveRecord

I am modeling an app with messages and notifications as follows (simplified):

# db/schema.rb
    create_table :messages do |t|
      ...
    end

    create_table :notifications do |t|
      t.references :message, index: true, foreign_key: true, null: false
      t.references :recipient, index: true, foreign_key: { to_table: :users }, null: false
      t.datetime :read_at
      ...
    end

Models:

class User < ApplicationRecord; end
class Message < ApplicationRecord; end
class Notification < ApplicationRecord
  belongs_to :message
  belongs_to :recipient, class_name: 'User'
  accepts_nested_attributes_for :message
end

In this simplified example:

• the messages table holds the actual messages (e.g title, text, images, ...)
• the notifications table keeps track of which user has read which message
• all messages are for all users (e.g. system-wide "announcements"). To be generalized to let messages target specific users.

I am looking for the cleanest (most Rails-like) and most performant way to load a user's notifications and related messages, while ensuring that all messages are loaded even if no notification has yet been created in DB for this user. This is to avoid needing to create as many notification rows in DB as there are users every time a new message is published. A row is added to the notifications table only when the user has read the message (storing the read_at value).

I managed to achieve it using this SQL:

  # user.rb

  def notifications
    unsanitised_sql = <<-SQL
      SELECT 
        :user_id AS recipient_id, m.id as message_id, n.read_at, n.created_at, n.updated_at,
        m.text, ...
      FROM (
        SELECT *
        FROM notifications 
        WHERE recipient_id = :user_id
      ) n
      FULL OUTER JOIN messages m
      ON (message_id = m.id)
    SQL

    ActiveRecord::Base
      .connection
      .select_all(ActiveRecord::Base.sanitize_sql [ unsanitised_sql, { user_id: id } ])
      .map do |row|

      Notification.new(
        recipient_id: row['recipient_id'],
        message_id: row['message_id'],
        read_at: row['read_at'],
        created_at: row['created_at'],
        updated_at: row['updated_at'],
        message_attributes: {
          id: row['message_id'],
          text: row['text'],
          ...
        }
      )
    end
  end

For instance if I have a Message with id=1, two users with id=20 and id=21, and one Notification with (id=30, message_id=1, recipient_id=20), for user 21 and message 1 I get a "virtual" notification with read_at=nil (as the user hasn't read it yet) and associated message data

> User.find(21).notifications

=> [#<Notification:0x00007fb5c64df138 id: nil, message_id: 1, recipient_id: 21, read_at: nil, created_at: nil, updated_at: nil>]

> User.find(21).notifications.first.message

=> #<Message:0x00007fd57c52b5a8 id: 1, text: ...>

However:

1. It is very verbose and the code needs to be updated whenever a new attribute is added to Message or Notification for instance;
2. I am not sure of the performance (I think it is fine as everything is already loaded in a single query, I don't think there is an N+1 problem for instance);
3. Most importantly, I would really prefer to achieve the same using a has_many :notifications association on User, or if not possible, using a custom notifications scope. This is in order to avoid eager-loading, to have a more fluent API, to be able to join with other possible relations, etc. Or at least I would like to improve the syntax somehow to be more Rails-like.

Any ideas?