ArgumentError (Relation passed to #or must be structurally compatible. Incompatible values: [:select])

Question

This works...

a = User.select("users.*, 1 as car_id").limit(2)
b = User.select("users.*, 1 as car_id").limit(2)
a.or(b)
>> returns a combined AR Relation

However,

a = User.select("users.*, 1 as car_id").limit(2)
b =  User.select("users.*, 2 as car_id").limit(2)
a.or(b)
>> ArgumentError (Relation passed to #or must be structurally compatible. Incompatible values: [:select])

If I do a+b, it combines, but then converts to an array, and I would like to keep an AR relation to continue to do queries on.

When the select has different values for car_id, it can not union. I would have thought the same column name would allow the union.

I am creating virtual attribute (car_id) on the model, as each set of records needs car_id defined with a different value.

How do I solve the error and union with virtual attributes?

I want an ActiveRecord Relation, and interesting enough:

SELECT users.*, 1 as car_id
FROM users
UNION
SELECT users.*, 2 as car_id
FROM users

works fine when running raw sql.

SQL Example:

 a.mail_for(:inbox).name

=> Mailboxer::Conversation::ActiveRecord_Relation

 a.mail_for(:inbox).to_sql

"SELECT DISTINCT mailboxer_conversations.*, '102' as mailer_id, 'Listing' as mailer_type FROM \"mailboxer_conversations\" INNER JOIN \"mailboxer_notifications\" ON \"mailboxer_notifications\".\"conversation_id\" = \"mailboxer_conversations\".\"id\" AND \"mailboxer_notifications\".\"type\" IN ('Mailboxer::Message') INNER JOIN \"mailboxer_receipts\" ON \"mailboxer_receipts\".\"notification_id\" = \"mailboxer_notifications\".\"id\" WHERE \"mailboxer_notifications\".\"type\" = 'Mailboxer::Message' AND \"mailboxer_receipts\".\"receiver_id\" = 102 AND \"mailboxer_receipts\".\"receiver_type\" = 'Listing' AND \"mailboxer_receipts\".\"mailbox_type\" = 'inbox' AND \"mailboxer_receipts\".\"trashed\" = FALSE AND \"mailboxer_receipts\".\"deleted\" = FALSE ORDER BY \"mailboxer_conversations\".\"updated_at\" DESC" 

So I pass an array of relations to union_scope...

 ar= a.mail_for(:inbox)
 br= b.mail_for(:inbox)
 cr= c.mail_for(:inbox)

combined = union_scope([[a,ar],[b, br],[c, cr])

def union_scope(*relation)
      combined = relation.first[1].none
      relation.each do |relation_set|
        mailer = relation_set[0]
        scope = relation_set[1].select("#{relation_set[1].table_name}.*, \'#{mailer.id}\' as mailer_id, \'#{mailer.class.name}\' as mailer_type")        
        combined = combined.or(scope)
      end
      combined
    end

Update:

def union_scope(*relation)
  combined = relation.first[1].none
  relation.each do |relation_set|
    mailer = relation_set[0]
    scope = relation_set[1].select("#{relation_set[1].table_name}.*, \'#{mailer.id}\' as mailer_id, \'#{mailer.class.name}\' as mailer_type")        
    combined = combined.union(scope)
  end
  conv = ::Mailboxer::Conversation.arel_table
  ::Mailboxer::Conversation.from(conv.create_table_alias(combined, :conversations).to_sql)
end

Resulted in this error:

Mailboxer::Conversation Load (5.8ms)  SELECT "mailboxer_conversations".* FROM ( SELECT DISTINCT "mailboxer_conversations".* FROM "mailboxer_conversations" INNER JOIN "mailboxer_notifications" ON "mailboxer_notifications"."conversation_id" = "mailboxer_conversations"."id" AND "mailboxer_notifications"."type" IN ('Mailboxer::Message') INNER JOIN "mailboxer_receipts" ON "mailboxer_receipts"."notification_id" = "mailboxer_notifications"."id" WHERE "mailboxer_notifications"."type" = $1 AND "mailboxer_receipts"."receiver_id" = $2 AND "mailboxer_receipts"."receiver_type" = $3 AND (1=0) ORDER BY "mailboxer_conversations"."updated_at" DESC UNION SELECT DISTINCT mailboxer_conversations.*, '102' as mailer_id, 'Listing' as mailer_type FROM "mailboxer_conversations" INNER JOIN "mailboxer_notifications" ON "mailboxer_notifications"."conversation_id" = "mailboxer_conversations"."id" AND "mailboxer_notifications"."type" IN ('Mailboxer::Message') INNER JOIN "mailboxer_receipts" ON "mailboxer_receipts"."notification_id" = "mailboxer_notifications"."id" WHERE "mailboxer_notifications"."type" = $4 AND "mailboxer_receipts"."receiver_id" = $5 AND "mailboxer_receipts"."receiver_type" = $6 ORDER BY "mailboxer_conversations"."updated_at" DESC ) "conversations"

ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at or near "UNION")
LINE 1: ...ER BY "mailboxer_conversations"."updated_at" DESC UNION SELE...
                                                             ^
: SELECT "mailboxer_conversations".* FROM ( SELECT DISTINCT "mailboxer_conversations".* FROM "mailboxer_conversations" INNER JOIN "mailboxer_notifications" ON "mailboxer_notifications"."conversation_id" = "mailboxer_conversations"."id" AND "mailboxer_notifications"."type" IN ('Mailboxer::Message') INNER JOIN "mailboxer_receipts" ON "mailboxer_receipts"."notification_id" = "mailboxer_notifications"."id" WHERE "mailboxer_notifications"."type" = $1 AND "mailboxer_receipts"."receiver_id" = $2 AND "mailboxer_receipts"."receiver_type" = $3 AND (1=0) ORDER BY "mailboxer_conversations"."updated_at" DESC UNION SELECT DISTINCT mailboxer_conversations.*, '102' as mailer_id, 'Listing' as mailer_type FROM "mailboxer_conversations" INNER JOIN "mailboxer_notifications" ON "mailboxer_notifications"."conversation_id" = "mailboxer_conversations"."id" AND "mailboxer_notifications"."type" IN ('Mailboxer::Message') INNER JOIN "mailboxer_receipts" ON "mailboxer_receipts"."notification_id" = "mailboxer_notifications"."id" WHERE "mailboxer_notifications"."type" = $4 AND "mailboxer_receipts"."receiver_id" = $5 AND "mailboxer_receipts"."receiver_type" = $6 ORDER BY "mailboxer_conversations"."updated_at" DESC ) "conversations"

Answer 1

While I cannot answer your actual question as to why these are incompatible I can offer a solution akin to your raw SQL example.

You can perform the same operation like so

user_table = User.arel_table
a = user_table.project(Arel.star, Arel.sql("1 as car_id")).take(2)
b = user_table.project(Arel.star, Arel.sql("2 as car_id")).take(2)
union = Arel::Nodes::UnionAll.new(a,b)
User.from(Arel::Nodes::As.new(union,user_table)) 

This will result in the following query.

SELECT 
  users.* 
FROM 
  ( (SELECT  *, 1 as car_id 
     FROM users  
     ORDER BY 
       users.id ASC 
     LIMIT 2) UNION ALL (
     SELECT  *, 2 as car_id 
     FROM users  
     ORDER BY 
       users.id ASC 
     LIMIT 2)) AS users

Since this will still return an ActiveRecord::Relation you can still act on this object as you would any other we have simply substituted the normal data source (the users table) with your custom union data source of the same name.

Update based on extreme revision and some general assumptions as to what you actually meant to do

a= a.mail_for(:inbox)
b= b.mail_for(:inbox)
c= c.mail_for(:inbox)


combined = union_scope(a,b,c)

def union_scope(*relations)
  base = build_scope(*relations.shift)
  combined = relations.reduce(base) do |memo, relation_set|
    Arel::Nodes::UnionAll.new(memo,build_scope(*relation_set))
  end
  union = Arel::Nodes::As.new(combined,::Mailboxer::Conversation.arel_table)
  ::Mailboxer::Conversation.from(union)
end
def build_scope(mailer,relation)
   relation.select(
      "#{relation.table_name}.*, 
      '#{mailer.id}' as mailer_id, 
      '#{mailer.class.name}' as mailer_type"
   ).arel 
end