brianhempel/active_record_union

UNIONs in ActiveRecord! Adds proper union and union_all methods to ActiveRecord::Relation.

active_record_union's Issues

Support for sinatra-activerecord

Is there a way to use this extension also within sinatra-activerecord?

Cannot union relation with includes

I get an error "Cannot union relation with includes" running my app (key gems: activeadmin + active_record_union).
But the same code works without any errors running via console.

App:

#...
def self.all_except_busy_alien(admin_user)
        not_my_free_areas = self.free_areas
                         .joins(:property)
                         .where.not(:properties => {assigned_person_id: admin_user.id})

        all_my_areas = self.joins(:property)
                    .where(:properties => {assigned_person_id: admin_user.id})

        all_my_areas.union(not_my_free_areas)
end
#...

Console:

Loading development environment (Rails 4.2.0)

>> all_my_areas = Area.joins(:property).where(:properties => {assigned_person_id: 6})
... Area Load (174.8ms)  SELECT `areas`.* FROM ...
#<ActiveRecord::Relation


>> all_my_areas.count
115


>> not_my_free_areas = Area.free_areas.joins(:property).where.not(:properties => {assigned_person_id: 6})
... Area Load (0.4ms)  SELECT `areas`.* FROM ...
#<ActiveRecord::Relation


>> not_my_free_areas.count
65


>> r = all_my_areas.union(not_my_free_areas)
... SELECT `areas`.* FROM ((SELECT `areas`.* FROM `areas` INNER JOIN `properties` ON `properties`.`id` = `areas`.`property_id` WHERE `properties`.`assigned_person_id` = 6) UNION (SELECT `areas`.* FROM `areas` INNER JOIN `areas_astatuses` ON `areas_astatuses`.`area_id` = `areas`.`id` INNER JOIN `astatuses` ON `astatuses`.`id` = `areas_astatuses`.`astatus_id` INNER JOIN `properties` ON `properties`.`id` = `areas`.`property_id` WHERE `astatuses`.`tag` = 'free' AND (`properties`.`assigned_person_id` != 6))) `areas`;
#<ActiveRecord::Relation


>> r.count
180

Test suite depends on activerecord version

it is green with activerecord 4.0.13,
1 failure with activerecord 4.0.8,
6 failures with activerecord 4.2.0 (but union apparently works without problems).

Different versions of activerecord build different SQL statement, but the test suite is comparing #to_sql to an expected SQL string.

I have no idea how this could be easily fixed.

There are different results of UNION subqueries depending on mysql version

It's not a gem bug, just a quick important note:

My development server (MySQL Ver 14.14 Distrib 5.6.17). But when I run the same code on production (mysql Ver 15.1 Distrib 5.5.47-MariaDB), I consistently get different results (different records order).

Is flat union chaining possible? Is it preferable to nested?

[user_1.posts, user_2.posts, Post.published].inject(:union)

Nested

SELECT "posts".* FROM (
  SELECT "posts".* FROM (
    SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" = 1
    UNION
    SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" = 2
  ) posts
  UNION
  SELECT "posts".* FROM "posts"  WHERE (published_at < '2014-07-19 16:12:45.882648')
) posts

Flat

SELECT "posts".* FROM (
  SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" = 1
  UNION
  SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" = 2
  UNION
  SELECT "posts".* FROM "posts"  WHERE (published_at < '2014-07-19 16:12:45.882648')
) posts

Flat chaining strikes me as potentially less problematic at least for some engines than deep nesting. Is it possible to achieve that in the current version? Would it make sense to add this?

Something like: Relation.union(user_1.posts, user_2.posts, Post.published)

Problem with (empty result).update_all

Example: Model User:
User.none.union(User.none).update_all(username: 'new username')

Setting username = 'new username' to all users of the database -> When User.none.union(User.none).count is equal to 0!

Rails version:
Rails 5.0.5

Ruby version:
ruby 2.5.3p105

Thanks

Interested in supporting intersect and except?

We have added support for intersect and except locally, was curious if you'd welcome a PR for that, since it expands the scope of the gem beyond union

How can I get the UNION of 2 different tables

Hi,

All the examples I could see are for the same table. I've tried messing around to see if I could get the UNION of multiple tables but failed every time. I'm basically wanting to produce a query that looks something like:

SELECT id, updated_at FROM table_1 UNION SELECT id, updated_at FROM table_2 ORDER BY updated_at;

If I try something like this I only get results from table_1:

t1 = Table1.select(:id, :updated_at).where('updated_at > ?', 2.weeks.ago)
t2 = Table2.select(:id, :updated_at).where('updated_at > ?', 2.weeks.ago)
results = Table1.union(t1).union(t2)

Associated scopes – Major security issue

gem does not work with SQLServer

By default, SQL Server table names are of the form 'dbo.table_name.' When this gem attempts to alias the unionized result-set, it tries to use the original table name, which SQL Server does not like because there is a '.' in it. I am not able to change my table names. Is there a way to supply my own alias? Or at least remove the 'dbo.' from the alias?

nil.union(something_not_nil)

I suppose it's not a bug, but maybe someone has found a workaround.

I have 6 objects initialised with 6 total different queries and I want to chain unions.

obj1.union(obj2).union(obj3) and so on

Each one can be nil, and I want to union all the results. There is no default (otherwise I could use default_obj.union(obj1) and so on).

Any ideas?

Separate the table name from the table subquery

Hello, I noticed that when you use .union, it automatically names the subquery as the table name, however this sometimes is not what you want, specifically when you have an aggregate function like SUM, and you need to refer to the original table that its name has already used by the subquery.

So I wrote a little workaround for this, you can check it here.

I'm still not sure that's the best way to implement it, that's why I didn't ask for a pull request, but either way, I wanted you to consider it.

Feature request: Test on Rails 6.0

Now Rails 6.0 is released, and it would be cool if there was

• a Gemfile for Rails 6
• a Travis configuration to run that
• probably turn the Travis configuration into an explicit matrix, so that we can point at newer Ruby versions for Rails 6

Eager-loading and support for includes, joins and references

Hi @brianhempel @glebm! I am coming from the Readme where you asked folks to make a noise around supporting 'eager-loading'. Is that still on the radar? In my case, almost all scopes have eager-loading and includes, joins and references, and I'd love to clean up my code using this!

FWIW, testing it so far, I keep getting the following error.

ArgumentError: Cannot union relation with includes.
	from /Users/anuj/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/active_record_union-1.2.0/lib/active_record_union/active_record/relation/union.rb:53:in `verify_relations_for_set_operation!'
	from /Users/anuj/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/active_record_union-1.2.0/lib/active_record_union/active_record/relation/union.rb:27:in `set_operation'
	from /Users/anuj/.rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/active_record_union-1.2.0/lib/active_record_union/active_record/relation/union.rb:11:in `union'

Thank you for putting out this library!

Fails on Rails 5.0.0.beta3: undefined method `visitor' for ActiveRecord::Relation

https://travis-ci.org/thredded/thredded/jobs/121828657

 Failure/Error: [search_topics, search_posts].compact.reduce(:union)

 NoMethodError:
   undefined method `visitor' for #<ActiveRecord::Relation []>
 # gems/activerecord-5.0.0.beta3/lib/active_record/relation/delegation.rb:124:in `method_missing'
 # gems/activerecord-5.0.0.beta3/lib/active_record/relation/delegation.rb:94:in `method_missing'
 # gems/active_record_union-1.1.1/lib/active_record_union/active_record/relation/union.rb:31:in `set_operation'
 # gems/active_record_union-1.1.1/lib/active_record_union/active_record/relation/union.rb:11:in `union'
 # ./lib/thredded/topics_search.rb:17:in `each'
 # ./lib/thredded/topics_search.rb:17:in `reduce'
 # ./lib/thredded/topics_search.rb:17:in `search'
 # ./app/models/thredded/topic.rb:12:in `block in <class:Topic>'
 # gems/activerecord-5.0.0.beta3/lib/active_record/scoping/named.rb:159:in `instance_exec'
 # gems/activerecord-5.0.0.beta3/lib/active_record/scoping/named.rb:159:in `block (2 levels) in scope'
 # gems/activerecord-5.0.0.beta3/lib/active_record/relation.rb:351:in `scoping'
 # gems/activerecord-5.0.0.beta3/lib/active_record/scoping/named.rb:159:in `block in scope'
 # ./spec/models/thredded/topic_spec.rb:67:in `block (2 levels) in <module:Thredded>'

PG::SyntaxError: ERROR: syntax error at or near "desc"

I have a model with a column named desc.

It works fine when using pluck

Mission.where(id: 1).union(id: 2).pluck('desc')
# SELECT "missions"."desc" FROM ( (SELECT "missions".* FROM "missions" WHERE "missions"."id" = $1) UNION (SELECT "missions".* FROM "missions" WHERE "missions"."id" = $2) ) "missions"  [["id", 1], ["id", 2]]

But it is broken when using select

Mission.where(id: 1).union(id: 2).select('desc')
# SELECT  desc FROM ( (SELECT "missions".* FROM "missions" WHERE "missions"."id" = $1) UNION (SELECT "missions".* FROM "missions" WHERE "missions"."id" = $2) ) "missions" LIMIT $3  [["id", 1], ["id", 2], ["LIMIT", 11]]

# ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at or near "desc")
# LINE 1: SELECT  desc FROM ( (SELECT "missions".* FROM "missions" WHE...
#                 ^
# : SELECT  desc FROM ( (SELECT "missions".* FROM "missions" WHERE "missions"."id" = $1) # UNION (SELECT "missions".* FROM "missions" WHERE "missions"."id" = $2) ) "missions" # LIMIT $3

It seems that it fails to quote the column desc as "missions"."desc"

Environment

Rails version: 5.2.1
ruby version: 2.5.1
active_record_union: 1.3.0

Fails on Rails 4.1 + PostgreSQL: bind supplies 2 parameters, but prepared statement requires 1

https://travis-ci.org/thredded/thredded/jobs/121800665

ActiveRecord::StatementInvalid:
PG::ProtocolViolation: ERROR: bind message supplies 2 parameters, but prepared statement "a253" requires 1
: SELECT "thredded_topics".* FROM ( (SELECT "thredded_topics".* FROM "thredded_topics" WHERE "thredded_topics"."messageboard_id" = $1 AND (to_tsvector('english', "thredded_topics"."title") @@ plainto_tsquery('english', 'Rando thread'))) UNION (SELECT "thredded_topics".* FROM "thredded_topics" INNER JOIN "thredded_posts" ON "thredded_posts"."postable_id" = "thredded_topics"."id" WHERE "thredded_topics"."messageboard_id" = $1 AND (to_tsvector('english', "thredded_posts"."content") @@ plainto_tsquery('english', 'Rando thread'))) ) "thredded_topics" ORDER BY "thredded_topics"."updated_at" DESC, "thredded_topics"."id" DESC LIMIT 50 OFFSET 0

Only getting this error on the specific combination of PostgreSQL + Rails 4.1. All the other builds are passing.

Feature request: union relations and SQL strings

I recently needed this and thought the project might want something like dis. Idea is for this to work;

User.new(id: 1).posts.union("SELECT * FROM posts WHERE created_at > now()")

I'd be happy to work on it if you think it valuable.

union doesn't work for union of fields

I had expected that the following code:

BsRequest.where(id: BsRequestAction.where(id: [1,2]).select(:bs_request_id).union(Review.where(id: [1,2,3]).select(:bs_request_id)))

to produce the following query:

SELECT  `bs_requests`.* FROM `bs_requests` WHERE `bs_requests`.`id` IN ((SELECT `bs_request_actions`.`bs_request_id` FROM `bs_request_actions` WHERE `bs_request_actions`.`id` IN (1, 2)) UNION (SELECT `reviews`.`bs_request_id` FROM `reviews` WHERE `reviews`.`id` IN (1, 2, 3)))

Instead, it produces:

SELECT  `bs_requests`.* FROM `bs_requests` WHERE `bs_requests`.`id` IN (SELECT `bs_request_actions`.`id` FROM ( (SELECT `bs_request_actions`.`bs_request_id` FROM `bs_request_actions` WHERE `bs_request_actions`.`id` IN (1, 2)) UNION (SELECT `reviews`.`bs_request_id` FROM `reviews` WHERE `reviews`.`id` IN (1, 2, 3)) ) `bs_request_actions`)

Union of multiple columns on 2 tables with no relationship

Does this work for a case when I have to do union of multiple columns on 2 tables that does not have any relationship?

I don't want all columns. I just out few columns.

Not binding polymorphic relation

Check out this code:

filtered_questions.to_sql`

=> "SELECT "questions"."id", "questions"."title", "questions"."meta", "questions"."content", "questions"."updated_at", "questions"."user_id", 'Question' AS "type" FROM "questions" INNER JOIN "taggings" ON "taggings"."tagged_id" = "questions"."id" AND "taggings"."tagged_type" = 'Question' INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE "questions"."deleted_at" IS NULL AND (1=0)"

filtered_findings.to_sql

=> "SELECT "findings"."id", "findings"."title", "findings"."meta", "findings"."content", "findings"."updated_at", "findings"."user_id", 'Finding' AS "type" FROM "findings" INNER JOIN "taggings" ON "taggings"."tagged_id" = "findings"."id" AND "taggings"."tagged_type" = 'Finding' INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE "findings"."deleted_at" IS NULL AND (1=0)"

filtered_questions.union_all(filtered_findings).to_sql

=> "SELECT "questions".* FROM ( (SELECT "questions"."id", "questions"."title", "questions"."meta", "questions"."content", "questions"."updated_at", "questions"."user_id", 'Question' AS "type" FROM "questions" INNER JOIN "taggings" ON "taggings"."tagged_id" = "questions"."id" AND "taggings"."tagged_type" = INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE "questions"."deleted_at" IS NULL AND (1=0)) UNION ALL (SELECT "findings"."id", "findings"."title", "findings"."meta", "findings"."content", "findings"."updated_at", "findings"."user_id", 'Finding' AS "type" FROM "findings" INNER JOIN "taggings" ON "taggings"."tagged_id" = "findings"."id" AND "taggings"."tagged_type" = INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE "findings"."deleted_at" IS NULL AND (1=0)) ) questions"

AND \"taggings\".\"tagged_type\" = INNER JOIN <- error spotted?

This gem somehow misses these two bindings and when I try to execute query I get error that 2 bindings are missing.

`union` ignored if used in a merge

Hi!

There's a very subtle issue I stumbled upon today if a union-ed scope is passed into ActiveRecord::Relation#merge. Any criteria from a union-built scope gets ignored when the relation gets merged into the scope chain.

https://github.com/rails/rails/blob/fe76a95b0d252a2d7c25e69498b720c96b243ea2/activerecord/lib/active_record/relation/spawn_methods.rb#L31-L39

https://github.com/rails/rails/blob/fe76a95b0d252a2d7c25e69498b720c96b243ea2/activerecord/lib/active_record/relation/merger.rb#L25-L46

I think this would probably be extremely hard to fix without monkey-patching pretty sensitive code in Relation::Merger, so the best advice I can see here would be to advise users to call #to_a on a union scope before passing it in to merge so that it continues to affect the query results.

A contrived example:

class Dog < ApplicationRecord
  has_many :bones
end

class Bone < ApplicationRecord
  belongs_to :dog 
end

unioned_scope = Dog.where(id: [1, 2]).union(Dog.where(id: [4, 5]))

# This will return bones for Dog #3, which isn't part of the unioned scope!
Bone.joins(:dog).merge(unioned_scope)