Propel: Creating Custom Query

Propel's Query class allows us to construct query without writing the actual sql. It's good for common queries but if we have to build complex queries, it may prove more less effective.

Knowing how to create custom queries also gives you a way to select data from view tables. I don't know why propel does not generate models for view tables, probably because they are view tables and does not really do anything except to select data. Anyway, I decided to write about creating your custom queries so to help you on that.

Let's say I have a table called 'books' with the following fields

id
title
isbn

To create a custom query, do it like this:

$sql = "SELECT * FROM books";
$con = Propel::getConnection();
$stmt = $con->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

Now i can select the query like this:

foreach($results as $row) {
    echo $row['id'];
    echo $row['title'];
    echo $row['isbn'];
}

The good thing about propel is that we can use this result from our custom query to populate the Book object.

$formatter = new PropelObjectFormatter();
$formatter->setClass('Book');
$books = $formatter->format($stmt);

Now, using the Book model, we can call the result like this:

foreach($books as $book) {
    echo $book->getId();    
    echo $book->getTitle();    
    echo $book->getIsbn();    
}