1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
|
require File.join(File.dirname(__FILE__), '..', '..', '..', 'spec_helper')
module ActiveRelation
describe Join do
before do
@relation1 = Table.new(:users)
@relation2 = Table.new(:photos)
@predicate = @relation1[:id].equals(@relation2[:user_id])
end
describe '==' do
before do
@another_predicate = @relation1[:id].equals(1)
@another_relation = Table.new(:cameras)
end
it 'obtains if the two relations and the predicate are identical' do
Join.new("INNER JOIN", @relation1, @relation2, @predicate).should == Join.new("INNER JOIN", @relation1, @relation2, @predicate)
Join.new("INNER JOIN", @relation1, @relation2, @predicate).should_not == Join.new("INNER JOIN", @relation1, @another_relation, @predicate)
Join.new("INNER JOIN", @relation1, @relation2, @predicate).should_not == Join.new("INNER JOIN", @relation1, @relation2, @another_predicate)
end
it 'is commutative on the relations' do
Join.new("INNER JOIN", @relation1, @relation2, @predicate).should == Join.new("INNER JOIN", @relation2, @relation1, @predicate)
end
end
describe '#qualify' do
it 'descends' do
Join.new("INNER JOIN", @relation1, @relation2, @predicate).qualify. \
should == Join.new("INNER JOIN", @relation1, @relation2, @predicate).descend(&:qualify)
end
end
describe '#descend' do
it 'distributes over the relations and predicates' do
Join.new("INNER JOIN", @relation1, @relation2, @predicate).qualify. \
should == Join.new("INNER JOIN", @relation1.qualify, @relation2.qualify, @predicate.qualify)
end
end
describe '#prefix_for' do
describe 'when the joined relations are simple' do
it "returns the name of the relation containing the attribute" do
Join.new("INNER JOIN", @relation1, @relation2, @predicate).prefix_for(@relation1[:id]) \
.should == @relation1.prefix_for(@relation1[:id])
Join.new("INNER JOIN", @relation1, @relation2, @predicate).prefix_for(@relation2[:id]) \
.should == @relation2.prefix_for(@relation2[:id])
end
end
describe 'when one of the joined relations is an alias' do
before do
@aliased_relation = @relation1.as(:alias)
end
it "returns the alias of the relation containing the attribute" do
Join.new("INNER JOIN", @aliased_relation, @relation2, @predicate).prefix_for(@aliased_relation[:id]) \
.should == @aliased_relation.alias
Join.new("INNER JOIN", @aliased_relation, @relation2, @predicate).prefix_for(@relation2[:id]) \
.should == @relation2.prefix_for(@relation2[:id])
end
end
end
describe '#engine' do
it "delegates to a relation's engine" do
Join.new("INNER JOIN", @relation1, @relation2, @predicate).engine.should == @relation1.engine
end
end
describe 'with simple relations' do
describe '#attributes' do
it 'combines the attributes of the two relations' do
simple_join = Join.new("INNER JOIN", @relation1, @relation2, @predicate)
simple_join.attributes.should ==
(@relation1.attributes + @relation2.attributes).collect { |a| a.bind(simple_join) }
end
end
describe '#to_sql' do
it 'manufactures sql joining the two tables on the predicate' do
Join.new("INNER JOIN", @relation1, @relation2, @predicate).to_sql.should be_like("
SELECT `users`.`id`, `users`.`name`, `photos`.`id`, `photos`.`user_id`, `photos`.`camera_id`
FROM `users`
INNER JOIN `photos` ON `users`.`id` = `photos`.`user_id`
")
end
it 'manufactures sql joining the two tables, merging any selects' do
Join.new("INNER JOIN", @relation1.select(@relation1[:id].equals(1)),
@relation2.select(@relation2[:id].equals(2)), @predicate).to_sql.should be_like("
SELECT `users`.`id`, `users`.`name`, `photos`.`id`, `photos`.`user_id`, `photos`.`camera_id`
FROM `users`
INNER JOIN `photos` ON `users`.`id` = `photos`.`user_id`
WHERE `users`.`id` = 1
AND `photos`.`id` = 2
")
end
end
end
describe 'with aggregated relations' do
before do
@aggregation = @relation2 \
.aggregate(@relation2[:user_id], @relation2[:id].count) \
.group(@relation2[:user_id]) \
.rename(@relation2[:id].count, :cnt) \
.as('photo_count')
end
describe '#attributes' do
it 'it transforms aggregate expressions into attributes' do
join_with_aggregation = Join.new("INNER JOIN", @relation1, @aggregation, @predicate)
join_with_aggregation.attributes.should ==
(@relation1.attributes + @aggregation.attributes).collect(&:to_attribute).collect { |a| a.bind(join_with_aggregation) }
end
end
describe '#to_sql' do
describe 'with the aggregation on the right' do
it 'manufactures sql joining the left table to a derived table' do
Join.new("INNER JOIN", @relation1, @aggregation, @predicate).to_sql.should be_like("
SELECT `users`.`id`, `users`.`name`, `photo_count`.`user_id`, `photo_count`.`cnt`
FROM `users`
INNER JOIN (SELECT `photos`.`user_id`, COUNT(`photos`.`id`) AS `cnt` FROM `photos` GROUP BY `photos`.`user_id`) AS `photo_count`
ON `users`.`id` = `photo_count`.`user_id`
")
end
end
describe 'with the aggregation on the left' do
it 'manufactures sql joining the right table to a derived table' do
Join.new("INNER JOIN", @aggregation, @relation1, @predicate).to_sql.should be_like("
SELECT `photo_count`.`user_id`, `photo_count`.`cnt`, `users`.`id`, `users`.`name`
FROM (SELECT `photos`.`user_id`, COUNT(`photos`.`id`) AS `cnt` FROM `photos` GROUP BY `photos`.`user_id`) AS `photo_count`
INNER JOIN `users`
ON `users`.`id` = `photo_count`.`user_id`
")
end
end
it "keeps selects on the aggregation within the derived table" do
Join.new("INNER JOIN", @relation1, @aggregation.select(@aggregation[:user_id].equals(1)), @predicate).to_sql.should be_like("
SELECT `users`.`id`, `users`.`name`, `photo_count`.`user_id`, `photo_count`.`cnt`
FROM `users`
INNER JOIN (SELECT `photos`.`user_id`, COUNT(`photos`.`id`) AS `cnt` FROM `photos` WHERE `photos`.`user_id` = 1 GROUP BY `photos`.`user_id`) AS `photo_count`
ON `users`.`id` = `photo_count`.`user_id`
")
end
end
end
end
end
|
