Eager-load Same Relationship, Different Constraints

Question

In Eloquent, I'm trying to lazy-eager-load the same relationship twice, with different constraints. The goal here is to learn two things about employee timesheets. One is all the hours they worked in the last year. The other is the first date they worked.

The first relation constraint is this:

$employeeTimeSheets = app(Timesheet::class)
    ->with([
        'punches' => function (Relation $query) {
            $query->where('punch_date', '>=', Carbon::now()->subYear());
        }
    ])

The second is:

$employeeTimeSheets = app(Timesheet::class)
    ->with([
        'punches' => function (Relation $query) {
            $query
                ->whereNotNull('punch_date')
                ->orderBy('punch_date')
                ->limit(1);
        }
    ])

The problem is of course 'punches' is only allowed once. And there's going to be a few thousand employees being pulled here, so it's kind of important to me to be able to eager-load this data for the sake of performance.

These are kind of arbitrary conditions, just once place in the whole system where they come up. So I'm not sure it warrants adding an entirely new relationship method to the Timesheet model. And I'd also rather not pull all the punches for each employee just to extract the last year and the minimum after the fact, because that would be a mountain of data.

I'm not sure this would be possible to do this without overriding how Eloquent handles relationships to add support for an as keyword in the name. But what I'm really going for is something that feels like this:

$employeeTimeSheets = app(Timesheet::class)
    ->with([
        'punches as last_year' => function (Relation $query) {
            $query->where('punch_date', '>=', Carbon::now()->subYear());
        },
        'punches as first_punch' => function (Relation $query) {
            $query
                ->whereNotNull('punch_date')
                ->orderBy('punch_date')
                ->limit(1);
        }
    ])

Does anyone have a better way?

Answer 1

Figured it out. An override of some of the methods in the Model class to parse for the as keyword did the trick. I stuffed all this into a trait, but it could just as easily be moved to a base class extended by all models, and itself extends Model:

/**
 * @uses \Illuminate\Database\Eloquent\Model
 * @uses \Illuminate\Database\Eloquent\Concerns\HasAttributes
 * @uses \Illuminate\Database\Eloquent\Concerns\HasRelationships
 *
 * Trait RelationAlias
 */
trait RelationAlias
{
    protected $validOperators = [
        'as'
    ];

    /**
     * @param string $method
     * @param array $parameters
     * @return mixed
     */
    public function __call($method, $parameters)
    {
        if ($key = $this->parseKey($method)) {
            $method = $key['concrete'];
            if (method_exists($this, $method)) {
                return $this->$method(...$parameters);
            }
        }

        return parent::__call($method, $parameters);
    }

    /**
     * @return array
     */
    protected function getArrayableRelations()
    {
        $arrayableRelations = parent::getArrayableRelations();
        foreach ($arrayableRelations as $key => $value) {
            if ($aliased = $this->parseKey($key)) {
                $arrayableRelations[$aliased['alias']] = $value;
                unset($arrayableRelations[$key]);
            }
        }

        return $arrayableRelations;
    }

    /**
     * @param $key
     * @return mixed
     */
    public function getRelationValue($key)
    {
        if ($found = parent::getRelationValue($key)) {
            return $found;
        }

        $relations = array_keys($this->relations);
        foreach ($relations as $relation) {
            $aliased = $this->parseKey($relation);
            if ($aliased && $aliased['alias'] == $key) {
                if ($this->relationLoaded($relation)) {
                    return $this->relations[$relation];
                }

                if (method_exists($this, $aliased['concrete'])) {
                    return $this->getRelationshipFromMethod($key);
                }
            }
        }
    }

    /**
     * @param $key
     * @return array|null
     */
    protected function parseKey($key)
    {
        $concrete = $operator = $alias = null;
        foreach ($this->validOperators as $operator) {
            if (preg_match("/.+ $operator .+/i", $key)) {
                list($concrete, $operator, $alias) = explode(' ', $key);
                break;
            }
        }

        return $alias ? compact('concrete', 'operator', 'alias') : null;
    }
}