Skip to content

Instantly share code, notes, and snippets.

@paul paul/conversations_query.rb Secret
Created May 19, 2018

Embed
What would you like to do?
class ConversationsQuery
extend Forwardable
delegate %i[first] => :query
alias_method :conversation, :first
def initialize(finder:, user:)
@user = user
@relation = case finder
when Conversation
Conversation.where(id: finder)
when ActiveRecord::Relation
Conversation.where(id: finder.select(:id))
else
Conversation.where(finder)
end
end
def query
associations = [
:account,
:contact_phone,
{
contact: [
:phone,
{
account: {
organization: [:blocked_phones, :opted_out_phones]
}
}
]
},
{ messages: :user }
]
@relation.from(latest_message_statement).includes(associations).order("latest_deliver_at DESC")
end
def latest_message_statement
<<~SQL
(
SELECT conversations.*,
latest_message.latest_deliver_at AS latest_deliver_at,
earliest_messages.earliest_deliver_at AS earliest_deliver_at,
latest_events.latest_event_name AS status,
user_read_statuses.last_read_at AS read_up_to
FROM conversations
JOIN LATERAL (
SELECT deliver_at AS latest_deliver_at
FROM messages
WHERE conversation_id = conversations.id
ORDER BY deliver_at DESC NULLS LAST
LIMIT 1
) latest_message ON TRUE
JOIN LATERAL (
SELECT deliver_at AS earliest_deliver_at
FROM messages
WHERE conversation_id = conversations.id
ORDER BY deliver_at ASC NULLS LAST
LIMIT 1
) earliest_messages ON TRUE
LEFT JOIN LATERAL (
SELECT event_name AS latest_event_name
FROM conversation_events
WHERE conversation_events.conversation_id = conversations.id
ORDER BY created_at DESC NULLS LAST
LIMIT 1
) latest_events ON TRUE
LEFT JOIN LATERAL (
SELECT last_read_at
FROM read_statuses
WHERE read_statuses.conversation_id = conversations.id
AND read_statuses.user_id = #{@user.id}
ORDER BY id DESC NULLS LAST
LIMIT 1
) user_read_statuses ON TRUE
WHERE latest_deliver_at IS NOT NULL
) AS conversations
SQL
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.