· 8 years ago · Apr 07, 2018, 11:14 AM
1See associated gist for simple definitions of Project, User, Task, Answer models
2referenced herein.
3
4Summary:
5
6 I believe the logic about how JOINs are constructed needs to fundamentally
7 change. In order to create the correct JOIN SQL, the logic needs to be
8 aware of and account for how an individual query param's "links" are related
9 to each other *only*, instead of how they the query param's themselves
10 relate to each other (which is how I intepret how it works now).
11
12 To begin with, think of 'project.user' as really 'task.project.user', and
13 'user.fname' really as 'task.user.fname' -- the Model's own table is the
14 implicit starting point (but not guaranteed to be a chaining point for any
15 given JOIN). This is important in knowing when to add an alias for the
16 originating/Model's table.
17
18 Let's call this "path" the "cumulative scope" (CS), which refers to the full
19 JOIN pathway construction necessary for a given query param and its
20 associated conditions.
21
22 (CE: I'm not entirely sure that the current implementation has access to
23 these concepts at the time join_statement() is called, so implementing
24 this logic might require a greater scope of refactoring.)
25
26 The idea is to iterate over each param, and "walking" each one's CS
27 left-to-right, associating a unique table alias for each "hop", then
28 accumulating any necessary JOIN statements for "hops" that haven't yet been
29 constructed.
30
31Some Axioms:
32
33 (1) if a query param's tailing link results in a new table alias, the
34 associated where condition must also refer to that same alias.
35
36 (2) table aliases should be unique based solely on a given hop's scope/path
37 from the base object.
38
39 (3) tailing relationships should resolve to keys: Task.all('answer.user' =>
40 obj) should resolve to answer.user_id => obj.primary_key (pseudocode).
41 This might already be the case, just haven't tested this out yet.
42
43A Demonstration Example
44
45 #1 Task.all('user.fname' => 'Foo', 'user.lname' => 'Bar', 'project.user.fname' => 'PHB')
46
47 If I were writing this SQL by hand, I'd probably write:
48
49 SELECT * FROM tasks
50 INNER JOIN users users_1 on tasks.task_id = users_1.id
51 INNER JOIN projects projects_1 on tasks.project_id = projects_1.id
52 INNER JOIN users users_2 on projects_1.user_id = users_2.id
53 WHERE users_1.fname = 'Foo' AND
54 users_1.lname = 'Bar' AND
55 users_2.name = 'PHB'
56
57 How to Logically Generate this SQL
58
59 (1) Start with CS['task'] = "tasks", by virtue of the storage_name being
60 referenced already in the query "SELECT * FROM tasks".
61
62 First param: 'user.fname' => 'Foo'
63
64 (2) Again, we really mean "task.user.fname". CS['task'] exists, but
65 not CS['task.user']. So we create the alias for this "hop":
66
67 CS['task.user'] = 'users_1'.
68
69 (3) Since we just created a CS, we don't have a JOIN set up for it.
70 Accumulate a JOIN for that CS:
71
72 English: create a join from CS['task'] to CS['task.user']
73 SQL: INNER JOIN users users_1 ON tasks.user_id = users_1.id
74
75 (4) Accumulate condition for the param:
76
77 English: constrain to any task.user whose fname is 'Foo'
78 SQL: WHERE users_1.fname = 'Foo'
79
80 Second param: 'user.lname' => 'Bar'
81
82 (5) CS path is 'task.user', and we already have CS['task'] and
83 CS['task.user']. The table alias is 'users_1'.
84
85 (6) Since we already had a CS/table alias, we don't need any new joins
86 to service this param.
87
88 (7) Accumulate condition for the param:
89
90 English: constrain to any task.user whose lname is 'Bar'
91 SQL: WHERE users_1.lname = 'Bar'
92
93 Third param: 'project.user.fname' => 'PHB'
94
95 (8) CS path is 'task.project.user'. We already have a CS['task'], but
96 there's no CS['task.project'] or CS['task.project.user'] yet. So:
97
98 CS['task.project'] = 'projects_1'
99 CS['task.project.users'] = 'users_2' # incremented from endpoint tablenames
100
101 (9) Each time we create a new CS, we need to set up a JOIN for it.
102 Accumulate a JOIN for each new CS:
103
104 English: create a join from CS['task'] to CS['task.project']
105 SQL: INNER JOIN projects projects_1 ON tasks.project_id = projects_1.id
106
107 English: create a join from CS['task.project'] to CS['task.project.user']
108 SQL: INNER JOIN users users_2 ON project_1.user_id = users_2.id
109
110 (10) Accumulate condition for the param:
111
112 English: constrain to any task.project.user whose fname is 'PHB'
113 SQL: WHERE users_2.lname = 'PHB'
114
115 Resulting SQL:
116
117 Original SQL: SELECT * FROM tasks
118 Joins: INNER JOIN users users_1 ON tasks.user_id = users_1.id
119 INNER JOIN projects projects_1 ON tasks.project_id = projects_1.id
120 INNER JOIN users users_2 ON project_1.user_id = users_2.id
121 Conditions: WHERE users_1.fname = 'Foo'
122 AND WHERE users_1.lname = 'Bar'
123 AND WHERE users_2.lname = 'PHB'
124
125 Looks just like the original hand-written version.
126
127
128Random Concluding Thoughts
129
130 (1) This algorithm appears capable of supporting convoluted (but not
131 invalid) invocations like
132
133 Task.all('project.tasks.user_id' => [2,1,3])
134
135 This would result in SQL that looks like:
136
137 SELECT * FROM tasks
138 INNER JOIN projects projects_1 ON tasks.project_id = projects_1.id
139 INNER JOIN tasks tasks_1 ON projects_1.id = tasks_1.project_id
140 WHERE tasks_1.user_id IN (2, 1, 3)
141
142 The Tasks table would get aliased once because CS['task'] is not the
143 same as CS['task.project.task'] in scope.
144
145 (2) I'm making an assumption that non-query conditional/join parameters are
146 already handled/accounted for (like :order, :limit, etc) in logic
147 elsewhere. But from a regression vs. feature standpoint, I don't know
148 if:
149
150 :order => ['project.tasks.user_id']
151
152 is supported (yet); if it is now or will be in the future, the handling
153 logic would need to be able to communicate (in this case):
154
155 CS['task.project.task'] # 'tasks_1'
156
157 to the portion of DM's DO Adapter responsible for generating the ORDER
158 statement. Then it would be easy.
159
160
161Other Examples to Ponder
162
163 #2 Task.all('user.fname' => 'task_owner', 'project.user.fname' => 'project_owner')
164
165 English: Give me all tasks belonging to user named 'task_owner', which
166 belong to a project belonging to a user named 'project_owner'.
167
168 SQL: SELECT * FROM tasks
169 INNER JOIN users users_1 ON tasks.task_id = users_1.id
170 INNER JOIN projects projects_1 ON tasks.project_id = projects_1.id
171 INNER JOIN users users_2 ON projects_1.user_id = users_2.id
172 WHERE users_1.fname = 'task_owner'
173 AND users_2.fname = 'project_owner'
174
175 #3 Task.all('user.fname' => 'task_owner', 'project.user.fname' => 'project_owner', 'answers.user.fname' => 'answer_owner')
176
177 English: Give me all tasks belonging to user named 'task_owner', which
178 belong to a project belonging to a user named 'project_owner',
179 and which contain answers that belong to a user named
180 'answer_owner'.
181
182 SQL: SELECT * FROM tasks
183 INNER JOIN users users_1 ON tasks.task_id = users_1.id
184 INNER JOIN projects projects_1 ON tasks.project_id = projects_1.id
185 INNER JOIN users users_2 ON projects_1.user_id = users_2.id
186 INNER JOIN answers answers_1 ON tasks.id = answers_1.task_id
187 INNER JOIN users users_3 ON answers_1.user_id = users_3.id
188 WHERE users_1.fname = 'task_owner'
189 AND users_2.fname = 'project_owner'
190 AND users_3.fname = 'answer_owner'