Naidim Naidim - 1 month ago 5
MySQL Question

How can I create a virtual summation field by entity

I have records which have a sq. mileage field. I want to sum these fields by date, so regardless of display page, order, etc. the value by entity will be the same.
e.g.:


|id| date |sqmiles|total|
|--|----------|-------|-----|
|1 |2010-10-10| 2 | 2 |
|2 |2011-11-11| 3 | 5 |
|3 |2012-12-12| 1 | 6 |



|id| date |sqmiles|total|
|--|----------|-------|-----|
|3 |2012-12-12| 1 | 6 |
|1 |2010-10-10| 2 | 2 |
|2 |2011-11-11| 3 | 5 |


My starting thoughts were a virtual field where it requests the following SQL:
SELECT SUM(sq_miles) FROM table WHERE date <= ($this->_properties['date']);
but with CakePHP3's new ORM I don't know how to put that into code.

Answer

This may not be the "best" answer, but I made it work: In the entity add use Cake\ORM\TableRegistry; then the following function:

    protected function _getTotalSqMiles()
    {
        $annexations = TableRegistry::get('Annexations');
        $query = $annexations->find();
        $sum = $query->func()->sum('sq_miles');
        $results = $query->select(['total' => $sum])
            ->where([
                'effective_date <=' => $this->_properties['effective_date']
            ])
            ->first();
        return $results->total;
    }