User queries to the database in MODx Revolution

This topic will certainly be useful to those who have quite large projects on MODx Revolution, since with the usual business cards there are enough standard methods of work a la $ modx-> getObject () , $ modx-> getCollection () , etc. . And these methods are in essence not just working with a database, but also with MODx objects.

When working with large projects, methods a la $ modx-> getCollection () are not the best solution for us for two reasons:
1. Resource overuse. These methods do not just receive data from the database, but also create instances of the objects received. In this case, getting information about 10,000 documents, we get 10,000 modResource objects, which is not very cool.
2. The task of counting the received records is complicated. In addition to the direct difficulties of counting at the request level, even if you get 10 records of the same document (for example), MODx will return only one modResource object as a result. And although this is often the case for many programmers (they received unique objects and are happy), it does not suit someone, because, again, there is an overrun of resources, and the final result immediately shows that the query is not optimized.
Moreover, when working on large projects, we often need not the objects themselves, but only information (records from the database).

The methods of working with the database described here pose 2 tasks:
1. Give greater flexibility in writing queries to the database.
2. Adhere to standard xPDO methods, that is, avoid pure SQL, because pure SQL is not generally kosher in frameworks for some reason (at least from the point of view of possible migration to another type of database, changing table names, prefixes or something else)
')
So, to the point.

First we need to master an important method.

$modx->newQuery($class); 

To build all queries in MODx you always need at least one base class from which the entire query will dance.

Here is a more detailed example:

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

In this case, $ q is the so-called criteria that we often see in the documentation.
This is almost the same as where , when we pass it as a second parameter, only a more powerful tool, since it has many important methods like Sortby, leftJoin, innerJoin, Limit and others.

Now we just got what we were going to fight with, that is, at the output we got several modResource objects. It is just that from this familiar example it will be easier for us to move further towards our goal.

So, we will alter our request a little.

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

Here we will get pure SQL, which will probably be needed by many people.
In this example, we have seen another important method.

 $q->prepare(); 

It just prepares the final SQL.

Now we can execute this SQL

 $q = $modx->newQuery('modResource'); $q->where(array( 'context_key' => 'web' )); $q->limit(10); //    $q->prepare(); $sql = $q->toSQL(); $query = $modx->prepare($sql); $query->execute(); $result = $query->fetchAll(PDO::FETCH_ASSOC); print_r($result); 

UPD: I will leave this example as a demonstration $ modx-> prepare ($ sql); but right after that, see the fixed one-call example -> prepare ();

 $q = $modx->newQuery('modResource'); $q->where(array( 'context_key' => 'web' )); $q->limit(10); //    $q->prepare(); $q->stmt->execute(); $result = $q->stmt->fetchAll(PDO::FETCH_ASSOC); print_r($result); 

At the output, we just get the data array.
But the columns will have not entirely successful titles a la
[modResource_id] => 0
[modResource_type] => document

To make it clearer, add an explicit SELECT to the query.

 $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 everything is fine with the names of the columns :-)

And now a rather useful and illustrative example: Get 1/10 records with a shift of 1/20 and sort them by ID.

 $q = $modx->newQuery('modResource'); $q->where(array( 'context_key' => 'web' )); $q->select(array( 'modResource.*' )); //     $total = $modx->getCount('modResource', $q); //   1/10     //   1/20 (offset) $q->limit($total / 10, $total / 20); //    ID    $q->sortby('id', 'DESC'); $q->prepare(); $q->stmt->execute(); $result = $q->stmt->fetchAll(PDO::FETCH_ASSOC); print_r($result); 

By the way, it is quite easy to remake these examples so as to get finite objects.

 $q = $modx->newQuery('modResource'); $q->where(array( 'context_key' => 'web' )); $q->select(array( 'modResource.*' )); //     $total = $modx->getCount('modResource', $q); //   1/10     //   1/20 (offset) $q->limit($total / 10, $total / 20); //    ID    $q->sortby('id', 'DESC'); $q->prepare(); //   $docs = $modx->getCollection('modResource', $q); 

The essence of the $ modx-> getObject () and $ modx-> getCollection () methods is to retrieve data from the database, initiate the specified class and stuff the received data into it using the $ object-> fromArray ($ array ()) method;

By the way, I strongly advise you not to play with print_r ($ docs); because the result of the a la $ modx-> getCollection () methods is an array of specified objects, each of which is an extended xPDO and MODx object combined, that is, it is sooo much information.
Therefore, to display information from objects, use the $ object-> toArray () method;
In this case, something like this:

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

Note also: the elements in the array of MODx objects are not listed in order, but each key is an object (record) ID, so you cannot surely access the 11th element through $ docs [10], since a document with ID 10 may appear The first, or 100th, or it may not exist at all, although there will be 100 documents in the array.
See for yourself by redoing the output as

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

To work with arrays of MODx objects, it is very useful to study the methods of working with the elements of arrays described here .

end () - Sets the internal array pointer to its last element.
key () - selects the key from the array
each () - Returns the current key / value pair from an array and shifts its pointer.
prev () - Moves the internal array pointer back one position.
reset () - Sets the internal array pointer to its first element.
next () - Moves the internal array pointer one position forward.

For example, if we want to get the first element from the MODx array, $ doc = $ docs [0] cannot be accessed in any way; In 99.9%, you will not get anything, since records with ID = 0 are almost never used.
Correctly address this: $ doc = current ($ doc);

Those who like this method will probably build in this way more complex queries from several tables at once, etc.

PS A small collective query with a pair of useful filters.
We will get all the settings from the WEB and MGR contexts, the values ​​of which are NOT NOT NULL and! = ''

 $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);