Filtering by Aggregates

If you specify criteria for a property that uses an aggregate function (eg. SUM, AVG), the criteria will need to be applied to the HAVING part of the SQL. You do not need to specify that this criteria belongs to the HAVING clause, as Objectiphy will take care of that automatically (although you can alternatively specify a having clause yourself, which is described further down). So assuming an entity is set up with the mapping definitions shown in the example on the annotations page, with an aggregate function for both the numberOfPets and totalWeightOfPets properties, we could build a query like this:

$query = QueryBuilder::create()
    ->where('lastName', '=', 'Smith')
    ->and('numberOfPets', '<', 3)
    ->and('totalWeightOfPets', '>', 2000)
    ->buildSelectQuery();

For completeness, we should also mention that a simple criteria array could be passed to a findBy method, which would be functionally identical to the above query (albeit not as easy to read):

$repository->findBy([
    'lastName' => 'Smith', 
    'numberOfPets' => [
        'operator' => '<', 
        'value' => 3
    ],
    'totalWeightOfPets' => [
        'operator' => '>', 
        'value' => 2000
    ]
);

Objectiphy would detect that the above query or criteria array has two properties that require aggregate functions and move those to the HAVING clause, to yield SQL that looks something like this:

SELECT [...] FROM child 
LEFT JOIN pets ON pets.child_id = child.id 
WHERE child.lastName = 'Smith'
GROUP BY child.id 
HAVING COUNT(pets.id) < 3
  AND SUM(pets.weight_in_grams) > 2000;

Note that the values in the query used by Objectiphy would use prepared statements, not the literal values - the values are just shown here for clarity.

In addition, you can use aggregate functions directly in your query, by passing criteria to the having method of the query builder - without having to specify any aggregate function information in the mapping definition. For example, if you have an entity that represents a bank statement, which has a one-to-many relationship for a property called transactions that holds a list of transactions, and each transaction entity had an amount property, you could use this to get all of the statements that have transactions totalling more than £1000:

QB::create()->having('SUM(%transactions.amount%)', '>', 1000);

This will evaluate to something like this in the SQL (the percent sign delimiters indicate that transactions.amount is a property path):

SELECT [...] FROM statement 
LEFT JOIN transaction ON transaction.statement_id = statement.id 
GROUP BY statement.id 
HAVING SUM(transaction.amount) > 1000;