Blog: Propel Queries: Now With Manual Binding, Too
Propel is quite good at guessing the binding type to use in your queries. But sometimes you need to force a binding type which is not the one Propel would have guessed. Starting with the next minor release (1.6.4), Propel will be able to do it.
Propel Guesses Binding Types From Your Schema
Consider the following query:
$books = BookQuery::create() ->filterByTitle('War%') ->filterByPrice(array('max' => 20)) ->find();
Propel translates this query into the following SQL prepared statement:
SELECT book.* FROM book WHERE book.TITLE LIKE ? AND book.PRICE < ?
Then, when you call
find(), Propel uses PDO to bind the question mark placeholders with the values used in the
filterByXXX() methods. Propel uses the binding type of the column as declared in the schema. Continuing on the previous example, where the
book.TITLE column is a
VARCHAR and the
book.PRICE column is a
INTEGER, Propel binds the values as follows:
$stmt = $con->prepare($sql); $stmt->bindValue(1, 'War%', PDO::PARAM_STR); // book.TITLE is a VARCHAR $stmt->bindValue(2, 20, PDO::PARAM_INT); // book.PRICE is an INTEGER $stmt->execute();
But what if you want to use another binding type?
Cases When You Need a Custom Binding Type
filterByXXX() methods are always tied to a column, so for these Propel always knows what binding to use. However, when you use the relational API, you can create conditions on more than just columns.
$books = BookQuery::create() ->where("LOCATE('War', Book.Title) = ?", true) ->find();
In this case, the binding should use
PDO::PARAM_BOOL, and not
PDO::PARAM_STR, which is the type Propel uses for the
book.TITLE column, declared as
Another example is when using
$books = BookQuery::create() ->withColumn('SUBSTRING(Book.Title, 1, 4)', 'title_start') ->having('title_start = ?', 'foo') ->find();
Here, Propel simply refuses the query, failing with a loud:
PropelException: Cannot determine the column to bind to the parameter in clause 'title_start = ?'
This is because the virtual column
title_start has no intrinsec type, so Propel cannot determine which binding to use.
Concretely, that means that the
having() support is somehow broken in Propel 1.6. Apart from concatenating the value to the SQL clause (and risking SQL injection), you cannot add a
HAVING clause using
Forcing a Custom Binding Type.
...until now. A way to force a custom binding type has just made its way to the Propel 1.6 master branch - and that means that it will be available in Propel 1.6.4.
It's as simple as it should be: just add the desired binding type as third parameter of either
having(), and you're good to go:
// custom binding in where() $books = BookQuery::create() ->where("LOCATE('War', Book.Title) = ?", true, PDO::PARAM_BOOL) ->find(); // custom binding in having() $books = BookQuery::create() ->withColumn('SUBSTRING(Book.Title, 1, 4)', 'title_start') ->having('title_start = ?', 'foo', PDO::PARAM_STR) ->find();
No more errors, no more SQL injection risk. You're in control of the binding type when you need it.
More than ever, there is no limit to what you can do with Propel - and less limits to what you can do with the awesome PropelQuery API.