ActiveRecord UNION left out

[lighthouse-import]

Imported from Lighthouse. Original ticket at: http://rails.lighthouseapp.com/projects/8994/tickets/6591
Created by clyfe - 2011-03-17 19:22:47 UTC

ActiveRecord returns Arel::Nodes::Union when I call model.union(active_relation), but it should return an ActiveRelation

1. What steps will reproduce the problem:

   class User << AR::Base; end
   User.where(:a => 1).union(User.where(:a => 2))

2. What is the wrong result:

   The result is an instance of Arel::Nodes::Union

3. What is the result that should happen instead:

   The computation should return an instance of ActiveRelation

THE GOOD

The resulting Arel::Nodes::Union object can be transformed to_sql and then we can use User.find_by_sql(sql)

a = User.where(:a => 1).union(User.where(:a => 2))
sql = a.to_sql # ( SELECT "users".* FROM "users" WHERE "users"."a" = 1 UNION SELECT "users".* FROM "users" WHERE "users"."a" = 2 )"
User.find_by_sql(sql)

THE ISSUE

The issue is obtaining an ActiveRelation object that we can further chain.
Caling a method (select, where, includes) on this (ActiveRelation unioned) object would have the behavior of further calling that method on each of the ActiveRelation objects involved in the UNION

---

All tough in general a UNION query can be avoided, there are some cases where the corect active relation #union functionality is needed.

For example this wold allow fixing issue #213 of CanCan ryanb/cancan#213

I would be glad to work in this issue, with a little help.

[robmathews]

Why'd we close this issue? It's a perfectly reasonable request.

[mrcsparker]

I agree - this is something that would that would be useful.

[ghost]

I have an app that would benefit from this. Please reopen this issue.

[TheMikeSanto]

I would also benefit from union returning a relation object. There's been countless times that I need to combine two queries together but still require a relation object. Please reopen.

[alassek]

I had to resort to string interpolation to solve this. A better option would be much appreciated.

[jpemberthy]

+1 for this one, guess it'd be useful.

[stephenprater]

+1

[aagrawal2001]

+1
definitely need this! This is causing a lot of redundant code for me.

[LTe]

+1

[staszek]

+1

[bblimke]

+1

[paneq]

+1

[arunagw]

cc/ @jonleighton

[mlomnicki]

+1

[jonleighton]

Any of you +1ers are welcome to submit a patch...

[robmathews]

re patch - Well ... I guess I don't know how. If someone gave me a pointer, I could try. As the original poster said, it needs a little help from somebody who understands the ActiveQuery internals.

[rubypanther]

Why write a patch if it is CLOSED? Seems like the first step is to flog whoever closed it, the second step is to re-open it, THEN complain that nobody wrote a patch yet.

[rafaelfranca]

@rubypanther this issue was imported from lighthouse. All the issues imported from lighthouse are closed automatically.

[vsalbaba]

Please reopen so this will get attention it deserves.

[arunagw]

It's open. Do it guys!!

[GarPit]

+1

[chrisfrommann]

This would also be incredibly helpful for me. I'd be happy to eventually write a patch if no one else can, but for now, +1...

[fd]

+1

[groe]

I have encountered that problem at some point in 3 projects over the last years. Would be really useful if there was ActiveRecord support for that. +3

[vkhang55]

+1

[nunziofiore]

+1000

[senny]

Is anyone working on this one? Is there a Pull Request?

[ofavre]

I proposed some code, but it actually depends on another issue: rails/arel#118.
The right way to solve that issue (and handle other set operations) is still not obvious to me, see this comment, help is welcomed.
I'm planning on proposing a proper pull request for the latter within few weeks, when I find time.
I'll then update my code for this issue and pull request it.
That said, someone can still handle these before I do... ;-)

[ejoubaud]

+1

[naveda89]

how is going this issue?? It could be so useful!! :P

[dylanz]

+1

[luke-plewa]

+1

[russ1985]

+100

[omgitsfletch]

+0.9999

[gmccue]

would be nice!

[kuraga]

Any updates? @ofavre , any updates?

[x3qt]

+1

[mjy]

+1

[mvoidex]

+1

[doshea]

+1 for this and for #intersect

[ilyakatz]

+1

[rafaelfranca]

This issue is stale for more than 2 years. Adding +1's will not help and we don't have time to fix all the issues in the world. So I'm closing this one.

Who is having the issue and care about fixing PDI.

[ofavre]

Update:
The pull request rails/arel#118, replaced by rails/arel#239 (by @jsanders), exposing the required functionality in Arel is ready to be merged.
Once done, the code at yakaz/rails@29b8ebd (relation-unions-v3.2.2 branch) can be updated and merged.

[rafaelfranca]

@ofavre thank you for the update. I have assigned the arel PR to me and I'll review it and apply your Rails patch too.

[jsanders]

@rafaelfranca Note that I based the work in rails/arel#239 off of 4-0-stable rather than master. I'm also guessing there is a little work that would need to be done to @ofavre's relation-unions-v3.2.2 branch to make it work against either 4-0-stable or master. Does it make sense to get these changes into 4.0 at this point? If not, I'd be glad to submit PRs against master instead.

[tjgfernandes]

Have applied @ofavre and @jsanders changes rails/issues/939#issuecomment-32709728 against rails 3-2-stable branch and arel 3-0-stable branch.

These changes are being tested and used with cancancan to combine different ability conditions for same resource by using unions (from @clyfe).

So far changes are working ok for tests and my use cases (rails 3.2.17).

[julianbei]

+1

[brianhempel]

I wrote a gem to add usable UNION functionality with ActiveRecord::Relation. We are using it in production. The gem's code may or may not provide a good template for a Rails PR, but it does demonstrate solutions to some of the issues along the way:

• Don't forget about bind values!
• Subselects with ORDER BY need to be wrapped in parentheses … except for SQLite, which just explodes in that case
• The UNION is aliased to the table name to allow further scoping

The important code is here for inspection: https://github.com/brianhempel/active_record_union/blob/master/lib/active_record_union/active_record/relation/union.rb

[guyisra]

+1

The problem with using find_by_sql is that it returns an array. However, uglifying it with string interpolation something like this

Model.from("(#{Model.scope1.to_sql}) union (#{Model.scope2.to_sql})) #{Model.table_name}")

will keep it as a relation for further use

[dmitry]

@guyisra you can use ARel for this kind of task: https://github.com/rails/arel/blob/afd98824eb22b67b1f74edd5ea19e4a19a9da981/test/test_select_manager.rb#L257

Worth to look at: rails/arel#320

[brianhempel]

If you do the union with AREL or SQL string interpolation, watch out for bind values. The SQL for my_user.posts is:

SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" = ?

to_sql won't preserve the value that's supposed to bind to the ?, and I'm pretty sure using AREL won't help you either. So, if you want a useable ActiveRecord::Relation upon which to chain further queries, you may have to do some rebinding.[1]

[1] Like here: https://github.com/brianhempel/active_record_union/blob/master/lib/active_record_union/active_record/relation/union.rb#L25

[asiniy]

+1