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()`]]

FORMATTING: this page deals exclusively with utilizing the &_sql parameter. See the Home page for information on formatting your results.

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 a couple special MySQL functions: YEAR and FROM_UNIXTIME. Remember that the createdon field is stored as an integer (a Unix timestamp), so it must be converted to a MySQL date before we can use MySQL date operators on it.

You would pass a value when linking to the page containing this snippet, e.g. [[~123? y=2012]]

In this example, we are using a placeholder of [[+query.year]] to denote where we will insert a year. To fill that placeholder, we use an input value filter on the &year input: y:get=2011.

• y : denotes a key in either the $_GET or $_POST arrays.
• :get : this is our "input modifier", which says to look inside the $_GET array for the preceding key.
• =2011 : this defines a default value for the input. You can quote the value however you want or not at all.

Note that the use of &year argument is purely arbitrary: the important thing is that it generates a corresponding placeholder with a "query." prefix. The use of the $_GET['y'] parameter is also arbitrary: we can listen for any parameter we want so long as it doesn't interfere with normal MODX operation (e.g. avoid q and page).

To demonstrate that these were arbitrary choices, let's rewrite that same Snippet using different argument names:

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

In this case, using the &pigs argument name corresponds to a placeholder named [[+query.pigs]]. Also our input modifier operates on a value of wings, so we are listening for values passed via $_GET['wings'].

Example: Listing Pages from a single Month

This is almost the same query as before -- it's also useful for powering archive pages. Remember that we probably want to filter based on month and year. Filtering by only month would return results for the given month during any calendar year.

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

Now when linking to the page containing this snippet, we must pass a y and m parameter:

[[~123? &y=2012 &m=12]]