1. Overview
      1. What It Is
      2. Why I Would Want to Use It
      3. Known Issues
    2. Getting Started
      1. Fundamentals
        1. Design Patterns
        2. xPDO, the Class
          1. As Object and Relational Mapper
          2. As PDO Wrapper
          3. As Service Layer
          4. The xPDO Constructor
            1. Hydrating Fields
      2. Introduction to the xPDO Object Model
        1. Understanding xPDOCriteria and xPDOQuery
      3. Creating a Model With xPDO
        1. Defining a Schema
          1. Defining Relationships
          2. Defining the Database and Tables
            1. Upgrading Models to Schema Version 1.1
          3. More Examples of xPDO XML Schema Files
          4. Validation Rules in your Schema
        2. Generating the Model Code
          1. Domain Classes
          2. O-R Maps
          3. Table Classes
      4. Using Your xPDO Model
        1. Creating Objects
        2. Database Connections and xPDO
        3. Loading Packages
        4. Removing Objects
          1. removeCollection
        5. Retrieving Objects
          1. getCollectionGraph
        6. Setting Object Fields
        7. Working with Related Objects
    3. Extending Your xPDO Model
      1. Adding Business Logic to this CRUD
      2. Overriding Derived Behavior
        1. Using Custom Object Loaders
      3. Working With Transient Object Classes
    4. Advanced Features
      1. Caching
        1. Caching Tutorial - Basic Snippets
        2. Caching Tutorial - Lifetimes
      2. Object Validation
        1. xPDOForeignKeyConstraint
        2. xPDOMaxLengthValidationRule
        3. xPDOMaxValueValidationRule
        4. xPDOMinLengthValidationRule
        5. xPDOMinValueValidationRule
        6. xPDOObjectExistsValidationRule
        7. A New Page
    5. xPDO Development
      1. Testing
      2. Building
      3. Documentation and Tutorials
    6. Class Reference
      1. xPDO
        1. xPDO.addPackage
        2. xPDO.beginTransaction
        3. xPDO.commit
        4. xPDO.connect
        5. xPDO.fromJSON
        6. xPDO.getCollection
        7. xPDO.getCollectionGraph
        8. xPDO.getCount
        9. xPDO.getDebug
        10. xPDO.getFields
        11. xPDO.getIterator
        12. xPDO.getManager
        13. xPDO.getObject
        14. xPDO.getObjectGraph
        15. xPDO.getOption
        16. xPDO.getTableName
        17. xPDO.loadClass
        18. xPDO.log
        19. xPDO.newObject
        20. xPDO.newQuery
        21. xPDO.query
        22. xPDO.setDebug
        23. xPDO.setLogLevel
        24. xPDO.setLogTarget
        25. xPDO.setOption
        26. xPDO.toJSON
      2. xPDOCacheManager
        1. xPDOCacheManager.copyFile
        2. xPDOCacheManager.copyTree
        3. xPDOCacheManager.delete
        4. xPDOCacheManager.deleteTree
        5. xPDOCacheManager.endsWith
        6. xPDOCacheManager.escapeSingleQuotes
        7. xPDOCacheManager.get
        8. xPDOCacheManager.getCachePath
        9. xPDOCacheManager.getCacheProvider
        10. xPDOCacheManager.matches
        11. xPDOCacheManager.replace
        12. xPDOCacheManager.set
        13. xPDOCacheManager.writeFile
        14. xPDOCacheManager.writeTree
      3. xPDOGenerator
        1. xPDOGenerator.parseSchema
        2. xPDOGenerator.writeSchema
      4. xPDOManager
        1. xPDOManager.createObjectContainer
        2. xPDOManager.createSourceContainer
        3. xPDOManager.removeObjectContainer
        4. xPDOManager.removeSourceContainer
      5. xPDOObject
        1. Configuration Accessors
          1. getOption
          2. setOption
        2. Field Accessors
          1. fromArray
          2. fromJSON
          3. get
          4. set
          5. toArray
          6. toJSON
        3. Metadata Accessors
          1. getFieldName
          2. getFKClass
          3. getFKDefinition
          4. getPK
          5. getPKType
          6. getSelectColumns
        4. Persistence Methods
          1. remove
          2. save
        5. Related Object Accessors
          1. addMany
          2. addOne
          3. getMany
          4. getOne
        6. State Accessors
          1. isDirty
          2. isLazy
          3. isNew
        7. Static Object Loaders
          1. _loadCollectionInstance
          2. _loadInstance
          3. _loadRows
          4. load
          5. loadCollection
          6. loadCollectionGraph
          7. Using Custom Loader Classes
        8. Validation
          1. addValidationRule
          2. getValidator
          3. isValidated
          4. removeValidationRules
          5. validate
      6. xPDOQuery
        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
      7. xPDORevisionControl
      8. xPDOTransport
        1. xPDOTransport.install
        2. xPDOTransport.pack
        3. xPDOTransport.uninstall
      9. xPDOValidator
        1. xPDOValidator.addMessage
        2. xPDOValidator.getMessages
        3. xPDOValidator.hasMessages
        4. xPDOValidator.validate

xPDOQuery

Last edited by Everett Griffiths on Nov 10, 2013.

Welcome to the MODX Documentation. It is an ongoing effort of the MODX community. If you would like to participate or if you notice any errors or missing content, please let us know.

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