This works locally in SQLite, however when I push to heroku I get this issue.
This issue only occurs when I end the statement in ORDER BY, otherwise it doesn't give me an issue. It's like it breaks conversations into two conversations and then confuses the two, I don't remember this error happening earlier--I could have sworn it was working.
Sorry this is so convoluted...
SELECT distinct conversations.*, receipts.is_read, profiles.nickname
FROM conversations INNER JOIN notifications
ON notifications.conversation_id = conversations.id
INNER JOIN receipts ON receipts.notification_id = notifications.id
INNER JOIN profiles ON profiles.id = notifications.sender_id
WHERE (receipts.mailbox_type = 'inbox' AND ((receipts.receiver_id = #{self.id})))
UNION
SELECT distinct conversations.*, receipts.is_read, profiles.nickname
FROM conversations INNER JOIN notifications
ON notifications.conversation_id = conversations.id
INNER JOIN receipts ON receipts.notification_id = notifications.id
INNER JOIN profiles on profiles.id = notifications.sender_id
WHERE (receipts.mailbox_type = 'sentbox' AND ((receipts.receiver_id = #{self.id})))
ORDER BY conversations.updated_at desc
However Heroku logs show this error.
2014-07-03T09:59:12.485350+00:00 app[web.1]: ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR: missing FROM-clause entry for table "conversations"
2014-07-03T09:59:12.761705+00:00 app[web.1]: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "conversations"
Just did the query without union and it works! It wasn't working earlier because the result set it returned looked different for some reaosn.