When the built-in finders and other methods offered will not work, you can create custom SQL queries using the database-independent query builder or by writing the SQL by hand.
Query Builder
The query builder is a great way to perform simple custom queries in a way that is completely independent of the specific database or adapter you are using. This ensures the query is portable to any other database type by using any of the available database adapters.
<?php
// Query Builder
$posts = $postMapper->select()
->from('mytable')
->where(array('user_id' => '5'))
->orWhere(array('account_id' => array(1, 2, 3, 6, 12, 82)));
Produces the following SQL with the MySQL adapter:
SELECT *
FROM mytable
WHERE (user_id = 5)
OR (account_id IN ('1', '2', '3', '6', '12', '82'))
Custom Queries / Raw SQL
Sometimes there are situations where writing your own custom query will be the most efficient solution to a particular problem. In these cases, you can write your own raw SQL using the query() method:
<?php
// Custom Query
$posts = $postMapper->query("
SELECT id, name
FROM categories
JOIN posts ON (posts.category_id = categories.id)
");
Bound Parameters
If you have any values to pass your custom query and still want to use
prepared statments with bound parameters, phpDataMapper can also handle
this using the query() function’s optional second parameter. The
placeholder values are given in numbered or named parameters using the
same array syntax as PHP’s PDO
extension.
<?php
// Numbered Placeholders
$posts = $postMapper->query("
SELECT *
FROM posts
WHERE slug = ?
", array('my-first-post'));
// Named Placeholders
$posts = $postMapper->query("
SELECT *
FROM posts
WHERE slug = :slug
", array('slug' => 'my-first-post'));