Recently, I met a requirement to query withMax the created_at field in relation of an associated table of model model, which only needs date information but not time. That is, to find the date value of the latest record created in the associated table.

My original method was to extract withMax(‘relation’, ‘created_at’) and then use the loop to process relation_created_at_max, which is really not very elegant. I tried using withMax(‘relation’, ‘date(created_at)’), but I couldn’t find date(created_at), so I had to click on the source of 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');
    }
Copy the code

You can see that the withAggregate method is actually called:

/**
     * 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= = =The '*' ? $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;
    }
Copy the code

The source code looks a bit scary, and to be honest I haven’t fully understood it yet, but if I notice a few key lines of code, I can get a rough idea of how it works

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

When the function takes a third argument $function, it uses its own logic to construct a sprintf(‘%s(%s)’,…) If I pass in date(created_at), it will automatically add some qualification to the table, like ‘relation’.’created_at’. It parses ‘relation’.’date(created_at)’, causing a BUG.

To avoid this, consider simply going else without passing in the $function argument, and that’s it

$model->withAggregate('relation'.'max(date(created_at))')
Copy the code

The only drawback is that the field will be named relation_maxDatecreated_at, which is caused by this statement

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

The $alias variable is overwritten as a combination of “$name $function $column”. To solve this problem, we can find the previous line of code

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

When $name contains as, $alias is given an initial value, which is the name immediately following as

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

We have successfully saved the aggregation result into the max_CREATE_date field