MODx, xPDO, & The WHERE Condition

I have had some fun working out the intricacies of how to use the “where” condition when building a query using MODx’s xPDO query builder. What I have found is that there is no one way to build a query using MODx.


The first method is by far the easiest, and if it is a simple query, it is the shortest method. Using the same premise used in the xpdo documentation at xPDOQuery.where, I will relate these queries to a search for some boxes. Here I will call the getCollection function on the “Boxes” object. I will then create an array for my criteria. In this case, I am looking for boxes 15 wide.


$boxes = $modx->getCollection('Boxes',array('width'=>15));

Because this is an array, I can pass multiple criteria. For example, I need a width of 15 and a length of 20.


$boxes = $modx->getCollection('Boxes',array(
  'width'=>15,
  'length'=>20
));

Another way to write the same query would be to create a new query using the newQuery function. Then passing the same array used in the above example, I can pass those values into the where condition.


$query = $modx->newQuery('Boxes');
$query->where(array(
   'width' => 15,
   'length' => 20,
));
$boxes = $modx->getCollection('Boxes',$query);

What I really like about this method is the prepare and toSql functions. Inserting that combination before the getCollection function will insert the sql query into the error log using the log function. This allows us to see what query is being generated for troubleshooting.


$query = $modx->newQuery('Boxes');
$query->where(array(
   'width' => 15,
   'length' => 20,
));
$query->prepare();
$modx->log(modX::LOG_LEVEL_ERROR, $query->toSql());
$boxes = $modx->getCollection('Boxes',$query);

Now what if we are passing a variable that may or may not have a value. Simple enough, use two where clauses. The where does not overwrite each other and is a useful method for simple combinations.


$length = isset($_POST['length'])?$_POST['length']:null;
$query = $modx->newQuery('Boxes');
$query->where(array('width' => 15));
if($length){
  $query->where(array('length' => $length));
}
$boxes = $modx->getCollection('Boxes',$query);

Yet another way to build the same query would be to use the andCondition or the orCondition functions. Note that a where condition must come first or you may not get the results you want.


$length = isset($_POST['length'])?$_POST['length']:null;
$query = $modx->newQuery('Boxes');
$query->where(array('width' => 15));
if($length){
  $query->andCondition(array('length' => $length));
}
$boxes = $modx->getCollection('Boxes',$query);

Now that we’re through the basics, let’s take a look at a more complex query. Now I’m looking for box entries created on or edited in the last three days using a the timestamp fields. In this case I use the “xPDOQuery::SQL_OR” as the conjunction (“xPDOQuery::SQL_AND” is the default value).


$runInterval = isset($_POST['interval'])?$_POST['interval']:'3 DAY';
$query = $modx->newQuery('Boxes');
$query->where(array(
   'width' => 15,
   'length' => 20,
));
if($runInterval>''){
  $query->where(array(
    array(
      'createdon >= TIMESTAMP(DATE_SUB(NOW(), INTERVAL '.$runInterval.'))',
      'editedon >= TIMESTAMP(DATE_SUB(NOW(), INTERVAL '.$runInterval.'))',
    )
  ),xPDOQuery::SQL_OR);
}
$boxes = $modx->getCollection('Boxes',$query);

In the sql statement, the where clause would look like:


WHERE width = 15 
AND length = 20 
AND ( createdon >= TIMESTAMP(DATE_SUB(NOW(), INTERVAL 3 DAY)) 
OR editedon >= TIMESTAMP(DATE_SUB(NOW(), INTERVAL 3 DAY)) )

Notice that I have the second where function array contained inside another array. This contains the “OR” to the interior array. If the second where had been written like:


  $query->where(array(
    'createdon >= TIMESTAMP(DATE_SUB(NOW(), INTERVAL '.$runInterval.'))',
    'editedon >= TIMESTAMP(DATE_SUB(NOW(), INTERVAL '.$runInterval.'))',
  ),xPDOQuery::SQL_OR);

In the sql statement, the where clause would look like:


WHERE width = 15 
AND length = 20 
OR createdon >= TIMESTAMP(DATE_SUB(NOW(), INTERVAL 3 DAY)) 
OR editedon >= TIMESTAMP(DATE_SUB(NOW(), INTERVAL 3 DAY))

For more complex queries prefixes for joining the criteria can be used. For this method, an operator must be used. In this example we’re search for boxes that are 15 or 18 wide and have a length between 18 and 22 or a height between 18 and 22. However, this function will not work because the ':=' operator was not placed after the width.


$query = $modx->newQuery('Boxes');
$query->where(array(
   array(
      'width' => 15,
      'OR:width' => 18
   ),
   array(
	 array(
		'AND:length:>=' => 18,
		'AND:length:<=' => 22
	 ),
	 array(
		'OR:height:>=' => 18,
		'AND:height:<=' => 22
	 )
   )
));
$boxes = $modx->getCollection('Boxes',$query);

The below function works because the ':=' was added.


$query = $modx->newQuery('Boxes');
$query->where(array(
   array(
      'width:=' => 15,
      'OR:width:=' => 18
   ),
   array(
	 array(
		'AND:length:>=' => 18,
		'AND:length:<=' => 22
	 ),
	 array(
		'OR:height:>=' => 18,
		'AND:height:<=' => 22
	 )
   )
));
$boxes = $modx->getCollection('Boxes',$query);

After ten examples of how to write a query in MODx xPDO, we have only scratched the surface.


Comments (0)



This thread has been closed from taking new comments.