Last modified 3 years ago

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 we'll use the simplified bookstore example data model from the Quickstart Guide.

The examples in this guide assume that you have already installed and gotten started using Propel.

CREATE

To add new data to the database, we instantiate the Propel-generated objects 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
/* initialize Propel, etc. */

$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:

INSERT INTO author (first_name, last_name) VALUES ('Jack', 'London');

Related Row Insert

Propel makes inserting related rows simple, by automatically "cascading" INSERT statements when a new object has other related objects added to it. In this example, we create new Author and Publisher objects, which are then added to a new Book object; all 3 objects will be saved when the Book->save() method is invoked.

<?php
/* initialize Propel, etc. */

// 1) Create an Author (row of 'author' table)

include_once 'bookstore/Author.php';

$author = new Author();
$author->setFirstName("Leo");
$author->setLastName("Tolstoy");
// note: we don't save this yet

// 2) Create a Publisher (row of 'publisher' table)

include_once 'bookstore/Publisher.php';

$pub = new Publisher();
$pub->setName("Viking Press");
// note: we don't save this yet

// 3) Create a Book (row of 'book' table)

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!

Because Propel knows whether an object has been saved or not, it would have also worked to save() the objects before adding them as relations to the Book object. (You may wish to read the wiki:Documentation/1.3/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.

Single-Col PK

Usually your table will have a single column as a primary key, which means that the retrieveByPK() method will accept a single parameter.

<?php

$firstBook = BookPeer::retrieveByPK(1);
// now $firstBook is a Book object, or NULL if no match was found.

Multi-Col PK

In some cases, your primary key may consist of more than one column. In that case the retrieveByPK() method will be created to accept multiple parameters, one for each primary key column.

For example, if we had a table with a multi-column primary key, defined like this:

   <table name="multicolpk_example" phpName="MultiColPKExample">
      <column name="id1" type="INTEGER" primaryKey="true"/>
      <column name="id2" type="INTEGER" primaryKey="true"/>
      <!-- other columns ... -->
   </table>

... then your retrieveByPK() method would be invoked like this:

<?php

$myObject = MultiColPKExamplePeer::retrieveByPK(1,2);

Getting Multiple Objects By PK

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

<?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.

Querying the Database

To select multiple rows by criteria other than the primary key, we have two choices: 1) use Propel's Criteria class, or 2) write custom SQL. The Criteria class provides a relatively simple approach to constructing a query. It's 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

Here are some examples of simple Criteria being used to return multiple objects.

Example 1: Find all authors with first name Karl but last name is _not_ Marx.

<?php

$c = new Criteria();
$c->add(AuthorPeer::FIRST_NAME, "Karl");
$c->add(AuthorPeer::LAST_NAME, "Marx", Criteria::NOT_EQUAL);

$authors = AuthorPeer::doSelect($c);
// $authors contains array of Author objects

... results in SQL query like:

SELECT ... FROM author WHERE author.FIRST_NAME = 'Karl' AND author.LAST_NAME <> 'Marx';

Example 2: Find all authors with last name of Tolstoy, Dostoevsky, or Bakhtin

<?php

$c = new Criteria();
$c->add(AuthorPeer::LAST_NAME, array("Tolstoy", "Dostoevsky", "Bakhtin"), Criteria::IN);

$authors = AuthorPeer::doSelect($c);
// $authors contains array of Author objects

... results in SQL query like:

SELECT ... FROM author WHERE author.LAST_NAME IN ('Tolstoy', 'Dostoevsky', 'Bakhtin');

Logically Complex Criteria

When you need to express logical relationships (AND, OR, etc.) between different criteria clauses, you need to manually combine individual Criterion objects together. The Criterion objects are the components that are transparently assembled into the Criteria object when you use the Criteria->add() method.

Example 1: Find all authors with first name "Leo" OR last name of "Tolstoy", "Dostoevsky", or "Bakhtin"

<?php

$c = new Criteria();
$cton1 = $c->getNewCriterion(AuthorPeer::FIRST_NAME, "Leo");
$cton2 = $c->getNewCriterion(AuthorPeer::LAST_NAME,  array("Tolstoy", "Dostoevsky", "Bakhtin"), Criteria::IN);
 
// combine them
$cton1->addOr($cton2);
 
// add to Criteria
$c->add($cton1);

... results in SQL query like:

SELECT ... FROM author WHERE (author.FIRST_NAME = 'Leo' OR author.LAST_NAME IN ('Tolstoy', 'Dostoevsky', 'Bakhtin'));

There are also some Criteria shortcuts if you want to perform a query with logical relationships between clauses that reference the same column.

Example 2: Find all authors with first name 'Leo' or 'Karl'

Using verbose Criterion approach, this looks like:

<?php

$c = new Criteria();
$cton1 = $c->getNewCriterion(AuthorPeer::FIRST_NAME, "Leo");
$cton2 = $c->getNewCriterion(AuthorPeer::FIRST_NAME, "Karl");
 
// combine them
$cton1->addOr($cton2);
 
// add to Criteria
$c->add($cton1);

Using shorthand Criteria methods:

<?php

$c = new Criteria();
$c->add(AuthorPeer::FIRST_NAME, "Leo");
$c->addOr(AuthorPeer::FIRST_NAME, "Karl");

It is important to note that these shorthand Criteria methods have a number of limitations -- primarily that they only work for single column relationships. From the author's perspective, it is not adviseable to use these methods when you need to express logical relationships between clause since they obscure the actual relationsihps between the Criterion objects and can easily lead to difficult-to-debug query generation issues. (These methods will probably change or go away in Propel 2.)

Using Custom SQL

Propel is designed to work with you rather than against you. In many 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.

So, with only a little bit more work, you can also get objects from your database using SQL. Using SQL to query the database introduces us to the generated populateObjects() method in our Peer classes -- which is called behind the scenes by the doSelect() method. This method expects to be passed a Creole ResultSet object, numerically indexed (i.e. which was created using !ResultSet::FETCHMODE_NUM option to executeQuery()).

Example 1: Use sub-selects to populate database

<?php

$con = Propel::getConnection(DATABASE_NAME);

$sql = "SELECT books.* FROM books WHERE NOT EXISTS (SELECT id FROM review WHERE book_id = book.id)";  
$stmt = $con->prepare($sql);
$stmt->execute();

$books = BookPeer::populateObjects($stmt);

Important things to remember when using custom SQL to populate Propel:

ResultSet column must be numerically indexed
ResultSet must contain all columns in the object
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 we've already seen in the RETRIEVE and CREATE sections.

<?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
/* initialize Propel, etc. */

// 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

BookPeer::doDelete(1);

Example 2: Delete using instantiated object

<?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. Some people prefer to do it this way, instantiating an object and then calling the delete() method on it. Others feel that this is "weird" since then you are left with an object that doesn't actually point to a database row. Whatever - you are free to choose :)

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

Download in other formats:

Plain Text