· 7 years ago · Oct 29, 2018, 02:44 AM
1require 'active_record'
2require 'logger'
3require 'arel'
4
5ActiveRecord::Base.establish_connection adapter: 'postgresql', database: 'josh_testing'
6ActiveRecord::Base.logger = Logger.new $stdout
7ActiveSupport::LogSubscriber.colorize_logging = false
8
9ActiveRecord::Schema.define do
10 self.verbose = false
11 drop_table :users, if_exists: true
12 create_table :users do |t|
13 t.string :name
14 t.integer :user_types, array: true, default: []
15 end
16end
17
18class User < ActiveRecord::Base
19end
20
21class Arel::Nodes::Any < Arel::Nodes::Binary
22 def operator; :"= any" end
23 alias :operand1 :left
24 alias :operand2 :right
25end
26
27class Arel::Visitors::ToSql
28 def visit_Arel_Nodes_Any(node, collector)
29 collector = visit node.left, collector
30 collector << " = ANY ("
31 collector = visit node.right, collector
32 collector << ")"
33 collector
34 end
35end
36
37module ActiveRecord::Querying
38 delegate :any, to: :all
39end
40
41module ActiveRecord::QueryMethods
42 # probably not actually general enough to be correct, but it works for my use case below
43 def any(opts)
44 opts.reduce self do |query, (column, value)|
45 query.where Arel::Nodes::Any.new(
46 Arel::Nodes::BindParam.new(value),
47 User.arel_table[column.to_s.pluralize.intern]
48 )
49 end
50 end
51end
52
53User.create! [
54 {name: "Josh", user_types: [1, 2]},
55 {name: "Sally", user_types: [3]},
56 {name: "Sam", user_types: [2, 3]},
57 {name: "Carla", user_types: [1, 3]},
58]
59
60User.any(user_type: 1).to_sql
61# => "SELECT \"users\".* FROM \"users\" WHERE true; 2 = ANY (\"users\".\"user_types\")"
62
63User.any(user_type: 1).any(user_type: 2).to_sql
64# => "SELECT \"users\".* FROM \"users\" WHERE 1 = ANY (\"users\".\"user_types\") AND 2 = ANY (\"users\".\"user_types\")"
65
66
67User.any user_type: 1
68# => [#<User:0x00007f9934c53d00 id: 1, name: "Josh", user_types: [1, 2]>,
69# #<User:0x00007f9934c53af8 id: 4, name: "Carla", user_types: [1, 3]>]
70
71User.any user_type: 2
72# => [#<User:0x00007f9934b4f418 id: 1, name: "Josh", user_types: [1, 2]>,
73# #<User:0x00007f9934b4efb8 id: 3, name: "Sam", user_types: [2, 3]>]
74
75User.any user_type: 3
76# => [#<User:0x00007f993628d080 id: 2, name: "Sally", user_types: [3]>,
77# #<User:0x00007f993628cec8 id: 3, name: "Sam", user_types: [2, 3]>,
78# #<User:0x00007f993628cd10 id: 4, name: "Carla", user_types: [1, 3]>]
79
80User.any(user_type: 1).any(user_type: 2)
81# => [#<User:0x00007f9936294650 id: 1, name: "Josh", user_types: [1, 2]>]
82
83# >> D, [2018-10-28T20:02:45.223943 #45384] DEBUG -- : (10.8ms) DROP TABLE IF EXISTS "users"
84# >> D, [2018-10-28T20:02:45.239001 #45384] DEBUG -- : (13.0ms) CREATE TABLE "users" ("id" bigserial primary key, "name" character varying, "user_types" integer[] DEFAULT '{}')
85# >> D, [2018-10-28T20:02:45.368282 #45384] DEBUG -- : ActiveRecord::InternalMetadata Load (0.5ms) SELECT "ar_internal_metadata".* FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = $1 LIMIT $2 [["key", "environment"], ["LIMIT", 1]]
86# >> D, [2018-10-28T20:02:45.377440 #45384] DEBUG -- : (0.2ms) BEGIN
87# >> D, [2018-10-28T20:02:45.378455 #45384] DEBUG -- : (0.2ms) COMMIT
88# >> D, [2018-10-28T20:02:45.386792 #45384] DEBUG -- : (0.2ms) BEGIN
89# >> D, [2018-10-28T20:02:45.388210 #45384] DEBUG -- : User Create (0.7ms) INSERT INTO "users" ("name", "user_types") VALUES ($1, $2) RETURNING "id" [["name", "Josh"], ["user_types", "{1,2}"]]
90# >> D, [2018-10-28T20:02:45.394842 #45384] DEBUG -- : (6.3ms) COMMIT
91# >> D, [2018-10-28T20:02:45.395553 #45384] DEBUG -- : (0.3ms) BEGIN
92# >> D, [2018-10-28T20:02:45.396586 #45384] DEBUG -- : User Create (0.4ms) INSERT INTO "users" ("name", "user_types") VALUES ($1, $2) RETURNING "id" [["name", "Sally"], ["user_types", "{3}"]]
93# >> D, [2018-10-28T20:02:45.401967 #45384] DEBUG -- : (5.1ms) COMMIT
94# >> D, [2018-10-28T20:02:45.402558 #45384] DEBUG -- : (0.2ms) BEGIN
95# >> D, [2018-10-28T20:02:45.403647 #45384] DEBUG -- : User Create (0.4ms) INSERT INTO "users" ("name", "user_types") VALUES ($1, $2) RETURNING "id" [["name", "Sam"], ["user_types", "{2,3}"]]
96# >> D, [2018-10-28T20:02:45.405176 #45384] DEBUG -- : (1.2ms) COMMIT
97# >> D, [2018-10-28T20:02:45.405859 #45384] DEBUG -- : (0.2ms) BEGIN
98# >> D, [2018-10-28T20:02:45.407027 #45384] DEBUG -- : User Create (0.4ms) INSERT INTO "users" ("name", "user_types") VALUES ($1, $2) RETURNING "id" [["name", "Carla"], ["user_types", "{1,3}"]]
99# >> D, [2018-10-28T20:02:45.408803 #45384] DEBUG -- : (1.4ms) COMMIT
100# >> D, [2018-10-28T20:02:45.410854 #45384] DEBUG -- : User Load (0.5ms) SELECT "users".* FROM "users" WHERE 1 = ANY ("users"."user_types")
101# >> D, [2018-10-28T20:02:45.412117 #45384] DEBUG -- : User Load (0.3ms) SELECT "users".* FROM "users" WHERE 2 = ANY ("users"."user_types")
102# >> D, [2018-10-28T20:02:45.413296 #45384] DEBUG -- : User Load (0.3ms) SELECT "users".* FROM "users" WHERE 3 = ANY ("users"."user_types")
103# >> D, [2018-10-28T20:02:45.414677 #45384] DEBUG -- : User Load (0.4ms) SELECT "users".* FROM "users" WHERE 1 = ANY ("users"."user_types") AND 2 = ANY ("users"."user_types")