= Basic C.R.U.D. Operations =

This guide will show you how to perform basic C.R.U.D. (Create, Retrieve, Update, Delete) operations on your database using Propel. For consistency, the simplified bookstore example data model from the [wiki:Users/Documentation/1.3/QuickStart Quickstart Guide] will be used.

The examples in this guide assume that you have already [wiki:Users/Documentation/1.3/Installation installed] and [wiki:Users/Documentation/1.3/QuickStart gotten started using] Propel.

== Create ==

To add new data to the database, instantiate a Propel-generated object and then call the ''save()'' method. Propel will generate the appropriate INSERT SQL from the instantiated object.

=== Simple INSERT ===

In its simplest form, inserting a new row would look like this:

{{{
#!php
<?php
/* initialize Propel, etc. */
include_once 'bookstore/Author.php';

$author = new Author();
$author->setFirstName("Jack");
$author->setLastName("London");
$author->save();
}}}

In the background, this would result in the following SQL being executed on the database:
{{{
#!sql
INSERT INTO author (first_name, last_name) VALUES ('Jack', 'London');
}}}

=== Related Row Insert ===

Propel creates setters for related objects that simplify the foreign key handling. You don't actually have to define a foreign key value. Instead, just set a related object, as follows:

{{{
#!php
<?php
/* initialize Propel, etc. */

include_once 'bookstore/Author.php';
$author = new Author();
$author->setFirstName("Leo");
$author->setLastName("Tolstoy");
$author->save();

include_once 'bookstore/Book.php';
$book = new Book();
$book->setTitle("War & Peace");
// associate the $author object with the current $book
$book->setAuthor($author);
$book->save();
}}}

Internally, the call to `Book::setAuthor($author)` translates into `Book::setAuthorId($author->getId())`. But you don't actually have to save a Propel object before associating it to another. In fact, Propel automatically "cascades" INSERT statements when a new object has other related objects added to it.

The following example shows how to create new Author and Publisher objects, which are then added to a new Book object; all 3 objects are saved when the `Book::save()` method is eventually invoked.

{{{
#!php
<?php
/* initialize Propel, etc. */

include_once 'bookstore/Author.php';
$author = new Author();
$author->setFirstName("Leo");
$author->setLastName("Tolstoy");
// no need to save the author yet

include_once 'bookstore/Publisher.php';
$pub = new Publisher();
$pub->setName("Viking Press");
// no need to the publisher yet

include_once 'bookstore/Book.php';
$book = new Book();
$book->setTitle("War & Peace");
$book->setIsbn("0140444173");
$book->setPublisher($pub);
$book->setAuthor($author);
$book->save(); // saves all 3 objects!
}}}

Refer to the [wiki:Users/Documentation/1.4/Relationships Relationships] page for more on working with related rows.

== Retrieve ==

Retrieving objects from the database, also referred to as ''hydrating'' objects, is essentially the process of executing a SELECT query against the database and populating a new instance of the appropriate object with the contents of each returned row.

In Propel, you use the generated ''Peer'' classes to select existing rows from the database. The Peer classes are classes with exclusively static methods for performing operations on a particular table.  There are several methods generated to assist you in selecting a single object or querying for multiple objects from the database.

=== Retrieving by Primary Key ===

The simplest way to retrieve an object (row) from the database, is to use the generated `retrieveByPK()` method.  This method must be passed the primary key value for the object you wish to retrieve.

Usually, tables have a single column as a primary key. In these cases, the `retrieveByPK()` method accepts a single parameter.

{{{
#!php
<?php
$firstBook = BookPeer::retrieveByPK(1);
// now $firstBook is a Book object, or NULL if no match was found.
}}}

In some cases, a primary key may consist of more than one column. In that case `retrieveByPK()` accepts multiple parameters, one for each primary key column.

You can also select multiple objects based on their primary keys, by calling the generated `retrieveByPKs()` method. It takes as a parameter an array of primary keys:

{{{
#!php
<?php
$selectedBooks = BookPeer::retrieveByPKs(array(1,2,3,4,5,6,7));
// $selectedBooks is an array of Book objects
}}}

Note that this only works for tables with single-column primary keys.

=== Reading Object Properties ===

Propel maps the columns of a table into properties of the generated objects. For each property, you can use a generated getter to access it.

{{{
#!php
<?php
$book = BookPeer::retrieveByPK(1);
echo $book->getFirstName();
echo $book->getLastName();
}}}

The definition of a foreign key in the schema results in a foreign object getter:

{{{
#!php
<?php
$pub = $book->getPublisher();
$author = $book->getAuthor();
}}}

=== Querying the Database ===

To select multiple rows by criteria other than by the primary key, you can either use Propel's `Criteria` class, or write custom SQL. 

The `Criteria` class provides a relatively simple approach to constructing a query. Its database neutrality and logical simplicity make it a good choice for expressing many common queries; however, for a very complex query, it may prove more effective (and less painful) to simply use a custom SQL query to hydrate your Propel objects.

==== Simple Criteria ====

An empty `Criteria` object carries no condition.
{{{
#!php
<?php
$c = new Criteria();
$authors = AuthorPeer::doSelect($c);
// $authors contains an array of Author objects
// one object for every row of the author table
foreach($authors as $author) {
  echo $author->getFirstName();
}
}}}

Each of the Propel-generated Peer classes have a `doSelect()` method accepting a `Criteria` as parameter. This method tranlates the Criteria into SQL, executes the SQL query, and populates (hydrates) Propel objects with the results.

To add a simple condition to a Criteria, simply call the `Criteria::add($column, $value)` method. For example, to find all authors with first name "Karl", create a Criteria as follows:

{{{
#!php
<?php
$c = new Criteria();
$c->add(AuthorPeer::FIRST_NAME, "Karl");
$authors = AuthorPeer::doSelect($c);
}}}

This results in SQL query like:
{{{
#!sql
SELECT * FROM author WHERE author.FIRST_NAME = 'Karl';
}}}

Notice that you should use the Peer column constants to designate columns in Criteria. These constants are generated according to the column name in the database by uppercasing them, so the `AuthorPeer::FIRST_NAME` refers to the `first_name` column of the author table.

Also, when you pass a value as second argument to `Criteria::add()`, this value is escaped using the PDO type of the column passed as first argument. This protects you from SQL injection risks.

Pretty much any SQL statement can be built using the Criteria API. For instance, in order to hydrate Author objects from the following query:
{{{
#!sql
SELECT book.* 
FROM book INNER JOIN author ON book.AUTHOR_ID = author.ID 
WHERE book.ISBN = '0140444173' author.FIRST_NAME = 'Leo'
ORDER BY author.LAST_NAME ASC, book.TITLE ASC
LIMIT 10;
}}}

...you can build the following Criteria:
{{{
#!php
<?php
$c = new Criteria();
$c->add(BookPeer::ISBN, '0140444173');
$c->addJoin(BookPeer::AUTHOR_ID, AuthorPeer::ID, Criteria::LEFT_JOIN);
$c->add(AuthorPeer::FIRST_NAME, "Leo");
$c->addAscendingOrderByColumn(AuthorPeer::LAST_NAME);
$c->addAscendingOrderByColumn(BookPeer::TITLE);
$c->setLimit(10);
$authors = AuthorPeer::doSelect($c);
}}}


Refer to the [wiki:Users/Documentation/1.4/Criteria Criteria] page detailed Criteria usage and examples.

==== Using Custom SQL ====

Propel is designed to work with you rather than against you. In some cases writing a complex query using `Criteria` ends up being really messy and even less intelligible or maintainable than a standard SQL query.  Propel was created to work the inherent limitations of a database-neutral `Criteria` system by making it easy for you to use your own SQL queries to hydrate result sets.

As Propel uses PDO to query the underlying database, you can always write custom queries using the PDO syntax. For instance, if you have to use a sub-select:

{{{
#!php
<?php
$con = Propel::getConnection(BookPeer::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' => 'Tolstoy');
}}}

With only a little bit more work, you can also populate `Book` objects from the resulting statement. Just use the resulting statement as the parameter to the generated `populateObjects()` method in you Peer class -- which is called behind the scenes by the `doSelect()` method:

{{{
#!php
<?php
$books = BookPeer::populateObjects($stmt);
// $books contains an array of Book objects
}}}

There are a few important things to remember when using custom SQL to populate Propel:
  * The resultset columns must be numerically indexed
  * The resultset must contain all columns in the object
  * The resultset must have columns ''in the same order'' as they are defined in the `schema.xml` file


== Update ==

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
<?php
// 1) Fetch an object by primary key
$myBook = BookPeer::retrieveByPK(1);

// 2) update the values & save() it.
$myBook ->setTitle("War & Peace");
$myBook->save();
}}}

There's really not much more to it than that. Of course you can also update relationships in a similar fashion to what was already shown in the CREATE section.

{{{
#!php
<?php
// 1) retrieve an Author
$author = AuthorPeer::retrieveByPK(1);

// 2) retrieve a Book
$book = BookPeer::retrieveByPK(1);

// 3) now blindly set $author as the author for $book!
$book->setAuthor($author);
$book->save();
}}}

== DELETE ==

Deleting objects can be accomplished using the Peer classes or the object classes.

=== Using Peer === 

You can use the generated `doDelete()` method of the generated Peer classes to delete rows from your tables.  You can pass this method a primary key, an instance of the appropriate object, or even a `Criteria` object (however this isn't very useful, since you can only delete by primary key).

'''Example 1:''' Delete using primary key

{{{
#!php
<?php
BookPeer::doDelete(1);
}}}

'''Example 2:''' Delete using instantiated object

{{{
#!php
<?php
$book = BookPeer::retrieveByPK(1);
BookPeer::doDelete($book);
}}}

=== Using Object === 

For consistency with other C.R.U.D. operations, you can also delete a database row using the object class by calling the `delete()` method on it:

{{{
#!php
$book = BookPeer::retrieveByPK(1);
$book->delete();
// (and now you must remember that you can no longer use the $book object)
}}}
