There are a variety of ways to retrieve Objects via xPDO. The two basic approaches here allow us to retrieve either a single object or a collection of objects. An Object is a single xPDOObject. It is retrieved via xPDO::getObject(). A Collection is an array of xPDOObjects. They are retrieved via xPDO::getCollection.
getObject takes 3 arguments: $className, $criteria, and $cacheFlag. The first argument is the class name you'd like to retrieve; the second is the criteria by which you'd like to search for it; and the final argument is the caching option for the object.
If an integer value is provided for the $cacheFlag argument, then it specifies the time to live in the object cache; if cacheFlag === false, caching is ignored for the object and if cacheFlag === true, the object will live in the cache indefinitely.
Back to $criteria. This can be one of 3 things:
- A primary key value
- An array of field(s)
- An xPDOCriteria object or derivative.
We'll get back to the third option later. First, an example of the first option:
If an object doesn't exist, it will return 'null'.
You can specify multiple filter criteria inside your 2nd argument:
Or, let's say we wanted to grab the first Box object we find with a width of 150:
If your criteria matches multiple objects, getObject will only return the first one. Which one will be first? It's up to the database and how its natural sort order.
We'll discuss the third option, xPDOCriteria, later in the xPDOQuery and xPDOCriteria sections.
xPDO::getCollection takes the same three arguments as getObject; except the $criteria field must be either an array or xPDOCriteria object.
Let's say we wanted to grab all the Box objects with width of 14:
One of the most powerful parts of xPDO is its ability to create complex queries in a simple fashion using the xPDOQuery wrapper. This class lets you build SQL queries using OOP methods that extend the xPDOCriteria class – you can pass its instance right into getObject or getCollection calls. The newQuery function creates an xPDOQuery object, and takes 3 parameters:
- $class - The class name to create the query for.
- $criteria - This is optional; but you can specify criteria here.
- $cacheFlag - Similar to getObject's cacheFlag, you can specify the caching behavior for this query.
First, let's just show how you might use newQuery to define the criteria we used before: width = 14. We'll just add a sorting option to sort the results.
Once you have your result, you can iterate over the array (see above). You can see the similarity between the defining a query object and passing a simple array to getObject or getCollection. So why use xPDOQuery? It's more flexible. Did you see how we could use it to specify the sorting order?
Next, let's use the query to join on a related table using xPDOQuery.innerJoin. Let's create an example query using xPDOQuery that will grab the first 5 Boxes with width of 5 and an owner of ID 2, sorted by their name. Our "Box" table has a many-to-many relationship with the "BoxOwner" table.
We can join on 3rd table ("Owner") by using another call to xPDOQuery.innerJoin. Let's also grab the 2nd 5 Boxes by specifying an offset – it's a 2nd argument to the limit() function:
You can see that the sortby and where functions can take dot syntax on their parameters; they can prefix their columns with alias – sometimes they have to do this to prevent collisions!
xPDOCriteria is a base SQL criteria class, and can be used to create complex SQL queries that don't necessarily fit into abstraction paradigms. Examples of this could be HAVING clauses, UNIONs, subqueries or other complex and custom queries.
|One downside to using xPDOCriteria is that it's not, by principle, database-agnostic since you're providing it with the SQL. If you plan on using other types of databases in the future, make sure to encapsulate any xPDOCriteria-based queries in Object classes that can be overridden per SQL platform.|
xPDOCriteria uses PDO formatting for it's SQL statements and bindings. An example of using xPDOCriteria to grab a list of Boxes with a width of greater than 4, limiting to only 10:
|A graph extends the idea of an object (or objects in collections). Instead of a simple object, a graph includes references to related objects. Graphs are a useful alternative to JOINs.|
This is the same as getCollectionGraph, but it returns a single object. See getCollectionGraph below for info.
|Aliases in JSON|
Remember that the JSON hash passed to getObjectGraph or getCollectionGraph needs to use aliases, not class names.
You have direct access to all of the fields (table rows) in the Collection Graph comprised in these four tables. The alias is used to create the graph. In this example, the 'Zip' table is the primary table, so we look at that table and we define relationships from the perspective of that primary table.
As with getObject and getCollection, we can supply a $criteria object to getCollectionGraph. Let's add some criteria to our getCollectionGraph() query. In this example, we can search for zipcodes in California (CA)
|Aliases in Criteria|
The table names you specify in your criteria must use the aliases, not the class names (just like the JSON hashes).
Let's show one more example, this time using MODX tables. This is only an example: filtering on Template Variables is a bit dangerous because the values stored in the database are not always the verbatim values you experience in the manager or in your templates. But this example should help demonstrate the usage of aliases and that you must be aware of the relationships between the objects (some related objects are singular, some are arrays).