Complex custom relationship example in Laravel

ActiveSpecialsRelation.php

<?php

namespace App\Relations;

use App\Product;
use App\Special;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Relations\Relation;

class ActiveSpecialsRelation extends Relation
{
    public function __construct(Product $parent)
    {
        parent::__construct(Special::query(), $parent);
    }

    /**
     * Set the base constraints on the relation query.
     *
     * @return void
     */
    public function addConstraints()
    {
        $this->query
            ->select('specials.*')
            ->active();

        $this->query = $this->applicableSpecials($this->query);
    }

    /**
     * Set the constraints for an eager load of the relation.
     *
     * @param array $models
     *
     * @return void
     */
    public function addEagerConstraints(array $products)
    {
        $this->query->where(function ($query) use ($products) {
            // eager load for web categories
            $query->orWhereIn(
                'product_web_category.product_id',
                collect($products)->pluck('id')
            );

            // eager load for specials
            $query->orWhereIn(
                'product_special.product_id',
                collect($products)->pluck('id')
            );

            // eager load for brands
            $query->orWhereIn(
                'product_id__brand',
                collect($products)->pluck('id')
            );

            // eager load for EVERYTHING
            $query->orWhere('specials.applies_to', 'everything');
        });
    }

    /**
     * Initialize the relation on a set of models.
     *
     * @param array $models
     * @param string $relation
     *
     * @return array
     */
    public function initRelation(array $products, $relation)
    {
        foreach ($products as $product) {
            $product->setRelation(
                $relation,
                $this->related->newCollection()
            );
        }

        return $products;
    }

    /**
     * Match the eagerly loaded results to their parents.
     *
     * @param array $models
     * @param \Illuminate\Database\Eloquent\Collection $results
     * @param string $relation
     *
     * @return array
     */
    public function match(array $products, Collection $specials, $relation)
    {
        if ($specials->isEmpty()) {
            return $products;
        }

        foreach ($products as $product) {
            $product->setRelation(
                $relation,
                $specials->filter(function (Special $special) use ($product) {
                    return $special->product_id__web_category == $product->id || // web category based specials
                           $special->product_id__special == $product->id || // selected product specials
                           $special->product_id__brand == $product->id || // brand based specials
                           $special->applies_to == 'everything'; // everything specials
                })
            );
        }

        return $products;
    }

    /**
     * Get the results of the relationship.
     *
     * @return mixed
     */
    public function getResults()
    {
        return $this->query->get();
    }

    /**
     * Add the constraints for an internal relationship existence query.
     *
     * Essentially, these queries compare on column names like whereColumn.
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $query
     * @param  \Illuminate\Database\Eloquent\Builder  $parentQuery
     * @param  array|mixed  $columns
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function getRelationExistenceQuery(Builder $query, Builder $parentQuery, $columns = ['*'])
    {
        return $this->applicableSpecials($query);
    }

    private function applicableSpecials($query)
    {
        // join for specific products
        $query->leftJoin('product_special', function ($join) {
            $join->on('product_special.special_id', '=', 'specials.id')
                 ->where('specials.applies_to', 'products');
        })
        ->addSelect('product_special.product_id as product_id__special');

        // join for web categories
        $query->leftJoin('product_web_category', function ($join) {
            $join->on('product_web_category.web_category_id', '=', 'specials.web_category_id')
                 ->where('specials.applies_to', 'web_category');
        })
        ->addSelect('product_web_category.product_id as product_id__web_category');

        // join for brands
        $query->leftJoin(DB::raw('(SELECT products.id as product_id__brand, products.brandId, brands.id as brand_id FROM products JOIN brands ON products.brandId = brands.brandId) as `product_brand`'), function ($join) {
            $join->on('product_brand.brand_id', '=', 'specials.brand_id')
                ->where('specials.applies_to', 'brand');
        })
        ->addSelect('product_brand.product_id__brand');

        $query->where(function ($query) {
            if ($this->parent->id !== null) {
                $query->orWhere('product_special.product_id', '=', $this->parent->id)
                    ->orWhere('product_web_category.product_id', '=', $this->parent->id)
                    ->orWhere('product_brand.product_id__brand', '=', $this->parent->id)
                    ->orWhere('specials.applies_to', 'everything');
            }
        });

        return $query;
    }
}