title: Complex Queries layout: docs_page date: 2014-08-15 16:25:14
There are a number of conditional operations in Spot that can be used to build more complex queries without obtuse query builder syntax or long namespaced constants.
A Query Example
# All posts with a 'published' or 'draft' status, descending by date_created, limit 10 $posts = $mapper->where(['status' => 'published']) ->orWhere(['status' => 'draft']) ->order(['date_created' => 'DESC']) ->group(['id']) ->having(['id > 20']) ->limit(10, 20);
SELECT * FROM posts WHERE status = 'published' OR WHERE status = 'draft' GROUP BY id ORDER BY date_created DESC HAVING id > 20 LIMIT 10 OFFSET 20
Accepted Query Values
Spot can use many different types of values in the query builder, and will automatically do the right thing with them.
Scalar values like normal strings, integers, and floats will be handled normally:
$posts = $mapper->all()->where(['status >=' => 3]);
SELECT * FROM posts WHERE status >= 3
DateTime objects can be used as query values, and will be automatically
converted to the native database driver's required format.
# All posts created before 3 days ago $posts = $mapper->where(['date_created <' => new DateTime('-3 days')]);
SELECT * FROM posts WHERE date_created < '2014-08-12'
Array values passed to Spot's query builder will result in automatic "IN" clause.
// Posts with 'id' of 1, 2, 5, 12, or 15 $posts = $mapper->all()->where(['id' => [1, 2, 5, 12, 15]]);
SELECT * FROM posts WHERE id IN(1, 2, 5, 12, 15)
If a value is
null, Spot will use the proper SQL syntax:
$posts = $mapper->all()->where(['status !=' => null]);
SELECT * FROM posts WHERE status IS NOT NULL
If a value is boolean
false, Spot will use the proper SQL syntax
according to the database adapter you are using:
$posts = $mapper->all()->where(['is_active' => true]);
-- PostgreSQL SELECT * FROM posts WHERE is_active = 't'; -- MySQL SELECT * FROM posts WHERE is_active = 1;
There is no substitute for the power and expressiveness of SQL. While ORMs like Spot are very nice to use, if you need to do complex queries, it's best to just use custom queries with the SQL you know and love. This is why Spot's query builder is fairly simple compared to other ORMs, and doesn't support things like subqueries, etc.
Spot provides a
query method that allows you to run custom SQL, and load the
results into a normal collection of entity objects. This way, you can easily run
custom SQL queries with all the same ease of use and convenience as the
built-in finder methods and you won't have to do any special handling.
Using Custom SQL
$posts = $mapper->query("SELECT * FROM posts WHERE id = 1");
Using Query Parameters
$posts = $mapper->query("SELECT * FROM posts WHERE id = ?", );
Using Named Placeholders
$posts = $mapper->query("SELECT * FROM posts WHERE id = :id", ['id' => 1]);
Joins are currently not enabled by Spot's query builder. The Doctine DBAL query builder does provide full support for them, so they may be enabled in the future.