[TOC]
# 基礎 C.R.U.D. 操作
本章節, 我們將學習如何使用Propel對您的數據庫進行基礎的 C.R.U.D. (增、刪、改、查)操作.
## 新增數據
要在數據庫增加新的數據, 首先實例化一個Propel自動生成的對象, 然后調用其中的save()方法, 實例化的對象會自動生成合適的INSERT SQL語句.
稍等, 在增加數據之前, 您可能會想定義一下列的值. 為了實現這一目的, Propel已經在對象里為表中的每一列生成了 setXXX() 方法.因此，在最簡單的形式中，插入一個新行就像下面的內容：
~~~
<?php
/* initialize Propel, etc. */
$author = new Author();
$author->setFirstName('Jane');
$author->setLastName('Austen');
$author->save();
~~~
setXXX() 方法中的 XXX 對應您在schema文件中為 \<column\> 定義的phpName屬性值, 如果phpName沒有被定義的話, XXX則會是駝峰風格(CamelCase)的列名
在后臺, 調用的save()方法會在數據庫中執行以下SQL:
~~~
INSERT INTO author (first_name, last_name) VALUES ('Jane', 'Austen');
~~~
## 讀取對象的屬性
Propel 生成的對象屬性映射了表中對應的列值. 每個屬性您都可以使用一個已經自動生成的get方法來獲取它.
~~~
<?php
echo $author->getId(); // 1
echo $author->getFirstName(); // 'Jane'
echo $author->getLastName(); // 'Austen'
~~~
在schema.xml文件中定義id列為自增鍵的話, id列會被數據庫自動設置. 一旦對象被保存，這個值就很容易被檢索：只需要調用getPhpName()。
這些調用不會發出數據庫查詢，因為對象已經加載到內存中了。
您還可以通過調用下列方法之一導出對象的所有屬性： toArray(), toXML(), toYAML(), toJSON(), toCSV(), and __toString():
~~~
<?php
echo $author->toJSON();
// {"Id":1,"FirstName":"Jane","LastName":"Austen"}
~~~
> Tip
> 對于每個導出方法，Propel還提供一個導入方法對應方法。因此，可以使用fromArray()從數組中輕松填充對象，從字符串中導入則可以使用 fromXML(), fromYAML(), fromJSON(), and fromCSV().
由生成的對象提供了許多更有用的方法。您可以在[活動記錄引用](http://propelorm.org/documentation/reference/active-record.html)中找到這些方法的一個廣泛列表。
## 檢索記錄
從數據庫中檢索對象，也稱為吸水化對象(hydrating objects)，本質上是對數據庫執行select查詢, 并使用每個返回行的內容填充適當對象。
### 按主鍵檢索
從數據庫中檢索行的最簡單方法是使用生成的findPK()方法。它只需要檢索要檢索的行的主鍵的值。
~~~
<?php
$q = new AuthorQuery();
$firstAuthor = $q->findPK(1);
// now $firstAuthor is an Author object, or NULL if no match was found.
~~~
這會構造一個簡單的選擇查詢。以mysql為例：
~~~
SELECT author.id, author.first_name, author.last_name
FROM `author`
WHERE author.id = 1
LIMIT 1;
~~~
當主鍵由多個列組成時，findPK()接受多個參數，對每個主鍵列都接受一個參數。
> Tip
> 每個生成的查詢對象都提供了名為create()的工廠方法。此方法將創建查詢的新實例，并允許您在一行中寫入查詢：
> ~~~
> <?php
> $firstAuthor = AuthorQuery::create()->findPK(1);
> ~~~
您還可以調用生成的findPKs()方法，基于主鍵選擇多個對象。這個方法將主鍵數組作為參數：
~~~
<?php
$selectedAuthors = AuthorQuery::create()->findPKs(array(1,2,3,4,5,6,7));
// $selectedAuthors is a collection of Author objects
~~~
### 查詢數據庫
若要檢索除主鍵之外的行，請使用find()方法。
不帶任何條件的空查詢對象將返回表的所有行。
~~~
<?php
$authors = AuthorQuery::create()->find();
// $authors contains a collection of Author objects
// one object for every row of the author table
foreach($authors as $author) {
echo $author->getFirstName();
}
~~~
若要在給定列上添加簡單條件，請使用查詢對象中生成的filterByXXX()方法之一，其中xxx是列的phpName。由于filterByXXX()方法返回當前查詢對象，因此可以繼續添加條件，或者調用獲取結果方法從而結束查詢。例如，以名稱進行篩選：
~~~
<?php
$authors = AuthorQuery::create()
->filterByFirstName('Jane')
->find();
~~~
當將值傳遞給filterByXXX()方法時，Propel使用列類型來轉義pdo中的值。這將保護您免受sql注入風險。
> filterByXXX()是用于創建查詢的首選方法。它非常的靈活，并接受使用通配符的參數，以及用于查詢更復雜結果的數組參數。有關詳細信息，請參閱[列篩選器方法](http://propelorm.org/documentation/reference/model-criteria.html#column_filter_methods)。
您還可以輕松地對查詢結果進行限制和排序。再次強調，查詢方法會返回當前的查詢對象，這樣您就可以輕松地將它們連接起來使用：
~~~
<?php
$authors = AuthorQuery::create()
->orderByLastName()
->limit(10)
->find();
~~~
find()總是返回對象的集合，即使只有一個結果。如果您只需要一個結果，那么使用findOne()而不是find()。它將添加限制并返回一個對象而不是數組：
~~~
<?php
$author = AuthorQuery::create()
->filterByFirstName('Jane')
->findOne();
~~~
> Tip
> Propel 為這個簡單的用例提供了魔術方法。因此，可以將上面的查詢寫成：
~~~
<?php
$author = AuthorQuery::create()->findOneByFirstName('Jane');
~~~
Propel的查詢api非常強大。下一章將教您如何使用它來為相關對象添加條件。可以在API文檔[ API reference](http://propelorm.org/documentation/reference/model-criteria.html)中查閱相關信息.
#### 使用自定義sql
Query類提供了一種相對簡單的構造查詢的方法。在表達一些常見查詢的時候，使用Query類是一個很好的選擇。但是，對于一個非常復雜的查詢，使用自定義的SQL查詢來填充Propel對象也許是更輕松有效的方法。
Propel使用PDO來查詢底層數據庫，因此您可以使用PDO語法編寫自定義查詢語句。例如，如果需要使用子選擇：
~~~
<?php
use Propel\Runtime\Propel;
$con = Propel::getWriteConnection(\Map\BookTableMap::DATABASE_NAME);
$sql = "SELECT * FROM book WHERE id NOT IN "
."(SELECT book_review.book_id FROM book_review"
." INNER JOIN author ON (book_review.author_id=author.ID)"
." WHERE author.last_name = :name)";
$stmt = $con->prepare($sql);
$stmt->execute(array(':name' => 'Austen'));
~~~
只需要稍微多一點點步驟，您還可以從結果語句填充book對象。Create a new ObjectFormatter for the Book model, and call the format() method using the DataFetcher instance of the current connection with the pdo statement:
~~~
<?php
use Propel\Runtime\Formatter\ObjectFormatter;
$con = Propel::getWriteConnection(\Map\BookTableMap::DATABASE_NAME);
$formatter = new ObjectFormatter();
$formatter->setClass('\Book'); //full qualified class name
$books = $formatter->format($con->getDataFetcher($stmt));
// $books contains a collection of Book objects
~~~
在使用Propel的自定義sql填充時，有幾件重要的事情需要記住：
待填充的結果必須使用數字索引。
待填充的結果必須包含表的所有列(延遲加載的列除外)
待填充的結果必須與schema.xml文件中定義的列順序相同
## Updating Objects
Updating database rows basically involves retrieving objects, modifying the contents, and then saving them. In practice, for Propel, this is a combination of what you’ve already seen in the previous sections:
~~~
<?php
$author = AuthorQuery::create()->findOneByFirstName('Jane');
$author->setLastName('Austen');
$author->save();
~~~
Alternatively, you can update several rows based on a Query using the query object’s update() method:
~~~
<?php
AuthorQuery::create()
->filterByFirstName('Jane')
->update(array('LastName' => 'Austen'));
~~~
This last method is better for updating several rows at once, or if you didn’t retrieve the objects before.
## Deleting Objects
Deleting objects works the same as updating them. You can either delete an existing object:
~~~
<?php
$author = AuthorQuery::create()->findOneByFirstName('Jane');
$author->delete();
~~~
Or use the delete() method in the query:
~~~
<?php
AuthorQuery::create()
->filterByFirstName('Jane')
->delete();
~~~
> Tip
> A deleted object still lives in the PHP code. It is marked as deleted and cannot be saved anymore, but you can still read its properties:
~~~
<?php
echo $author->isDeleted(); // true
echo $author->getFirstName(); // 'Jane'
~~~
## Query Termination Methods
The Query methods that don’t return the current query object are called “Termination Methods”. You’ve already seen some of them: find(), findOne(), update(), delete(). There are two more termination methods that you should know about:
count() returns the number of results of the query.
~~~
<?php
$nbAuthors = AuthorQuery::create()->count();
~~~
You could also count the number of results from a find(), but that would be less effective, since it implies hydrating objects just to count them.
paginate() returns a paginated list of results:
~~~
<?php
$authorPager = AuthorQuery::create()->paginate($page = 1, $maxPerPage = 10);
// This method will compute an offset and a limit
// based on the number of the page and the max number of results per page.
// The result is a PropelModelPager object, over which you can iterate:
foreach ($authorPager as $author) {
echo $author->getFirstName();
}
~~~
A pager object gives more information:
~~~
<?php
echo $pager->getNbResults(); // total number of results if not paginated
echo $pager->haveToPaginate(); // return true if the total number of results exceeds the maximum per page
echo $pager->getFirstIndex(); // index of the first result in the page
echo $pager->getLastIndex(); // index of the last result in the page
$links = $pager->getLinks(5); // array of page numbers around the current page; useful to display pagination controls
~~~
## Collections And On-Demand Hydration
The find() method of generated Model Query objects returns a PropelCollection object. You can use this object just like an array of model objects, iterate over it using foreach, access the objects by key, etc.
~~~
<?php
$authors = AuthorQuery::create()
->limit(5)
->find();
foreach ($authors as $author) {
echo $author->getFirstName();
}
~~~
The advantage of using a collection instead of an array is that Propel can hydrate model objects on demand. Using this feature, you’ll never fall short of memory when retrieving a large number of results. Available through the setFormatter() method of Model Queries, on-demand hydration is very easy to trigger:
~~~
<?php
$authors = AuthorQuery::create()
->limit(50000)
->setFormatter(ModelCriteria::FORMAT_ON_DEMAND) // just add this line
->find();
foreach ($authors as $author) {
echo $author->getFirstName();
}
~~~
In this example, Propel will hydrate the Author objects row by row, after the foreach call, and reuse the memory between each iteration. The consequence is that the above code won’t use more memory when the query returns 50,000 results than when it returns 5.
ModelCriteria::FORMAT_ON_DEMAND is one of the many formatters provided by the Query objects. You can also get a collection of associative arrays instead of objects, if you don’t need any of the logic stored in your model object, by using ModelCriteria::FORMAT_ARRAY.
The ModelCriteria Query API reference describes each formatter, and how to use it.
## Propel Instance Pool
Propel keeps a list of the objects that you already retrieved in memory to avoid calling the same request twice in a PHP script. This list is called the instance pool, and is automatically populated from your past requests:
~~~
<?php
// first call
$author1 = AuthorQuery::create()->findPk(1);
// Issues a SELECT query
...
// second call
$author2 = AuthorQuery::create()->findPk(1);
// Skips the SQL query and returns the existing $author1 object
~~~
