SQL: Days since last collection

[jyamada]

My SQL is very rusty so I don't remember how to do something basic. Trying to track and update the average gallons collected by each account, and days since most recent collection.

My first instinct was two sql calls, get all counts, get all collections, do the math and update the accounts. But that's n^2 and I feel like there's query syntax that can do that for me.

Also I am doing this stat update inside a dashlet report, which works tolerably enough, but wouldn't update on save of a new collection. Had the hack idea of making a custom formula that called the update function before save, but that seems not smart.

[jyamada]

rtfm remembered how sql works, but main issue still stands. This can update one account when the collection is saved, but should the dashlet run all of them? Do I make a mass update button somehow?

Current code that runs before collection save

    public function updater(string $account):stdClass
    {
        $queryBuilder = $this->selectBuilderFactory
            ->create()
            ->from(CustomCollection::ENTITY_TYPE)
            ->withStrictAccessControl()
            ->buildQueryBuilder();

        $whereClause[] = [
            'accountId=' => $account,
        ];

        $queryBuilder
            ->select([
                ['SUM:gallonsCollected', 'sumGallonsCollected'],
                ['AVG:gallonsCollected', 'avgGallonsCollected'],
                ['MAX:collectionDate', 'maxCollectionDate'],
            ])
            ->where($whereClause);

        $this->util->handleDistinctReportQueryBuilder($queryBuilder, $whereClause);

        $sth = $this->entityManager
            ->getQueryExecutor()
            ->execute($queryBuilder->build());

        $rowList = $sth->fetchAll() ?: [];
        
        $entity = $this->entityManager->getRDBRepositoryByClass(Account::class)->getById($account);
        $entity->set(array(
        'cLifetimeGallons' => $rowList[0]['sumGallonsCollected'],
        'cAverageGallons' => $rowList[0]['avgGallonsCollected'],
        'cLastCollectionDate' => $rowList[0]['maxCollectionDate'],
        ));
        $this->entityManager->saveEntity($entity, [SaveOption::SILENT => true]);

    } 

Which does work, but using the formula to make php calls seems wrong.