Custom database queries in MODx Revolution

This topic will certainly be useful for those who have quite a lot of projects on MODx Revolution, as with the usual sites of business cards is enough and the standard working methods of a La $modx- > getObject(), $modx->getCollection(), etc., and these methods are inherently not just a job database, but also with the objects of MODx.

When working with large projects methods a La $modx- > getCollection() for us is not the best solution for two reasons:
1. The overexpenditures. These methods are not just getting data from DB, but also create instance of the retrieved objects. In this case, getting information about 10000 documents, we get 10000 objects of modResource that it is not very cool.
2. The task is complicated by the count of records. In addition to the direct difficulties of counting even at the query level, even if you get 10 records of the same document (for example), MODx will return as a result only one object modResource. Although often that will suit many programmers (they were the unique objects and are happy), someone is not satisfied, because once again there is a wastage of resources, and the final result immediately and not see that the query is not optimized.
Besides, when working on large projects we often need is not the objects themselves, but only information (records from database).

The methods described here work with databases pose 2 problems:
1. To give greater flexibility in writing queries to the database.
2. Stick with the standard xPDO methods, that is to avoid pure SQL, so clean SQL for some reason in the frameworks generally not kosher (at least from the point of view of possible migration to another database type, change the table names, prefixes, or anything else)

So, to the point.

First we need to master important method
the

$modx- > newQuery($class);

To build all the queries in MODx always need at least one base class, which will dance the whole request.

Here is a more detailed example:
the

$q = $modx- > newQuery('modResource');
$q- > where(array(
'context_key' => 'web'
));
$result = $modx- > getCollection('modResource', $q);

In this case, $q is common to us in the documentation of the so-called criteria.
It's almost the same as where when we pass it as second parameter, only a more powerful tool as it has many important methods of type Sortby, leftJoin, innerJoin, Limit and others.

Now we just got something that was going to fight, that is on the way out we got several objects of modResource. Just from this familiar example will be easier for us to move forward towards our goal.

So, a few will improve our query.
the

$q = $modx- > newQuery('modResource');
$q- > where(array(
'context_key' => 'web'
));
$q->prepare();
$sql = $q- > toSQL();

Here we get a clean SQL that probably many people need.
In this example, we saw another important method
the

$q->prepare();

He prepares the final SQL.

Now we can execute this SQL.
the

$q = $modx- > newQuery('modResource');
$q- > where(array(
'context_key' => 'web'
));

$q->limit(10); // Add the limit records

$q->prepare();
$sql = $q- > toSQL();

$query = $modx->prepare($sql);
$query->execute();

$result = $query->fetchAll(PDO::FETCH_ASSOC);
print_r($result);

UPD: this example will leave as demonstrasi $modx->prepare($sql); but just behind this look corrected example with one call ->prepare();
the

$q = $modx- > newQuery('modResource');
$q- > where(array(
'context_key' => 'web'
));

$q->limit(10); // Add the limit records

$q->prepare();

$q->stmt->execute();

$result = $q->stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($result);

At the output we get an array of data.
But the column will have a not very good name, a La
[modResource_id] => 0
[modResource_type] => document

To make it clearer, let's add an explicit SELECT in the query.
the

$q = $modx- > newQuery('modResource');
$q- > where(array(
'context_key' => 'web'
));
$q- > select(array(
'modResource.*'
));
$q->limit(10); 

$q->prepare();
$q->stmt->execute();

$result = $q->stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($result);

Now all is well with the fieldnames :-)

Now quite a useful and clear example: Get 1/10 of the records with a shift of 1/20 and sort by ID.
$q- > where(array( 'context_key' => 'web' )); $q- > select(array( 'modResource.*' )); // Count total number of records $total = $modx- > getCount('modResource', $q); // Set the limit of 1/10 of the total number of records // shift 1/20 (offset) $q->limit($total / 10, $total / 20); // And sorted by ID in reverse order $q- > sortby('id', 'DESC'); $q->prepare(); $q->stmt->execute(); $result = $q->stmt->fetchAll(PDO::FETCH_ASSOC); print_r($result);

By the way, is quite easy these examples are to alter so as to obtain the targets.

the

$q = $modx- > newQuery('modResource');
$q- > where(array(
'context_key' => 'web'
));
$q- > select(array(
'modResource.*'
));

// Count total number of records
$total = $modx- > getCount('modResource', $q);

// Set the limit of 1/10 of the total number of records
// shift 1/20 (offset)

$q->limit($total / 10, $total / 20); 

// And sorted by ID in reverse order
$q- > sortby('id', 'DESC');

$q->prepare();

// Get objects
$docs = $modx- > getCollection('modResource', $q);

The essence of the methods of $modx->getObject() and $modx->getCollection() is that getting data from a DB to initiate the specified class and fill the received data to the method $object->fromArray($array());

By the way, strongly advise you not to play with print_r($docs); as a result, methods a La $modx- > getCollection() — an array of these objects, each of which is an advanced MODx and xPDO object taken together, that is sooo much information.
Because to display information from object use the method $object->toArray();
In this case, something like this:
the

foreach($docs as $doc){
print_r($doc->toArray());
}

Another note: the elements in the array of objects of MODx are listed in no particular order, and each key — ID of the object (record), because you can't exactly refer to the 11th element of $docs[10], because the document with ID 10 can be 1-th or 100-th, or it may not be, although the array will be 100 documents.
You can see for yourself by altering the output as
the

foreach($docs as $id = > $doc){
print "<br />". $id;
}

To work with arrays of objects of MODx is very useful to explore methods for working with arrays are described here.

end() — Sets the internal pointer of an array to its last element
key() — Fetches a key from an array
each() — Returns the current key/value from an array and advance the pointer
prev() — Moves the internal pointer of the array one position
reset() — Sets the internal pointer of an array to its first element
next() — Moves the internal array pointer one place forward.

For example, if we want to get the first element from an array of MODx, it is impossible to turn $doc = $docs[0]; 99.9% you get nothing, as records with ID = 0 is almost never used.
Correctly to speak so: $doc = current($doc);

Those who like this method, will probably build and thus more complex queries from multiple tables, etc.

PS here's a collective inquiry with a couple of useful filters.
Get all the settings from the WEB and MGR contexts, the value of which IS NOT NULL and != "

the

$q = $modx- > newQuery('modContext');

$where = array(
'modContext.key:in' => array('web', 'mgr'),
'cs.value:!=' => NULL,
'cs.value:!=' => ",
);
$q- > select(array(
'modContext.key',
'cs.key as setting_key',
'cs.value'
));

$q->innerJoin('modContextSetting', 'cs', 'cs.context_key = modContext.key');

$q- > where($where);

$q->prepare();
$q->stmt->execute();

$result = $q->stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($result);

Article based on information from habrahabr.ru