Skip to content

Home

Jump to bottom Edit New page
Everett Griffiths edited this page Feb 11, 2015 · 37 revisions

About

Query is a generic tool for querying collections in MODX Revolution via Snippet parameters and formatting the results, including pagination. It attempts to be as transparent as possible in its interactions with the xPDO Query objects, with modifications for Snippet syntax. Query can be a replacement for getResources, but it can also be used to query other collections (e.g. list all users, chunks, etc.). It can also be used to issue raw database queries for reporting.

Arguments

The parameters passed to the Query Snippet are of two sorts: filters and controls. The filter arguments are specific to the object being queried and they correspond exactly to the column names of the tables involved.

Filter Arguments

Note
 The raw names of the filter arguments correspond exactly to the attribute names of your objects, which can be thought of as the column names of the corresponding database table 
[[Query? &pagetitle=`Home`]]
[[Query? &_classname=`modUser` &username=`myuser`]]

Control Arguments

All control arguments begin with an underscore.

  • _classname (optional) : the classname of the collection being queried. Default: modResource

  • _pkg (optional) : used when querying third-party custom tables. Passed to addPackage(). You can pass a semi-colon-separate string: package_name; model_path; and optionally table_prefix

  • _tpl (optional) : Chunk name or formatting string used to format each row in the result set.

  • _tplOuter (optional) : Chunk name or formatting string used to wrap the final result.

  • _limit (optional) : limit the number of results returned.

  • _sortby (optional) : Depends on the columns being queried.

  • _sortdir (optional) : Default ASC

  • _page (optional) : specifies the page number

  • _offset : specifies an offset number

  • _sql (optional) : Used to provide a raw database query. This causes the limit,offset,sortby,sortdir etc. parameters to be ignored.

  • _style (optional): set a Pagination style (see https://github.com/craftsmancoding/pagination). Default: "default".

  • _graph (optional) : JSON hash used to join on related tables via getCollectionGraph()

  • _select (optional) : used to limit the columns selected and returned when querying on simple collections. Ignored when using _graph. Default: *

  • _log_level (optional) : You can optionally increase the logging level of this script. Defaults to the global System Setting.

  • _debug (optional) : return debugging information, including the raw SQL query.

  • _view (optional) : can specify an old-school PHP view. Current available are 'table' and 'json'.

Operators

You can provide operators to your filter arguments by altering the names of the parameters. These are alphabetical versions of the operators defined by xPDO’s Query classes. The Snippet syntax requires that we avoid any symbols, so all the operators must be referenced by the following:

  • LIKE: &pagetitle:LIKE=`xyz` would translate to pagetitle LIKE \'%xyz%'

  • NOT_LIKE: &pagetitle:NOT_LIKE=`xyz` would translate to pagetitle NOT LIKE \'%xyz%'

  • STARTS_WITH: maps to like, but quotes input as 'value%'

  • ENDS_WITH: maps to like, but quotes input as '%value'

  • GT: greater than

  • GTE: greater than or equal to

  • LT: less than

  • LTE: less than or equal to

  • E: equal

  • NE: not equal

  • IN: will split on commas in the the argument

  • NOT_IN: will split on commas in the argument

Manually Set Operator

If your Snippet syntax ever becomes too unwieldy or if you prefer not to specify your operators inline, you can specify the operator separately by in an argument that prefixes the column name with op.

For example, following two statements are equivalent:

[[Query? &pagetitle:LIKE=`San`]]
[[Query? &pagetitle=`San` &_op_pagetitle=`LIKE`]]

Filter Joining

By default, your filter criteria are joined using "AND", e.g. get the books where title starts with "A" and author equals "Smith". You can specify "OR:" as a condition on your parameter names. For example if you want to retrieve pages using either template 3 or 4, you could use the IN argument, or use the "OR:" to join the filter paramaters:

[[Query? &template=`3` &OR:template:e=`4`]]

Note that if you specify how your criteria are joined, you must specify an operator after the column name (e.g. you must explicitly use e for equals or set another valid operator).

Input Value Modifiers

Inspired by MODX’s Output Filters (see http://goo.gl/bSzfwi), the Query Snippet supports dynamic inputs via its own "value modifiers" that mimic the syntax used by MODX for its output filters (aka "output modifiers). This is useful for building search forms or enabling pagination.

For example, you can change the &_sortby argument dynamically by setting a URL parameter, then you can adjust your Query snippet call to read the "sortby" $_GET variable:

[[!Query? &_sortby=`sortby:get`]]

The following value modifiers included:

  • get : causes the named value to read from the $_GET array. $options = default value.

  • post : causes the named value to read from the $_POST array. $options = default value.

  • decode : runs json_decode on the input. Useful if you need to pass an array as an argument.

You can also supply your own Snippet names to be used as value modifiers. They should accept the following inputs:

  • $input : the value sent to the snippet. E.g. in &_sortby=xyz:get, the $input is "xyz"

  • $options : any extra option. E.g. &_sortby=xyz:get=123, the $options is "123". These may be quoted any way you prefer.

Warning
 Use input filters with extreme caution! Query does not perform any data sanitization directly, so inputs could be exploited via SQL injection if you exposed a value that should not be exposed (like &_sql).

Examples

Fetch pages matching a certain template:

[[!Query? &template=`3`]]

Find users whose usernames begin with "B":

[[!Query? &_classname=`modUser` &username:STARTS_WITH=`b`]]

Paginate all manager events whose names begin with "namespace" and set a URL trigger to listen for $_GET['d'] to trigger debugging information:

[[!Query? &_classname=`modManagerLog` &_limit=`10` &action:STARTS_WITH=`namespace` &_debug=`d:get=0`]]

Return JSON data so query can be used to supply an Ajax form:

[[!Query? &_classname=`modChunk` &_limit=`10` &_view=`json`]]

Search for a range:

[[!Query? &_classname=`Product` &price:GT=`100` &AND:price:LTE=`199`]]

Quickly set up a search form by listening for post-data, and join on related tables:

<form action="" method="post">
    Username: <input type="text" name="username" value="[[+query.username]]" /><br />
    <input type="submit" value="Search" />
</form>
[[!Query? &_classname=`modUser` &_graph=`{"Profile":{}}` &_select=`id,username,Profile.email` &username:LIKE=`username:post`]]

Get a specific list of Chunks:

[[!Query? &_classname=`modChunk` &name:IN=`header,footer,meta`]]

Paginated output:

[[!Query? &_limit=10 &_style=`apple`
        &_tpl=`myTpl`
        &_tplOuter=`<ul>[[+content]]</ul>[[+pagination_links ]]`
        &_offset=`offset:get`]]

Where myTpl contains

<li><a href="[[~[[+id]]]]">[[+pagetitle]]</a></li>

Placeholders

The Query Snippet sets several placeholders that you can use anywhere in your page or template or in our tplOuter.

  • [[+page_count]] : integer count of the number of pages of results available

  • [[+results]] : the actual list of results

  • [[+pagination_links]] : links to other result pages. This will be empty if there are no results or not enough results to require pagination.

Limitations

Query provides a syntax that aims to support the most common SQL query patterns. It does not attempt to support all valid SQL groupings or operators. If the arguments here get to be too complicated to read, that’s when you should consider writing your own Snippet.

Add a custom footer
Add a custom sidebar
Clone this wiki locally