diff options
| -rw-r--r-- | lib/arel/attributes/attribute.rb | 133 | ||||
| -rw-r--r-- | lib/arel/nodes.rb | 3 | ||||
| -rw-r--r-- | lib/arel/nodes/does_not_match.rb | 6 | ||||
| -rw-r--r-- | lib/arel/nodes/matches.rb | 6 | ||||
| -rw-r--r-- | lib/arel/nodes/not_in.rb | 6 | ||||
| -rw-r--r-- | lib/arel/visitors/to_sql.rb | 14 | ||||
| -rw-r--r-- | spec/attributes/attribute_spec.rb | 434 |
7 files changed, 597 insertions, 5 deletions
diff --git a/lib/arel/attributes/attribute.rb b/lib/arel/attributes/attribute.rb index b5698a3f1d..25ffe717b2 100644 --- a/lib/arel/attributes/attribute.rb +++ b/lib/arel/attributes/attribute.rb @@ -7,16 +7,24 @@ module Arel Nodes::NotEqual.new self, other end + def not_eq_any others + grouping_any :not_eq, others + end + + def not_eq_all others + grouping_all :not_eq, others + end + def eq other Nodes::Equality.new self, other end def eq_any others - first = Nodes::Equality.new self, others.shift + grouping_any :eq, others + end - Nodes::Grouping.new others.inject(first) { |memo,expr| - Nodes::Or.new(memo, Nodes::Equality.new(self, expr)) - } + def eq_all others + grouping_all :eq, others end def in other @@ -36,13 +44,130 @@ module Arel end end + def in_any others + grouping_any :in, others + end + + def in_all others + grouping_all :in, others + end + + def not_in other + case other + when Arel::SelectManager + Nodes::NotIn.new self, other.to_a.map { |x| x.id } + when Range + if other.exclude_end? + left = Nodes::LessThan.new(self, other.min) + right = Nodes::GreaterThanOrEqual.new(self, other.max) + Nodes::Or.new left, right + else + left = Nodes::LessThan.new(self, other.min) + right = Nodes::GreaterThan.new(self, other.max) + Nodes::Or.new left, right + end + else + Nodes::NotIn.new self, other + end + end + + def not_in_any others + grouping_any :not_in, others + end + + def not_in_all others + grouping_all :not_in, others + end + + def matches other + Nodes::Matches.new self, other + end + + def matches_any others + grouping_any :matches, others + end + + def matches_all others + grouping_all :matches, others + end + + def does_not_match other + Nodes::DoesNotMatch.new self, other + end + + def does_not_match_any others + grouping_any :does_not_match, others + end + + def does_not_match_all others + grouping_all :does_not_match, others + end + def gteq right Nodes::GreaterThanOrEqual.new self, right end + def gteq_any others + grouping_any :gteq, others + end + + def gteq_all others + grouping_all :gteq, others + end + def gt right Nodes::GreaterThan.new self, right end + + def gt_any others + grouping_any :gt, others + end + + def gt_all others + grouping_all :gt, others + end + + def lt right + Nodes::LessThan.new self, right + end + + def lt_any others + grouping_any :lt, others + end + + def lt_all others + grouping_all :lt, others + end + + def lteq right + Nodes::LessThanOrEqual.new self, right + end + + def lteq_any others + grouping_any :lteq, others + end + + def lteq_all others + grouping_all :lteq, others + end + + private + + def grouping_any method_id, others + first = send method_id, others.shift + + Nodes::Grouping.new others.inject(first) { |memo,expr| + Nodes::Or.new(memo, send(method_id, expr)) + } + end + + def grouping_all method_id, others + first = send method_id, others.shift + + Nodes::Grouping.new others.inject(first) { |memo,expr| + Nodes::And.new(memo, send(method_id, expr)) + } + end end class String < Attribute; end diff --git a/lib/arel/nodes.rb b/lib/arel/nodes.rb index 6ed05fb31b..0063ed1cd5 100644 --- a/lib/arel/nodes.rb +++ b/lib/arel/nodes.rb @@ -10,8 +10,11 @@ require 'arel/nodes/greater_than' require 'arel/nodes/greater_than_or_equal' require 'arel/nodes/less_than' require 'arel/nodes/less_than_or_equal' +require 'arel/nodes/matches' +require 'arel/nodes/does_not_match' require 'arel/nodes/in' +require 'arel/nodes/not_in' require 'arel/nodes/lock' require 'arel/nodes/function' require 'arel/nodes/count' diff --git a/lib/arel/nodes/does_not_match.rb b/lib/arel/nodes/does_not_match.rb new file mode 100644 index 0000000000..b5693df711 --- /dev/null +++ b/lib/arel/nodes/does_not_match.rb @@ -0,0 +1,6 @@ +module Arel + module Nodes + class DoesNotMatch < Binary + end + end +end diff --git a/lib/arel/nodes/matches.rb b/lib/arel/nodes/matches.rb new file mode 100644 index 0000000000..4b9fd34ad1 --- /dev/null +++ b/lib/arel/nodes/matches.rb @@ -0,0 +1,6 @@ +module Arel + module Nodes + class Matches < Binary + end + end +end diff --git a/lib/arel/nodes/not_in.rb b/lib/arel/nodes/not_in.rb new file mode 100644 index 0000000000..6cb2c6f832 --- /dev/null +++ b/lib/arel/nodes/not_in.rb @@ -0,0 +1,6 @@ +module Arel + module Nodes + class NotIn < Binary + end + end +end diff --git a/lib/arel/visitors/to_sql.rb b/lib/arel/visitors/to_sql.rb index fc821a0ddb..7f9186d28c 100644 --- a/lib/arel/visitors/to_sql.rb +++ b/lib/arel/visitors/to_sql.rb @@ -161,6 +161,14 @@ module Arel "#{visit o.left} < #{visit o.right}" end + def visit_Arel_Nodes_Matches o + "#{visit o.left} LIKE #{visit o.right}" + end + + def visit_Arel_Nodes_DoesNotMatch o + "#{visit o.left} NOT LIKE #{visit o.right}" + end + def visit_Arel_Nodes_StringJoin o "#{visit o.left} #{visit o.right}" end @@ -191,6 +199,12 @@ module Arel "#{visit o.left} IN (#{right})" end + def visit_Arel_Nodes_NotIn o + right = o.right + right = right.empty? ? 'NULL' : right.map { |x| visit x }.join(', ') + "#{visit o.left} NOT IN (#{right})" + end + def visit_Arel_Nodes_And o "#{visit o.left} AND #{visit o.right}" end diff --git a/spec/attributes/attribute_spec.rb b/spec/attributes/attribute_spec.rb index 6420028423..c6d4fcebd4 100644 --- a/spec/attributes/attribute_spec.rb +++ b/spec/attributes/attribute_spec.rb @@ -28,6 +28,38 @@ module Arel end end + describe '#not_eq_any' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:id].not_eq_any([1,2]).should be_kind_of Nodes::Grouping + end + + it 'should generate ORs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].not_eq_any([1,2]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."id" != 1 OR "users"."id" != 2) + } + end + end + + describe '#not_eq_all' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:id].not_eq_all([1,2]).should be_kind_of Nodes::Grouping + end + + it 'should generate ANDs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].not_eq_all([1,2]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."id" != 1 AND "users"."id" != 2) + } + end + end + describe '#gt' do it 'should create a GreaterThan node' do relation = Table.new(:users) @@ -44,6 +76,38 @@ module Arel end end + describe '#gt_any' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:id].gt_any([1,2]).should be_kind_of Nodes::Grouping + end + + it 'should generate ORs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].gt_any([1,2]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."id" > 1 OR "users"."id" > 2) + } + end + end + + describe '#gt_all' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:id].gt_all([1,2]).should be_kind_of Nodes::Grouping + end + + it 'should generate ANDs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].gt_all([1,2]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."id" > 1 AND "users"."id" > 2) + } + end + end + describe '#gteq' do it 'should create a GreaterThanOrEqual node' do relation = Table.new(:users) @@ -60,6 +124,134 @@ module Arel end end + describe '#gteq_any' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:id].gteq_any([1,2]).should be_kind_of Nodes::Grouping + end + + it 'should generate ORs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].gteq_any([1,2]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."id" >= 1 OR "users"."id" >= 2) + } + end + end + + describe '#gteq_all' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:id].gteq_all([1,2]).should be_kind_of Nodes::Grouping + end + + it 'should generate ANDs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].gteq_all([1,2]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."id" >= 1 AND "users"."id" >= 2) + } + end + end + + describe '#lt' do + it 'should create a LessThan node' do + relation = Table.new(:users) + relation[:id].lt(10).should be_kind_of Nodes::LessThan + end + + it 'should generate < in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].lt(10) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE "users"."id" < 10 + } + end + end + + describe '#lt_any' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:id].lt_any([1,2]).should be_kind_of Nodes::Grouping + end + + it 'should generate ORs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].lt_any([1,2]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."id" < 1 OR "users"."id" < 2) + } + end + end + + describe '#lt_all' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:id].lt_all([1,2]).should be_kind_of Nodes::Grouping + end + + it 'should generate ANDs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].lt_all([1,2]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."id" < 1 AND "users"."id" < 2) + } + end + end + + describe '#lteq' do + it 'should create a LessThanOrEqual node' do + relation = Table.new(:users) + relation[:id].lteq(10).should be_kind_of Nodes::LessThanOrEqual + end + + it 'should generate <= in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].lteq(10) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE "users"."id" <= 10 + } + end + end + + describe '#lteq_any' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:id].lteq_any([1,2]).should be_kind_of Nodes::Grouping + end + + it 'should generate ORs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].lteq_any([1,2]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."id" <= 1 OR "users"."id" <= 2) + } + end + end + + describe '#lteq_all' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:id].lteq_all([1,2]).should be_kind_of Nodes::Grouping + end + + it 'should generate ANDs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].lteq_all([1,2]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."id" <= 1 AND "users"."id" <= 2) + } + end + end + describe '#average' do it 'should create a AVG node' do relation = Table.new(:users) @@ -140,6 +332,24 @@ module Arel check equality.right.should == 1 equality.should be_kind_of Nodes::Equality end + + it 'should generate = in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].eq(10) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE "users"."id" = 10 + } + end + + it 'should handle nil' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].eq(nil) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE "users"."id" IS NULL + } + end end describe '#eq_any' do @@ -148,7 +358,7 @@ module Arel relation[:id].eq_any([1,2]).should be_kind_of Nodes::Grouping end - it 'should generate multiple ORs in sql' do + it 'should generate ORs in sql' do relation = Table.new(:users) mgr = relation.project relation[:id] mgr.where relation[:id].eq_any([1,2]) @@ -158,6 +368,118 @@ module Arel end end + describe '#eq_all' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:id].eq_all([1,2]).should be_kind_of Nodes::Grouping + end + + it 'should generate ANDs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].eq_all([1,2]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."id" = 1 AND "users"."id" = 2) + } + end + end + + describe '#matches' do + it 'should create a Matches node' do + relation = Table.new(:users) + relation[:name].matches('%bacon%').should be_kind_of Nodes::Matches + end + + it 'should generate LIKE in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:name].matches('%bacon%') + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE "users"."name" LIKE '%bacon%' + } + end + end + + describe '#matches_any' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:name].matches_any(['%chunky%','%bacon%']).should be_kind_of Nodes::Grouping + end + + it 'should generate ORs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:name].matches_any(['%chunky%','%bacon%']) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."name" LIKE '%chunky%' OR "users"."name" LIKE '%bacon%') + } + end + end + + describe '#matches_all' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:name].matches_all(['%chunky%','%bacon%']).should be_kind_of Nodes::Grouping + end + + it 'should generate ANDs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:name].matches_all(['%chunky%','%bacon%']) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."name" LIKE '%chunky%' AND "users"."name" LIKE '%bacon%') + } + end + end + + describe '#does_not_match' do + it 'should create a DoesNotMatch node' do + relation = Table.new(:users) + relation[:name].does_not_match('%bacon%').should be_kind_of Nodes::DoesNotMatch + end + + it 'should generate NOT LIKE in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:name].does_not_match('%bacon%') + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE "users"."name" NOT LIKE '%bacon%' + } + end + end + + describe '#does_not_match_any' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:name].does_not_match_any(['%chunky%','%bacon%']).should be_kind_of Nodes::Grouping + end + + it 'should generate ORs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:name].does_not_match_any(['%chunky%','%bacon%']) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."name" NOT LIKE '%chunky%' OR "users"."name" NOT LIKE '%bacon%') + } + end + end + + describe '#does_not_match_all' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:name].does_not_match_all(['%chunky%','%bacon%']).should be_kind_of Nodes::Grouping + end + + it 'should generate ANDs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:name].does_not_match_all(['%chunky%','%bacon%']) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."name" NOT LIKE '%chunky%' AND "users"."name" NOT LIKE '%bacon%') + } + end + end + describe '#in' do it 'can be constructed with a list' do end @@ -168,6 +490,116 @@ module Arel check node.left.should == attribute check node.right.should == [1, 2, 3] end + + it 'should generate IN in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].in([1,2,3]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE "users"."id" IN (1, 2, 3) + } + end + end + + describe '#in_any' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:id].in_any([1,2]).should be_kind_of Nodes::Grouping + end + + it 'should generate ORs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].in_any([[1,2], [3,4]]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."id" IN (1, 2) OR "users"."id" IN (3, 4)) + } + end + end + + describe '#in_all' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:id].in_all([1,2]).should be_kind_of Nodes::Grouping + end + + it 'should generate ANDs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].in_all([[1,2], [3,4]]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."id" IN (1, 2) AND "users"."id" IN (3, 4)) + } + end + end + + describe '#not_in' do + it 'can be constructed with a list' do + end + + it 'should return a NotIn node' do + attribute = Attribute.new nil, nil, nil + node = Nodes::NotIn.new attribute, [1,2,3] + check node.left.should == attribute + check node.right.should == [1, 2, 3] + end + + it 'should generate NOT IN in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].not_in([1,2,3]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE "users"."id" NOT IN (1, 2, 3) + } + end + end + + describe '#not_in_any' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:id].not_in_any([1,2]).should be_kind_of Nodes::Grouping + end + + it 'should generate ORs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].not_in_any([[1,2], [3,4]]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."id" NOT IN (1, 2) OR "users"."id" NOT IN (3, 4)) + } + end + end + + describe '#not_in_all' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:id].not_in_all([1,2]).should be_kind_of Nodes::Grouping + end + + it 'should generate ANDs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].not_in_all([[1,2], [3,4]]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."id" NOT IN (1, 2) AND "users"."id" NOT IN (3, 4)) + } + end + end + + describe '#eq_all' do + it 'should create a Grouping node' do + relation = Table.new(:users) + relation[:id].eq_all([1,2]).should be_kind_of Nodes::Grouping + end + + it 'should generate ANDs in sql' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + mgr.where relation[:id].eq_all([1,2]) + mgr.to_sql.should be_like %{ + SELECT "users"."id" FROM "users" WHERE ("users"."id" = 1 AND "users"."id" = 2) + } + end end end |