withRelationalAttribute.php

/*
  Provides an easy interface for add select statements to Eloquent relationships. 
  This allows you to sort by the fields in the query.

  Usage
  add to AppServiceProvider@boot
  Relationship name and column must be seperated by pipe. 
  Laravel strips out . so it was easier to just use something else
  
  Model::withRelationalAttribute(['relation|column as relation_column'])->get()
  
  Location:withRelationalAttribute([
    'company|name as company_name', 
    'consumers|count(*) as consumers_count' => function ($q) {
        $q->where('status', 'active');    
      } 
  ])
  ->orderBy('company_name')
  ->get();
  
*/

Builder::macro('withRelationalAttribute', function ($relations)
        {
            if (empty($relations)) {
                return $this;
            }
            if (is_null($this->query->columns)) {
                $this->query->select([$this->query->from.'.*']);
            }

            $relations = is_array($relations) ? $relations : func_get_args();
            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 counts on the same relationship name.
                $segments = explode(' ', $name);
                unset($alias);
                if (count($segments) === 3 && Str::lower($segments[1]) === 'as') {
                    [$name, $alias] = [$segments[0], $segments[2]];
                }
                
                if(strpos($name,'|')) {
                    [$name, $column]= explode('|', $name);
                } else {
                    throw new \Exception('Must define column using pipe notation column|name or column|sum(*) '.$name);
                }


                $relation = $this->getRelationWithoutConstraints($name);
                // Here we will get the relationship count 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
                // count query. We will normalize the relation name then append _count as the name.
                $query = $relation->getRelationExistenceQuery(
                    $relation->getRelated()->newQuery(), $this, new Expression($column)
                )->setBindings([], 'select');

                $query->callScope($constraints);
                $query = $query->mergeConstraintsFrom($relation->getQuery())->toBase();
                if (count($query->columns) > 1) {
                    $query->columns = [$query->columns[0]];
                }
                // Finally we will add the proper result column alias to the query and run the subselect
                // statement against the query builder. Then we will return the builder instance back
                // to the developer for further constraint chaining that needs to take place on it.
                $column = $alias ?? Str::snake($name.'_count');
                $this->selectSub($query, $column);
            }

            return $this;
        });