0

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.

bezzoon
  • 1,755
  • 4
  • 24
  • 52
  • Can you provide your associations as described in your models? – Vapire Jun 25 '14 at 18:40
  • I am using mailboxer @Vapire https://github.com/mailboxer/mailboxer/wiki if this isnt' enough info can you give me more detailed instructions on how to give you the information that you want – bezzoon Jun 25 '14 at 18:45
  • Do you want your desired result to be only ONE result set? – Vapire Jun 25 '14 at 18:55

1 Answers1

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")
Vapire
  • 4,568
  • 3
  • 24
  • 41
  • yes, you are absolutely right! one interesting thing to note is that rails was ordering by id automatically and ruining the query, so the only thing I changed to make it work was added reorder -- so a.mailbox.sentbox.reorder('').to_sql -- also I should add that is is returning an array of hashes, and I don't know how to get the hash to behave properly. I am figuring that our rn. – bezzoon Jun 25 '14 at 21:00