Jump to main content Jump to doc navigation

The xPDOQuery extends the xPDOCriteria class and allows you to abstract out complex SQL queries into an OOP format. This allows encapsulation of SQL calls so that they can work in multiple database types, and be easy to read and dynamically build.

  1. xPDOQuery.andCondition
  2. xPDOQuery.groupby
  3. xPDOQuery.innerJoin
  4. xPDOQuery.leftJoin
  5. xPDOQuery.limit
  6. xPDOQuery.orCondition
  7. xPDOQuery.rightJoin
  8. xPDOQuery.select
  9. xPDOQuery.setClassAlias
  10. xPDOQuery.sortby
  11. xPDOQuery.where

Examples

Grab the first 4 Boxes with:

  1. Owners that have the letter 'a' in their names
  2. A width of at least 10
  3. A height that is not 2
  4. A color of 'red','blue' or 'green'
  5. sorted by the Box name, ascending and then by the Box height, descending
$query = $xpdo->newQuery('Box');
// Remember: syntax here is classname, your alias. Note that filters use the alias.
$query->innerJoin('Owner','User');
// the Owner is actually a User object, defined as Owner in the relationship alias
$query->where(array(
    'Owner.name:LIKE' => '%a%',
    'Box.width:>=' => 10,
    'Box.height:!=' => 2,
    'Box.color:IN' => array('red','green','blue'),
));
$query->sortby('Box.name','ASC');
$query->sortby('Box.height','DESC');
$query->limit(4);
$boxes = $xpdo->getCollection('Box',$query);

You can also do more complex queries, like so:

$query = $xpdo->newQuery('Person');
$query->where(array(
    array(
        'first_name:=' => 'Bob',
        array(
            'OR:last_name:LIKE' => 'Boblablaw',
            'AND:gender:=' => 'M',
        ),
    ),
    'password:!=' => null,
));

translates to:

(
  (      `Person`.`first_name` = 'Bob'
    OR ( `Person`.`last_name` LIKE 'Boblablaw' AND `Person`.`gender` = 'M' )
  )
  AND password IS NOT NULL
)

Note that if you're specifying the conditional in the key string, such as 'OR:disabled:!=' => true, you'll need to specify the operand as well. This means that you must specify = explicitly, such as in: 'AND:gender:=' => 'M'

Valid Operators

$c = $xpdo->newQuery('Person');
$c->where(array(
  'name:=' => 'John', /* Equal To */
  'name:!=' => 'Sue', /* Unequal To */
  'age:>' => '21', /* Greater Than */
  'age:>=' => '21', /* Greater Than or Equal To */
  'age:<' => '18', /* Less Than */
  'age:<=' => '18', /* Less Than or Equal To */
  'search:LIKE' => 'Term', /* LIKE statement */
  'field' => null, /* check for NULL */
  'ids:IN' => array(1,2,3), /* IN statement */
));

Debugging

Sometimes you need to see what query is actually being generated. You can do this by preparing the query and outputting it using the toSQL() method.

$c = $xpdo->newQuery('Person');
// add filters here...
$c->prepare();
print $c->toSQL();

See Also

Support the team building MODX with a monthly donation.

The budget raised through OpenCollective is transparent, including payouts, and any contributor can apply to be paid for their work on MODX.

Backers

  • modmore
  • STERC
  • Digital Penguin
  • Jens Wittmann – Gestaltung & Entwicklung
  • Fabian Christen
  • Dannevang Digital
  • Sepia River Studios
  • Chris Fickling
  • CrewMark
  • deJaya
  • eydolan
  • Following Sea
  • Lefthandmedia
  • Murray Wood
  • Anton Tarasov
  • Stéphane Jäggi
  • Raffy
  • Snow Creative
  • Nick Clark
  • A. Moreno
  • JT Skaggs
  • Helen
  • YJ
  • krisznet
  • Richard
  • Yanni

Budget

$311 per month—let's make that $500!

Learn more