9.9. Queries and Reporting

You can query your data store to retrieve a specific set of entities or sets of aggregated data without knowing anything about SQL, LDAP filters, XQuery, or whatever else your data store uses to filter things stored within it. When creating these query objects, you can use either a simple but robust API or a SQL-like syntax.

9.9.1. Xyster_Orm_Query

The query object provides a way to precisely return search results for entities. You can specify Criteria, Sorts, a maximum number of entities to return (limit), and a number to skip (offset). When executed, a Xyster_Orm_Query will return a Xyster_Orm_Set of the appropriate type.

First is an example of using the query API.

$query = $orm->query('Person');
$query->where(Xyster_Data_Expression::eq('affiliation','Rebel'))    // it's the rebels, sir.
        ->order(Xyster_Data_Sort::asc('name'))                      // sort by name
        ->limit(3);                           // only 3 of them
$people = $query->execute();

Pretty easy. Here's the "XSQL" (Xyster SQL) syntax.

$query = $orm->query('Person', 'where affiliation = "Rebel" order by name asc limit 3');
$people = $query->execute();

The XSQL syntax for a normal query follows the following format.

WHERE criteria[ ORDER BY sort[, sort...][ LIMIT number[ OFFSET number]]]

Criteria look much like SQL criteria except using double-quotes instead of single quotes for string literals. All column names should be using the entity's camel case-style properties, not the data store field names. Aggregate functions cannot be used in Criteria or Sorts for a normal query.

One interesting feature is that you can use field names that deal with linked entities, as detailed in the following example.

// only return people with a nemesis
$query = $orm->query('Person',
        'where nemesis->name is not null order by name asc limit 3');
$people = $query->execute;

Columns can even be method calls with parameters. (Example: nemesis->callMethod()->anotherMethod("string", 1.999, anotherEntityField) ).

9.9.2. Xyster_Orm_Query_Report

The report query is like the query, but you can specify which columns to return and what their names should be, how to group them, apply any group criteria, and specify distinct values: it's just like a full-blown SQL query.

When a report query is executed, it returns a Xyster_Data_Set of the results. Let's try a query for affiliations and the number each one has.

$report = $orm->reportQuery('Person');
$count = Xyster_Data_Field::count('personId', 'people'); // aliased field
        ->where($count->lte(5))         // any affiliation with < 5 people
$affiliationCounts = $report->execute();

Here's the same query in XSQL syntax.

$report = $orm->reportQuery('Person', 'SELECT affiliation, count(personId) as people'.
        'GrouP By affiliation HAViNG count(personId) < 5 '.
        'ORDER BY count(personId) desc');
$affiliationCounts = $report->execute();

The XSQL syntax for a report query follows the following format.

SELECT field[, field...][ WHERE criteria][ GROUP BY field[, field...][ HAVING criteria]][ ORDER BY sort[, sort...]][ LIMIT number[ OFFSET number]]