Laravel ORM withAggregate 用法簡析

wjllance發表於2021-02-04
LaravelORM

最近遇到一個需求，要對模型model的某個關聯表relation中的created_at欄位做withMax查詢，只需要日期資訊不需要時間，也就是找出關聯表中建立時間最新的記錄的日期值。

我原本的做法先是用withMax('relation', 'created_at')取出來之後,再用迴圈對relation_created_at_max進行處理，這樣實在是不太優雅，我嘗試使用過withMax('relation', 'date(created_at)'),但結果提示找不到date(created_at)這一列，於是我不得不點進去看了一下withMax的原始碼：

    /**
     * Add subselect queries to include the max of the relation's column.
     *
     * @param  string|array  $relation
     * @param  string  $column
     * @return $this
     */
    public function withMax($relation, $column)
    {
        return $this->withAggregate($relation, $column, 'max');
    }

可以看到這個方法裡面實際上就是呼叫了withAggregate方法：

/**
     * Add subselect queries to include an aggregate value for a relationship.
     *
     * @param  mixed  $relations
     * @param  string  $column
     * @param  string  $function
     * @return $this
     */
    public function withAggregate($relations, $column, $function = null)
    {
        if (empty($relations)) {
            return $this;
        }

        if (is_null($this->query->columns)) {
            $this->query->select([$this->query->from.'.*']);
        }

        $relations = is_array($relations) ? $relations : [$relations];

        foreach ($this->parseWithRelations($relations) as $name => $constraints) {
            // First we will determine if the name has been aliased using an "as" clause on the name
            // and if it has we will extract the actual relationship name and the desired name of
            // the resulting column. This allows multiple aggregates on the same relationships.
            $segments = explode(' ', $name);

            unset($alias);

            if (count($segments) === 3 && Str::lower($segments[1]) === 'as') {
                [$name, $alias] = [$segments[0], $segments[2]];
            }

            $relation = $this->getRelationWithoutConstraints($name);

            if ($function) {
                $hashedColumn = $this->getQuery()->from === $relation->getQuery()->getQuery()->from
                                            ? "{$relation->getRelationCountHash(false)}.$column"
                                            : $column;

                $expression = sprintf('%s(%s)', $function, $this->getQuery()->getGrammar()->wrap(
                    $column === '*' ? $column : $relation->getRelated()->qualifyColumn($hashedColumn)
                ));
            } else {
                $expression = $column;
            }

            // Here, we will grab the relationship sub-query and prepare to add it to the main query
            // as a sub-select. First, we'll get the "has" query and use that to get the relation
            // sub-query. We'll format this relationship name and append this column if needed.
            $query = $relation->getRelationExistenceQuery(
                $relation->getRelated()->newQuery(), $this, new Expression($expression)
            )->setBindings([], 'select');

            $query->callScope($constraints);

            $query = $query->mergeConstraintsFrom($relation->getQuery())->toBase();

            // If the query contains certain elements like orderings / more than one column selected
            // then we will remove those elements from the query so that it will execute properly
            // when given to the database. Otherwise, we may receive SQL errors or poor syntax.
            $query->orders = null;
            $query->setBindings([], 'order');

            if (count($query->columns) > 1) {
                $query->columns = [$query->columns[0]];
                $query->bindings['select'] = [];
            }

            // Finally, we will make the proper column alias to the query and run this sub-select on
            // the query builder. Then, we will return the builder instance back to the developer
            // for further constraint chaining that needs to take place on the query as needed.
            $alias = $alias ?? Str::snake(
                preg_replace('/[^[:alnum:][:space:]_]/u', '', "$name $function $column")
            );

            $this->selectSub(
                $function ? $query : $query->limit(1),
                $alias
            );
        }

        return $this;
    }

這段原始碼看上去有點恐怖，說實話我還沒有完全看明白，但是隻要注意到其中關鍵的幾行程式碼，就可以大致上摸清楚它的用法

if ($function) {
...
}else {
    $expression = $column;
}

當這個函式有第三個引數$function傳入的時候，它會走自己的一套邏輯去構造出一個sprintf('%s(%s)', ...)的一個表示式，在這套邏輯中它可能就自動加上了一些跟關聯表相關的一些限定，類似於'relation'.'created_at'，這樣就導致我在傳入date(created_at)的時候，被它解析成了'relation'.'date(created_at)',從而出現了BUG。

為了避免這種情況，我們考慮直接走else語句，不傳入$function引數，也就是這樣

$model->withAggregate('relation', 'max(date(created_at))')

這樣就可以成功獲取到我們想要的資料，唯一的缺點是這個欄位會被命名為relation_max_date_created_at,導致這個結果的罪魁禍首就是這條語句

$alias = $alias ?? Str::snake(
    preg_replace('/[^[:alnum:][:space:]_]/u', '', "$name $function $column")
);

$alias變數被$name $function $column的組合覆蓋掉了，為了解決這個問題，我們可以找到前面的這一行程式碼

if (count($segments) === 3 && Str::lower($segments[1]) === 'as') {
    [$name, $alias] = [$segments[0], $segments[2]];
}

當傳入的$name裡包含as的時候，$alias會被賦予一個初值，也就是as後面緊接的名字，於是我們可以把上面那條語句修改為

$model->withAggregate('relation as max_create_date', 'max(date(created_at))')

這樣我們就成功地把聚合的結果儲存到了max_create_date欄位當中

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章