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
|
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 '#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
it 'needs a test' do
pending
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
|
