tricky union query using ruby on rails/active record

Question

I have

a = Profile.last
a.mailbox.inbox
a.mailbox.sentbox
active_conversations = [IDS OF ACTIVE CONVERSATIONS]

a.mailbox.inbox & active_conversations 

returns part of what I need

I want

(a.mailbox.inbox & active_conversations) AND a.mailbox.sentbox

but I need it as SQL, so that I can order it efficiently. I want to order it by ('updated_at')

I have tried joins and other things but they don't work. The classes of (a.mailbox.inboxa and the sentbox are

ActiveRecord::Relation::ActiveRecord_Relation_Conversation

but

(a.mailbox.inbox & active_conversations)

is an array

edit

Something as simple as a.mailbox.inbox JOINS SOMEHOW a.mailbox.sentbox I should be able to work with, but I also can't seem to figure out.

Answer 1

Instead of doing

(a.mailbox.inbox & active_conversations)

you should be able to do

a.mailbox.inbux.where('conversations.id IN (?)', active_conversations)

I believe the Conversation class (and its underlying conversations table) should be right according to the mailboxer code.

However this gives you an ActiveRelation object instead of an array. You can transform this to pure SQL using to_sql. So I think something like this should work:

# get the SQL of both statements
inbox_sql = a.mailbox.inbux.where('conversations.id IN (?)', active_conversations).to_sql
sentbox_sql = a.mailbox.sentbox.to_sql

# use both statements in a UNION SQL statement issued on the Conversation class
Conversation.from("#{inbox_sql} UNION #{sentbox_sql} ORDER BY id AS conversations")