-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathwhere_clauses.html
147 lines (128 loc) · 4.53 KB
/
where_clauses.html
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
<!DOCTYPE html>
<html lang="en">
<head>
<title>Menukaarten-docs</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" href="assets/css/stylesheet.css" media="screen,print">
<link rel="stylesheet" href="assets/css/print.css" media="print">
<link rel="stylesheet" type="text/css" href="assets/css/shCore.css" media="screen,print">
<link rel="stylesheet" type="text/css" href="assets/css/shThemeDefault.css" media="screen,print">
<script type="text/javascript" src="assets/js/jquery.min.js"></script>
<script type="text/javascript" src="assets/js/SyntaxHighlighter.js"></script>
<script type="text/javascript" src="assets/js/build_menu.js"></script>
</head>
<body>
<div id="header-wrapper">
<div id="header">
<h1>Documentation SexyFramework</h1>
<span>Created by Vincent Bremer & Douwe de Haan</span>
</div>
</div>
<div id="container">
<div id="menu-wrapper">
<div id="menu">
<h1>Table of contents</h1>
<ul></ul>
</div>
</div>
<div id="content-wrapper">
<div id="content">
<!-- START CONTENT -->
<h1>Where clauses</h1>
<p>For the base model we needed a function to get results from database matched by a where clause. There are many ways to solve this problem and we've did some search which options are available.</p>
<p>
We tried building all different methods, and execute the following 2 queries:
</p>
<span class="bold">Test queries:</span>
<pre class="brush: sql">
-- Test query #1
SELECT * FROM users WHERE hometown = 'Sneek'
-- Test query #2
SELECT * FROM users WHERE name = 'Vincent' AND age = 23
</pre>
<p>
After trying those options we made up a list of advantages and disadvantages of the different options available.
</p>
<h2>CakePHP and Ruby on Rails</h2>
<p>CakePHP and Ruby on Rails use the following format:</p>
<pre class="brush: php">
// Test query #1
$user->where('hometown = ?', 'Sneek')
// Test query #2
$user->where('name = ? AND age = ?', array('Vincent', 20))
</pre>
<span class="bold">Advantages</span>
<ul>
<li>No problems combining AND, OR and LIKE clauses</li>
<li>The values placed on the question mark are easily escaped</li>
</ul>
<span class="bold">Disadvantages</span>
<ul class="list">
<li>You have to manually join the values together with AND or OR clauses</li>
</ul>
<h2>Kohana</h2>
<p>Kohana uses the following format:</p>
<pre class="brush: php">
// Test query #1
$user->where('hometown', '=', 'Sneek')
// Test query #2
$user->where(name, '=', 'Vincent')->where('age', '=', 20)
</pre>
<span class="bold">Advantages</span>
<ul>
<li>Pretty easy to understand</li>
</ul>
<span class="bold">Disadvantages</span>
<ul class="list">
<li>By default all where clauses are joined with AND, if you want to use OR, you have to use created separate functions</li>
</ul>
<h2>Activerecord (CodeIgniter)</h2>
<p>Activerecord uses the following format:</p>
<pre class="brush: php">
// Test query #1
$user->where('hometown =', 'Sneek');
// Test query #2
$user->where('name =', 'Sneek')->where('age =', 23);
</pre>
<span class="bold">Advantages</span>
<ul>
<li>Very clear and easy to understand</li>
<li>Values are easily escaped</li>
</ul>
<span class="bold">Disadvantages</span>
<ul class="list">
<li>All fields are automatically joined by AND if you want to join it with OR, you have to call other functions.</li>
</ul>
<h2>Custom</h2>
<pre class="brush: php">
// Test query #1
$user->where("hometown = 'Sneek")
// Test query #2
$user->where("name = 'Vincent' AND age = 23")
</pre>
<span class="bold">Advantages</span>
<ul>
<li>Pure MySQL code, so every combination of where possible.</li>
</ul>
<span class="bold">Disadvantages</span>
<ul class="list">
<li>Values are not easily escaped and can't be validated.</li>
</ul>
<h2>Our chosen solution</h2>
<p>We've chosen the CakePHP and Ruby on Rails method with some small modifications. We have chosen for this method because it's the most easiest way to program difficult queries, and it's pretty easy to make sure the user input is correctly escaped.</p>
Some more examples:
<pre class="brush: php">
// Search users who's first or lastname contains Tim:
$user->where('firstname LIKE ? OR lastname LIKE ?', '%tim%')->find();
// Get all users older than 10 but not older than 40
$user->where('age > ? AND age < ?', array(10, 40))->find();
// Get all users who's hometown is not Amsterdam
$user->where('hometown != ?', 'Amsterdam')->find();
</pre>
<!-- END CONTENT -->
</div>
</div>
</div>
<script type="text/javascript" src="assets/js/SyntaxHighlighter_settings.js"></script>
</body>
</html>