Constructing complicated queries

1

I have a moderately complicated data model in postgresql with lots of relations, and I need to gather them all together for display in a view.

I have a Conversation which associates with a few other objects, which have their own associations, and I need some or all of those loaded depending on the view being rendered. For example, Conversation has many Messages belongs to User, and in a single-conversation view (GET /conversations/1234) I want to include the recent messages and the users that sent them, while in the conversation-list view (GET /conversations) I want only the users and the one most recent message. I’m struggling with 1) how to write relations and repositories that might involve complex queries like LATERAL JOINS, and 2) how do I share those queries with other repositories/relations?

Some specific questions I’ve had that don’t have satisfying answers in the documentation include:

  • Is there a way to reuse or combine the methods on the repository? For example, to get a single conversation with all the associated objects, I have a method #by_slug_with_embeds(slug). To get all the conversations with those associations loaded, I have a separate method #with_embeds. A feature I need to add next is to limit the conversations by account, do I just add it to those methods, eg #for_account_by_slug_with_embeds(account, slug)? If I need to add another, this method is starting to get unwieldy. Do I instead name it for its purpose, like #for_index(account:) and #for_show(account:, slug:)?
  • I have a custom Conversation ROM::Struct model that these get loaded into. A single Conversation has a messages attribute containing all it messages, but to save memory and load, the Conversations within a list should not load those, and instead have a single latest_message attribute. Are these a single Model with two optional attribute, or two separate models that have very similar attributes?

I’ve found some examples to crib from in Hanami [1], but few for ROM itself. I imagine the patterns are similar since Hanami is just ROM underneath, but are the any ROM-specific examples I’m missing?

class ConversationsRelation < ROM::Relation[:sql]
  gateway :default

  schema(:conversations, infer: true) do
    associations do
      belongs_to :contact
      belongs_to :account

      has_many :messages
      has_many :conversation_events
    end
  end

  def with_embeds
    combine(:account, :conversation_events, contact: :phone, messages: :user)
  end

end

class ConversationRepository < ROM::Repository::Root
  root :conversations

  commands :create, update: :by_pk, delete: :by_pk, mapper: :conversation

  def by_slug_with_embeds(slug)
    conversations.with_embeds.by_pk(slug).one!
  end

end

And for comparison, this is a cleaned-up version of an ActiveRecord-based query object I’m trying to replace with ROM functionality: https://gist.github.com/paul/07d62974e4c8e401f00af58a98b8e9fa (The actual one is way gnarlier, and getting worse by the iteration).