Incrementally Building the v1/events_controller#index Query

combining-ar-scopes-with-or.md

Combining ActiveRecord Scopes with OR [<v5]

This came about while trying to construct an ActiveRecord query.

via http://stackoverflow.com/a/9540911/111363

Note that merge combines WHERE clauses using AND. While ActiveRecord does not currently have the ability to combine relations using OR instead, it is coming in ActiveRecord 5 (https://github.com/rails/rails/commit/9e42cf019f2417473e7dcbfcb885709fa2709f89).

It'll look something like this:

via http://tombroomfield.com/the-upcoming-active-record-or-method-will-clean-up-your-queries/

User.where(country: 'Australia').or(User.where(first_name: 'Tom')).where(middle_name: 'Philip')

Without Reassignment

[7] 2.1.5@4.0.13 (main)> Event.upcoming_today.count
D, [2015-07-14T23:17:07.179339 #20195] DEBUG -- :    (0.7ms)  SELECT COUNT(*) FROM "events" WHERE ("events"."start_date" BETWEEN '2015-07-15 00:00:00.000000' AND '2015-07-16 00:00:00.000000') AND "events"."status" = 'upcoming'
40

[8] 2.1.5@4.0.13 (main)> events = Event.published.order("published_on DESC").with_pagination(1, 50);

[9] 2.1.5@4.0.13 (main)> events.merge(Event.upcoming_today);

[10] 2.1.5@4.0.13 (main)> events.to_sql
"SELECT  \"events\".* FROM \"events\"  WHERE (published_on IS NOT NULL)  ORDER BY published_on DESC LIMIT 50 OFFSET 0"

[11] 2.1.5@4.0.13 (main)> events.count
D, [2015-07-14T23:18:58.359863 #20195] DEBUG -- :    (0.7ms)  SELECT COUNT(count_column) FROM (SELECT 1 AS count_column FROM "events" WHERE (published_on IS NOT NULL) LIMIT 50 OFFSET 0) subquery_for_count
50

With Reassignment

[12] 2.1.5@4.0.13 (main)> Event.upcoming_today.count
D, [2015-07-14T23:19:27.238969 #20195] DEBUG -- :    (0.7ms)  SELECT COUNT(*) FROM "events" WHERE ("events"."start_date" BETWEEN '2015-07-15 00:00:00.000000' AND '2015-07-16 00:00:00.000000') AND "events"."status" = 'upcoming'
40

[13] 2.1.5@4.0.13 (main)> events = Event.published.order("published_on DESC").with_pagination(1, 50);

[14] 2.1.5@4.0.13 (main)> events = events.merge(Event.upcoming_today);

[15] 2.1.5@4.0.13 (main)> events.to_sql
"SELECT  \"events\".* FROM \"events\"  WHERE (published_on IS NOT NULL) AND (\"events\".\"start_date\" BETWEEN '2015-07-15 00:00:00.000000' AND '2015-07-16 00:00:00.000000') AND \"events\".\"status\" = 'upcoming'  ORDER BY published_on DESC, start_date ASC LIMIT 50 OFFSET 0"

[16] 2.1.5@4.0.13 (main)> events.count
D, [2015-07-14T23:19:52.733494 #20195] DEBUG -- :    (1.1ms)  SELECT COUNT(count_column) FROM (SELECT 1 AS count_column FROM "events" WHERE (published_on IS NOT NULL) AND ("events"."start_date" BETWEEN '2015-07-15 00:00:00.000000' AND '2015-07-16 00:00:00.000000') AND "events"."status" = 'upcoming' LIMIT 50 OFFSET 0) subquery_for_count
40

With ActiveRecordUnion

[17] 2.1.5@4.0.13 (main)> Event.upcoming_today.count
D, [2015-07-14T23:21:00.135734 #20195] DEBUG -- :    (0.7ms)  SELECT COUNT(*) FROM "events" WHERE ("events"."start_date" BETWEEN '2015-07-15 00:00:00.000000' AND '2015-07-16 00:00:00.000000') AND "events"."status" = 'upcoming'
40

[18] 2.1.5@4.0.13 (main)> events = Event.published.order("published_on DESC").with_pagination(1, 50);

[19] 2.1.5@4.0.13 (main)> events = events.union(Event.upcoming_today);

[20] 2.1.5@4.0.13 (main)> events.to_sql
"SELECT \"events\".* FROM ( (SELECT  \"events\".* FROM \"events\"  WHERE (published_on IS NOT NULL)  ORDER BY published_on DESC LIMIT 50 OFFSET 0) UNION (SELECT \"events\".* FROM \"events\"  WHERE (\"events\".\"start_date\" BETWEEN '2015-07-15 00:00:00.000000' AND '2015-07-16 00:00:00.000000') AND \"events\".\"status\" = 'upcoming'  ORDER BY start_date ASC) ) events"

[21] 2.1.5@4.0.13 (main)> events.count
D, [2015-07-14T23:21:32.549578 #20195] DEBUG -- :    (11.0ms)  SELECT COUNT(*) FROM ( (SELECT "events".* FROM "events" WHERE (published_on IS NOT NULL) ORDER BY published_on DESC LIMIT 50 OFFSET 0) UNION (SELECT "events".* FROM "events" WHERE ("events"."start_date" BETWEEN '2015-07-15 00:00:00.000000' AND '2015-07-16 00:00:00.000000') AND "events"."status" = 'upcoming' ORDER BY start_date ASC) ) events
79