I need to write a query, using Arel or ActiveRecord, in Rails 5, using intersections and unions. Every answer on SO that I've found so far about this generally seems to involve transforming the particular example the OP gives into another kind of query. This will not work for me, as these queries are being composed programmatically and by far the most sensible way of doing it is as subqueries which are then joined with INTERSECT and UNION.
Intersecting with & and unioning with | totally works, and does what I want it to, but both of these return arrays and, thus, will bring a huge huge huge dataset into memory for no good reason.
MyModel.where(...).intersect(MyModel.where(...))
This sort of works, but the result is an Arel::Nodes::Intersect rather than an ActiveRecord::Relation, which isn't as useful. I tried building these up using Arel directly but was frustrated by Arel's lack of documentation.
Is there a more Railsy way of accomplishing this that will still be chainable (i.e., can still be lazily paginated like an AR::Relation)?
For INTERSECT, just use:
MyModel.where(...).where(...)
or it can be nicer syntax to put it multi-line like this:
MyModel\
.where(...)
.where(...)
You can also use where(nil), it will have no effect on the relation. This is useful when you're programmatically constructing the where conditions (e.g. where({a: a_val} if a_val.present?).
For UNION, use Rails 5 or:
MyModel.where(...).or(MyModel.where(...))
or with multiline syntax:
MyModel\
.where(...)
.or(
MyModel\
.where(...)
)