Finders

Version 10 (Jacques Fuentes, 2010-08-31 07:23 PM)

1 3 Kien La
h2. Finders
2 3 Kien La

                
3 2 Kien La
*(#topic-list) "Single record result":/projects/main/wiki/Finders#single-record-result
4 1
* "Multiple records result":/projects/main/wiki/Finders#multiple-records-result
5 4 Kien La
* "Finder options":/projects/main/wiki/Finders#finder-options
6 4 Kien La
* "Conditions":/projects/main/wiki/Finders#conditions
7 4 Kien La
* "Limit & Offset":/projects/main/wiki/Finders#limit-offset
8 4 Kien La
* "Order":/projects/main/wiki/Finders#order
9 4 Kien La
* "Select":/projects/main/wiki/Finders#select
10 4 Kien La
* "From":/projects/main/wiki/Finders#from
11 5 Kien La
* "Group":/projects/main/wiki/Finders#group
12 5 Kien La
* "Having":/projects/main/wiki/Finders#having
13 4 Kien La
* "Read only":/projects/main/wiki/Finders#read-only
14 4 Kien La
* "Dynamic finders":/projects/main/wiki/Finders#dynamic-finders
15 4 Kien La
* "Joins":/projects/main/wiki/Finders#joins
16 4 Kien La
* "Find by custom SQL":/projects/main/wiki/Finders#find-by-custom-sql
17 10 Jacques Fuentes
* "Eager loading associations":/projections/main/wiki/Finders#eager-loading
18 1

                
19 1
ActiveRecord supports a number of methods by which you can find records such as: via primary key, dynamic field name finders. It has the ability to fetch all the records in a table with a simple call, or you can make use of options like order, limit, select, and group.
20 1

                
21 6 Kien La
There are essentially two groups of finders you will be working with: "a single record result":/projects/main/wiki/Finders#single-record-result and "multiple records result":/projects/main/wiki/Finders#multiple-records-result. Sometimes there will be little transparency for the method calls, meaning you may use the same method to get either one, but you will pass an option to that method to signify which type of result you will fetch.
22 1

                
23 7 Kien La
All methods used to fetch records from your database will go through *Model::find()*, with one exception, custom sql can be passed to "Model::find_by_sql()":/projects/main/wiki/Finders#find-by-custom-sql. In all cases, the finder methods in ActiveRecord are statically invoked. This means you will always use the following type of syntax.
24 1

                
25 1
<pre class="code"><code class="php">
26 1
class Book extends ActiveRecord\Model {}
27 1
 
28 1
Book::find('all');
29 1
Book::find('last');
30 1
Book::first();
31 1
Book::last();
32 1
Book::all();
33 1
</code></pre>
34 1
 
35 2 Kien La
h4(#single-record-result). Single record result
36 1

                
37 1
Whenever you invoke a method which produces a single result, that method will return an instance of your model class. There are 3 different ways to fetch a single record result. We'll start with one of the most basic forms.
38 1

                
39 1
h4. Find by primary key
40 1

                
41 6 Kien La
You can grab a record by passing a primary key to the find method. You may pass an "options array":/projects/main/wiki/Finders#finder-options as the second argument for creating specific queries. If no record is found, a RecordNotFound exception will be thrown.
42 1

                
43 1
<pre class="code"><code class="php">
44 1
# Grab the book with the primary key of 2
45 1
Book::find(2);
46 1
# sql => SELECT * FROM `books` WHERE id = 2
47 1
</code></pre>
48 1
 
49 1
h4. Find first
50 1

                
51 1
You can get the first record back from your database two ways. If you do not pass conditions as the second argument, then this method will fetch all the results from your database, but will only return the very first result back to you. Null will be returned if no records are found.
52 1

                
53 1
<pre class="code"><code class="php">
54 1
# Grab all books, but only return the first result back as your model object.
55 1
$book = Book::first();
56 1
echo "the first id is: {$book->id}" # => the first id is: 1
57 1
# sql => SELECT * FROM `books`
58 1

                
59 1
# this produces the same sql/result as above
60 1
Book::find('first');
61 1
</code></pre>
62 1
 
63 1
h4. Find last
64 1

                
65 1
If you haven't yet fallen asleep reading this guide, you should've guessed this is the same as "find first", except that it will return the last result. Null will be returned if no records are found.
66 1

                
67 1
<pre class="code"><code class="php">
68 1
# Grab all books, but only return the last result back as your model object.
69 1
$book = Book::last();
70 1
echo "the last id is: {$book->id}" # => the last id is: 32
71 1
# sql => SELECT * FROM `books`
72 1

                
73 1
# this produces the same sql/result as above
74 1
Book::find('last');
75 1
</code></pre>
76 1
 
77 2 Kien La
h4(#multiple-records-result). Multiple records result
78 1

                
79 1
This type of result will always return an array of model objects. If your table holds no records, or your query yields no results, then an empty array will be given.
80 1

                
81 1
h4. Find by primary key
82 1

                
83 6 Kien La
Just like the single record result for find by primary key, you can pass an array to the find method for multiple primary keys. Again, you may pass an "options array":/projects/main/wiki/Finders#finder-options as the last argument for creating specific queries. Every key which you use as an argument must produce a corresponding record, otherwise, a RecordNotFound exception will be thrown.
84 1

                
85 1
<pre class="code"><code class="php">
86 1
# Grab books with the primary key of 2 or 3
87 1
Book::find(2,3);
88 1
# sql => SELECT * FROM `books` WHERE id IN (2,3)
89 1

                
90 1
# same as above
91 1
Book::find(array(2,3));
92 1
</code></pre>
93 1
 
94 1
h4. Find all
95 1

                
96 6 Kien La
There are 2 more ways which you can use to get multiple records back from your database. They use different methods; however, they are basically the same thing. If you do not pass an "options array":/projects/main/wiki/Finders#finder-options, then it will fetch all records.
97 1

                
98 1
<pre class="code"><code class="php">
99 1
# Grab all books from the database
100 1
Book::all();
101 1
# sql => SELECT * FROM `books`
102 1

                
103 1
# same as above
104 1
Book::find('all');
105 1
</code></pre>
106 1
 
107 1
Here we pass some options to the same method so that we don't fetch *every* record.
108 1

                
109 1
<pre class="code"><code class="php">
110 1
$options = array('limit' => 2);
111 1
Book::all($options);
112 1
# sql => SELECT * FROM `books` LIMIT 0,2
113 1

                
114 1
# same as above
115 1
Book::find('all', $options);
116 1
</code></pre>
117 1
 
118 4 Kien La
h4(#finder-options). Finder options
119 1

                
120 1
There are a number of options available to pass to one of the finder methods for granularity. Let's start with one of the most important options: conditions.
121 1

                
122 4 Kien La
h4(#conditions). Conditions
123 1

                
124 1
This is the "WHERE" of a SQL statement. By creating conditions, ActiveRecord will parse them into a corresponding "WHERE" SQL statement to filter out your results. Conditions can be extremely simple by only supplying a string. They can also be as complex as you'd like by creating a conditions string that uses ? marks as placeholders for values. Let's start with a simple example of a conditions string.
125 1

                
126 1
<pre class="code"><code class="php">
127 1
# fetch all the cheap books!
128 1
Book::all(array('conditions' => 'price < 15.00'));
129 1
# sql => SELECT * FROM `books` WHERE price < 15.00
130 1

                
131 1
# fetch all books that have "war" somewhere in the title
132 1
Book::find(array('conditions' => "title LIKE '%war%'"));
133 1
# sql => SELECT * FROM `books` WHERE title LIKE '%war%'
134 1
</code></pre>
135 1
 
136 1
As stated, you can use *?* marks as placeholders for values which ActiveRecord will replace with your supplied values. The benefit of using this process is that ActiveRecord will escape your string in the backend with your database's native function to prevent SQL injection.
137 1

                
138 1
<pre class="code"><code class="php">
139 1
# fetch all the cheap books!
140 1
Book::all(array('conditions' => array('price < ?', 15.00)));
141 1
# sql => SELECT * FROM `books` WHERE price < 15.00
142 1

                
143 1
# fetch all lousy romance novels
144 1
Book::find('all', array('conditions' => array('genre = ?', 'Romance')));
145 1
# sql => SELECT * FROM `books` WHERE genre = 'Romance'
146 1

                
147 1
# fetch all books with these authors
148 1
Book::find('all', array('conditions' => array('author_id in (?)', array(1,2,3))));
149 1
# sql => SELECT * FROM `books` WHERE author_id in (1,2,3)
150 1

                
151 1
# fetch all lousy romance novels which are cheap
152 1
Book::all(array('conditions' => array('genre = ? AND price < ?', 'Romance', 15.00)));
153 1
# sql => SELECT * FROM `books` WHERE genre = 'Romance' AND price < 15.00
154 1
</code></pre>
155 1
 
156 1
Here is a more complicated example. Again, the first index of the conditions array are the condition strings. The values in the array after that are the values which replace their corresponding ? marks.
157 1

                
158 1
<pre class="code"><code class="php">
159 1
# fetch all cheap books by these authors
160 1
$cond =array('conditions'=>array('author_id in(?) AND price < ?', array(1,2,3), 15.00));
161 1
Book::all($cond);
162 1
# sql => SELECT * FROM `books` WHERE author_id in(1,2,3) AND price < 15.00
163 1
</code></pre>
164 1
 
165 4 Kien La
h4(#limit-offset). Limit & Offset
166 1

                
167 1
This one should be fairly obvious. A limit option will produce a SQL limit clause for supported databases. It can be used in conjunction with the *offset* option.
168 1

                
169 1
<pre class="code"><code class="php">
170 1
# fetch all but limit to 10 total books
171 1
Book::find('all', array('limit' => 10));
172 1
# sql => SELECT * FROM `books` LIMIT 0,10
173 1

                
174 1
# fetch all but limit to 10 total books starting at the 6th book
175 1
Book::find('all', array('limit' => 10, 'offset' => 5));
176 1
# sql => SELECT * FROM `books` LIMIT 5,10
177 1
</code></pre>
178 1
 
179 4 Kien La
h4(#order). Order
180 1

                
181 1
Produces an ORDERY BY SQL clause.
182 1

                
183 1
<pre class="code"><code class="php">
184 1
# order all books by title desc
185 1
Book::find('all', array('order' => 'title desc'));
186 1
# sql => SELECT * FROM `books` ORDER BY title desc
187 1

                
188 1
# order by most expensive and title
189 1
Book::find('all', array('order' => 'price desc, title asc'));
190 1
# sql => SELECT * FROM `books` ORDER BY price desc, title asc
191 1
</code></pre>
192 1
 
193 4 Kien La
h4(#select). Select
194 1

                
195 6 Kien La
Passing a select key in your "options array":/projects/main/wiki/Finders#finder-options will allow you to specify which columns you want back from the database. This is helpful when you have a table with too many columns, but you might only want 3 columns back for 50 records. It is also helpful when used with a group statement.
196 1

                
197 1
<pre class="code"><code class="php">
198 1
# fetch all books, but only the id and title columns
199 1
Book::find('all', array('select' => 'id, title'));
200 1
# sql => SELECT id, title FROM `books`
201 1

                
202 1
# custom sql to feed some report
203 1
Book::find('all', array('select' => 'avg(price) as avg_price, avg(tax) as avg_tax'));
204 1
# sql => SELECT avg(price) as avg_price, avg(tax) as avg_tax FROM `books` LIMIT 5,10
205 1
</code></pre>
206 1
 
207 4 Kien La
h4(#from). From
208 1

                
209 6 Kien La
This designates the table you are selecting from. This can come in handy if you do a "join":/projects/main/wiki/Finders#joins or require finer control.
210 1

                
211 1
<pre class="code"><code class="php">
212 1
# fetch the first book by aliasing the table name
213 1
Book::first(array('select'=> 'b.*', 'from' => 'books as b'));
214 1
# sql => SELECT b.* FROM books as b LIMIT 0,1
215 1
</code></pre>
216 1
 
217 4 Kien La
h4(#group). Group
218 1

                
219 1
Generate a GROUP BY clause.
220 1

                
221 1
<pre class="code"><code class="php">
222 1
# group all books by prices
223 1
Book::all(array('group' => 'price'));
224 1
# sql => SELECT * FROM `books` GROUP BY price
225 1
</code></pre>
226 1
 
227 4 Kien La
h4(#having). Having
228 1

                
229 1
Generate a HAVING clause to add conditions to your GROUP BY.
230 1

                
231 1
<pre class="code"><code class="php">
232 1
# group all books by prices greater than $45
233 1
Book::all(array('group' => 'price', 'having' => 'price > 45.00'));
234 1
# sql => SELECT * FROM `books` GROUP BY price HAVING price > 45.00
235 1
</code></pre>
236 1
 
237 4 Kien La
h4(#read-only). Read only
238 1

                
239 1
Readonly models are just that: readonly. If you try to save a readonly model, then a ReadOnlyException will be thrown.
240 1

                
241 1
<pre class="code"><code class="php">
242 1
# specify the object is readonly and cannot be saved
243 1
$book = Book::first(array('readonly' => true));
244 1
 
245 1
try {
246 1
  $book->save();
247 1
} catch (ActiveRecord\ReadOnlyException $e) {
248 1
  echo $e->getMessage();
249 1
  # => Book::save() cannot be invoked because this model is set to read only
250 1
}
251 1
</code></pre>
252 1
 
253 4 Kien La
h4(#dynamic-finders). Dynamic finders
254 1

                
255 8 Kien La
These offer a quick and easy way to construct conditions without having to pass in a bloated array option. This option makes use of PHP 5.3's "late static binding":http://www.php.net/lsb combined with "__callStatic()":http://www.php.net/__callstatic allowing you to invoke undefined static methods on your model. You can either use YourModel::find_by which returns a "single record result":/projects/main/wiki/Finders#single-record-result and YourModel::find_all_by returns "multiple records result":/projects/main/wiki/Finders#multiple-records-result. All you have to do is add an underscore and another field name after either of those two methods. Let's take a look.
256 1

                
257 1
<pre class="code"><code class="php">
258 1
# find a single book by the title of War and Peace
259 1
$book = Book::find_by_title('War and Peace');
260 1
#sql => SELECT * FROM `books` WHERE title = 'War and Peace'
261 1

                
262 1
# find all discounted books
263 1
$book = Book::find_all_by_discounted(1);
264 1
#sql => SELECT * FROM `books` WHERE discounted = 1
265 1

                
266 1
# find all discounted books by given author
267 1
$book = Book::find_all_by_discounted_and_author_id(1, 5);
268 1
#sql => SELECT * FROM `books` WHERE discounted = 1 AND author_id = 5
269 1

                
270 1
# find all discounted books or those which cost 5 bux
271 1
$book = Book::find_by_discounted_or_price(1, 5.00);
272 1
#sql => SELECT * FROM `books` WHERE discounted = 1 OR price = 5.00
273 1
</code></pre>
274 1
 
275 4 Kien La
h4(#joins). Joins
276 1

                
277 6 Kien La
A join option may be passed to specify SQL JOINS. There are two ways to produce a JOIN. You may pass custom SQL to perform a join as a simple string. By default, the joins option will not "select":/projects/main/wiki/Finders#select the attributes from the joined table; instead, it will only select the attributes from your model's table. You can pass a select option to specify the fields.
278 1

                
279 1
<pre class="code"><code class="php">
280 1
# fetch all books joining their corresponding authors
281 1
$join = 'LEFT JOIN authors a ON(books.author_id = a.author_id)';
282 1
$book = Book::all(array('joins' => $join));
283 1
# sql => SELECT `books`.* FROM `books`
284 1
#          LEFT JOIN authors a ON(books.author_id = a.author_id)
285 1
</code></pre>
286 1
 
287 6 Kien La
Or, you may specify a join via an "associated":/projects/main/wiki/Associations model.
288 1

                
289 1
<pre class="code"><code class="php">
290 1
class Book extends ActiveRecord\Model
291 1
{
292 1
  static $belongs_to = array(array('author'),array('publisher'));
293 1
}
294 1
 
295 1
# fetch all books joining their corresponding author
296 1
$book = Book::all(array('joins' => array('author')));
297 1
# sql => SELECT `books`.* FROM `books`
298 1
#          INNER JOIN `authors` ON(`books`.author_id = `authors`.id)
299 1

                
300 1
# here's a compound join
301 1
$book = Book::all(array('joins' => array('author', 'publisher')));
302 1
# sql => SELECT `books`.* FROM `books`
303 1
#          INNER JOIN `authors` ON(`books`.author_id = `authors`.id)
304 1
#         INNER JOIN `publishers` ON(`books`.publisher_id = `publishers`.id)
305 1
</code></pre>
306 1
 
307 1
Joins can be combined with strings and associated models.
308 1

                
309 1
<pre class="code"><code class="php">
310 1
class Book extends ActiveRecord\Model
311 1
{
312 1
  static $belongs_to = array(array('publisher'));
313 1
}
314 1
 
315 1
$join = 'LEFT JOIN authors a ON(books.author_id = a.author_id)';
316 1
# here we use our $join string and the association publisher
317 1
$book = Book::all(array('joins' => $join, 'publisher'));
318 1
# sql => SELECT `books`.* FROM `books`
319 1
#          LEFT JOIN authors a ON(books.author_id = a.author_id)
320 1
#         INNER JOIN `publishers` ON(`books`.publisher_id = `publishers`.id)
321 1
</code></pre>
322 1
 
323 4 Kien La
h4(#find-by-custom-sql). Find by custom SQL
324 1

                
325 6 Kien La
If, for some reason, you need to create a complicated SQL query beyond the capacity of "finder options":/projects/main/wiki/Finders#finder-options, then you can pass a custom SQL query through Model::find_by_sql(). This will render your model as "readonly":/projects/main/wiki/Finders#read-only so that you cannot use any write methods on your returned model(s).
326 1

                
327 1
*Caution:* find_by_sql() will NOT prevent SQL injection like all other finder methods. The burden to secure your custom find_by_sql() query is on you.
328 1

                
329 1
<pre class="code"><code class="php">
330 1
# this will return a single result of a book model with only the title as an attirubte
331 1
$book = Book::find_by_sql('select title from `books`');
332 1
 
333 1
# you can even select from another table
334 1
$cached_book = Book::find_by_sql('select * from books_cache');
335 1
# this will give you the attributes from the books_cache table
336 9 Jacques Fuentes
</code></pre>
337 9 Jacques Fuentes

                
338 9 Jacques Fuentes
h4(#eager-loading). Eager loading associations
339 9 Jacques Fuentes

                
340 9 Jacques Fuentes
Eager loading retrieves the base model and its associations using only a few queries. This avoids the N + 1 problem.
341 9 Jacques Fuentes

                
342 9 Jacques Fuentes
Imagine you have this code which finds 10 posts and then displays each post's author's first name.
343 9 Jacques Fuentes
<pre class="code"><code class="php">
344 9 Jacques Fuentes
$posts = Post::find('all', array('limit' => 10));
345 9 Jacques Fuentes
foreach ($posts as $post)
346 9 Jacques Fuentes
  echo $post->author->first_name;
347 9 Jacques Fuentes
</code></pre>
348 9 Jacques Fuentes

                
349 9 Jacques Fuentes
What happens here is the we get 11 queries, 1 to find 10 posts, + 10 (one per each post to get the first name from the authors table). If you had 50 posts, this would be 51 queries. 
350 9 Jacques Fuentes

                
351 9 Jacques Fuentes
We solve this problem by using the *include* option which would only issue two queries instead of 11. Here's how this would be done:
352 9 Jacques Fuentes

                
353 9 Jacques Fuentes
<pre class="code"><code class="php">
354 9 Jacques Fuentes
$posts = Post::find('all', array('limit' => 10, 'include' => array('author')));
355 9 Jacques Fuentes
foreach ($posts as $post)
356 9 Jacques Fuentes
  echo $post->author->first_name;
357 9 Jacques Fuentes

                
358 9 Jacques Fuentes
SELECT * FROM `posts` LIMIT 10
359 9 Jacques Fuentes
SELECT * FROM `authors` WHERE `post_id` IN (1,2,3,4,5,6,7,8,9,10)
360 9 Jacques Fuentes
</code></pre>
361 9 Jacques Fuentes

                
362 9 Jacques Fuentes
Since *include* uses an array, you can specify more than one association:
363 9 Jacques Fuentes
<pre class="code"><code class="php">
364 9 Jacques Fuentes
$posts = Post::find('all', array('limit' => 10, 'include' => array('author', 'comments')));
365 9 Jacques Fuentes
</code></pre>
366 9 Jacques Fuentes

                
367 9 Jacques Fuentes
You can also *nest* the *include* option to eager load associations of associations. The following would find the first post, eager load the first post's category, its associated comments and the associated comments' author:
368 9 Jacques Fuentes

                
369 9 Jacques Fuentes
<pre class="code"><code class="php">
370 9 Jacques Fuentes
$posts = Post::find('first', array('include' => array('category', 'comments' => array('author'))));
371 1
</code></pre>