Raw SQL

Using raw SQL is sometimes the only sensible option for retrieving the records you need. When the Snippet interface starts becoming more trouble than it's worth, it's not the worst idea to write your own query manually. This is especially true when you need to take advantage of the underlying MySQL functions, e.g. for aggregating data, or converting dates. Reporting queries often require more than the straight-forward joins.

This page centers around what you can do with the &_sql parameter, for example:

[[Query? &_sql=`SELECT NOW()`]]

Limit and Offset

Although the intention of the &_sql parameter is to allow you to execute an explicit SQL query, the &_limit and &_offset parameters are still evaluated because they are so commonly used for pagination. If they are present, they will cause LIMIT and/or OFFSET clauses to be appended to your query!

For example:

[[Query? 
    &_sql=`SELECT * FROM modx_site_snippets` 
    &_limit=`3` 
    &_offset=`2`
]]

Would result in a query like this:

SELECT * FROM modx_site_snippets LIMIT 3 OFFSET 2

Supplying Dynamic Values to your Query

A query that never changes is not terribly useful. Often you will want to supply a variable to the query. For example, imagine you have a reporting query that shows summarized data for a particular user. It would be handy if you could pass a user_id to the query. With Query, this is possible by using its input value modifiers, which can read values out of the $_GET or $_POST arrays. Savvy readers will realize that modifying the SQL statement as a string is usually a bad idea because it risks SQL-injection attacks. For this reason, any inputs are quoted using PDO quote.

Example: Listing Pages from a single Year

This is a query we might use in an archival functionality: show all pages matching a single year:

[[!Query? 
    &_sql=`SELECT * FROM modx_site_content WHERE YEAR(FROM_UNIXTIME(createdon))=[[+query.year]]`
    &year=`y:get=2011`
]]

This isn't easy for the xPDO to do directly since we are leveraging special MySQL functions. Remember that the createdon is stored as an integer (i.e. as a Unix timestamp), so it must be converted to a MySQL date before we can use MySQL date operators on it.