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. Overriding Derived Behavior
        1. Using Custom Object Loaders
      2. 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
    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.where

Last edited by Susan Ottwell on Oct 9, 2013.

xPDOQuery::where

Add a WHERE condition to the query. In general, the way this works is you append an operator to an attribute after a colon.

array('attribute:operator' => 'value')
Operator Symbol Example
Equals none $query->where(array('width' => 15));
Not Equals != $query->where(array('width:!=' => 15));
Greater Than > $query->where(array('width:>' => 15));
Less Than < $query->where(array('width:<' => 15));
Greater Than or Equal to >= $query->where(array('width:>=' => 15));
Less Than or Equal to <= $query->where(array('width:<=' => 15));
Like LIKE $query->where(array('width:LIKE' => '%15%'));
Not Like NOT LIKE $query->where(array('width:NOT LIKE' => '%15%'));
Exists in IN $query->where(array('width:IN' => array(15,16,17,20)));
Not Exists in NOT IN $query->where(array('width:NOT IN' => array(15,16,17,20)));
Is Null IS $query->where(array('width:IS' => null));

Syntax

API Docs: http://api.modx.com/revolution/2.2/db_core_xpdo_om_xpdoquery.class.html#\xPDOQuery::where()

xPDOQuery where ([mixed $conditions = ''],
 [string $conjunction = xPDOQuery::SQL_AND],
 [mixed $binding = null],
 [integer $condGroup = 0])

Examples

Get all Boxes with width of 15.

$query = $xpdo->newQuery('Box');
$query->where(array(
   'width' => 15,
));
$boxes = $xpdo->getCollection('Box',$query);

Get all boxes with a width of 15 or 10.

$query = $xpdo->newQuery('Box');
$query->where(array('width' => 15));
$query->where(array('width' => 10),xPDOQuery::SQL_OR); // you can use orCondition here as well
$boxes = $xpdo->getCollection('Box',$query);

An alternative method to get boxes with a width of 15 or 10.

$query = $xpdo->newQuery('Box');
$query->where(array(
	array( // two arrays used to contain the OR statement within the listed conditions
   		'width' => 15
	),
	array(
		'width' => 10
	)
),xPDOQuery::SQL_OR); // use one array if no additional where statements are used.
$boxes = $xpdo->getCollection('Box',$query);

Next alternative method to get boxes with a width of 15 or 10.

$query = $xpdo->newQuery('Box');
$query->where(array(
	array(
   		'width' => 15
	),
	array(
		'OR:width:=' => 10
	)
));
$boxes = $xpdo->getCollection('Box',$query);

Grab all boxes with a width greater than or equal to 15, but not with a width of 23.

$query = $xpdo->newQuery('Box');
$query->where(array(
   'width:>=' => 15,
   'width:!=' => 23,
));
$boxes = $xpdo->getCollection('Box',$query);

Get all boxes with a name with the letter 'q' in it:

$query = $xpdo->newQuery('Box');
$query->where(array(
   'name:LIKE' => '%q%',
));
$boxes = $xpdo->getCollection('Box',$query);

Using and & or in the same query to get all boxes with a width of 15 or 10 and a height between 10 and 15.

$query = $xpdo->newQuery('Box');
$query->where(array(
   array(
      'width:=' => 15, // note that adding 'AND:' or 'OR:' in front of the attribute, an operator must be used ':='
      'OR:width:=' => 10
   ),
   array(
      'AND:height:>=' => 10,
      'AND:height:<=' => 15
   )
));
$boxes = $xpdo->getCollection('Box',$query);

More Complex Examples

If you have a more complex model with many joined tables, the where method should refer only to the class alias (not the class name) that contains the attribute. Below is an example of a query passed to the getCollectionGraph method, where you can see that myTable object is joined through to the user profile information.

$query = $modx->newQuery('myTable');
$query->where(array('Profile.fullname:LIKE' => '%Company%'));
$records = $this->ParentCMS->getCollectionGraph('myTable', '{"modUser": {"Profile":{} } }',$query);

Another method is to pass a $criteria array immediately as the 2nd argument to newQuery. Notice how the alias "Resource" is used since that's what is listed as the alias in the schema definition for the modTemplateVarResource object:

$criteria = array();
$criteria['modTemplateVarResource.tmplvarid'] = 9;
$criteria['modTemplateVarResource.value:IN'] = array('Red','Green','Blue');
$criteria['Resource.template'] = 2;
$criteria = $modx->newQuery('modTemplateVarResource', $criteria);
$tvrs = $modx->getCollectionGraph('modTemplateVarResource','{"Resource":{}}', $criteria);

Here are a couple of different examples of doing subqueries:

http://forums.modx.com/index.php?topic=60287.0

https://github.com/netProphET/revolution/commit/464b8ff3d05f7114412ef19c3ec4729fa78ffeba

See Also

Suggest an edit to this page on GitHub (Requires GitHub account. Opens a new window/tab) or become an editor of the MODX Documentation.