这工作...
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
然而,
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])
如果我这样做a+b
,它会合并,然后转换为array
,并且我想保持 AR 关系以继续对其进行查询。
我正在模型上创建虚拟属性 (car_id),因为每组记录都需要使用不同值定义的 car_id。
如何解决错误和与虚拟属性的联合?
我想要一个 ActiveRecord 关系,而且足够有趣:
SELECT users.*, 1 as car_id
FROM users
UNION
SELECT users.*, 2 as car_id
FROM users
运行原始 sql 时工作正常。
SQL 示例:
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"
所以我将一系列关系传递给 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
更新:
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
导致了这个错误:
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"
虽然我无法回答您关于为什么这些不兼容的实际问题,但我可以提供类似于您的原始 SQL 示例的解决方案。
您可以像这样执行相同的操作
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))
这将导致以下查询。
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
由于这仍然会返回一个ActiveRecord::Relation
您仍然可以像对待任何其他对象一样对这个对象进行操作,我们只是用您的同名自定义联合数据源替换了普通数据源(用户表)。
根据极端修订和一些关于您实际打算做什么的一般假设进行更新
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句