Building a Simple Recommendation Engine with Eloquent and Weighted Scoring

"Recommendation engine" tends to summon images of collaborative filtering and ML pipelines, but a large share of real-world "customers who bought this also bought..." features are solved perfectly well with a weighted scoring query against data you already have. This tutorial builds one for a fictional storefront at PHP Architect, recommending related products based on shared category, shared tags, and co-purchase history.

Step 1: Decide what "related" means, numerically

Before writing any code, define the scoring rules. For our storefront:

• Same category: +3 points
• Each shared tag: +1 point
• Purchased together with the current product at least once: +5 points

These weights are a judgment call, not a formula — you tune them by looking at the recommendations they produce and adjusting until they look right. That's normal; this is the part of the "engine" that's genuinely subjective.

Step 2: Model the co-purchase data

Assuming an order_items table linking orders to products, we can find products frequently bought alongside a given product with a self-join:

namespace PhpArchitect\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Product extends Model
{
    public function coPurchasedProductIds(): array
    {
        return DB::table('order_items as oi1')
            ->join('order_items as oi2', 'oi1.order_id', '=', 'oi2.order_id')
            ->where('oi1.product_id', $this->id)
            ->where('oi2.product_id', '!=', $this->id)
            ->select('oi2.product_id', DB::raw('COUNT(*) as times'))
            ->groupBy('oi2.product_id')
            ->pluck('oi2.product_id')
            ->all();
    }
}

Step 3: Build the scoring query

Rather than fetching candidates and scoring them in PHP, push the scoring into SQL with a raw CASE-driven select — it's faster than hydrating every product in the catalog to compute a score in a loop.

public function recommendationsQuery()
{
    $tagIds = $this->tags()->pluck('tags.id');
    $coPurchasedIds = $this->coPurchasedProductIds();

    return Product::query()
        ->where('id', '!=', $this->id)
        ->where('is_published', true)
        ->select('products.*')
        ->selectRaw('
            (CASE WHEN category_id = ? THEN 3 ELSE 0 END) +
            (SELECT COUNT(*) FROM product_tag pt
                WHERE pt.product_id = products.id AND pt.tag_id IN (' . $tagIds->map(fn () => '?')->implode(',') . ')) +
            (CASE WHEN id IN (' . (empty($coPurchasedIds) ? '0' : implode(',', array_fill(0, count($coPurchasedIds), '?'))) . ') THEN 5 ELSE 0 END)
            as score
        ', [
            $this->category_id,
            ...$tagIds->all(),
            ...$coPurchasedIds,
        ])
        ->havingRaw('score > 0')
        ->orderByDesc('score')
        ->limit(6);
}

This is denser than most Eloquent code you'll write day to day, and that's intentional — pushing the arithmetic into the database means the score calculation happens once per candidate row inside the query engine, not once per hydrated model in a PHP loop.

Step 4: Expose it through the model

class Product extends Model
{
    public function recommended()
    {
        return $this->recommendationsQuery()->get();
    }
}

$related = $product->recommended();

Step 5: Cache it — the inputs don't change every request

Scores only change when tags, category, or order history change, none of which happen on every page view. Cache per product and invalidate on the events that actually move the score:

public function recommended()
{
    return cache()->remember(
        "product:{$this->id}:recommendations",
        now()->addHours(6),
        fn () => $this->recommendationsQuery()->get(),
    );
}

Bust it from an Eloquent observer when tags change or a new order is placed:

class ProductObserver
{
    public function saved(Product $product): void
    {
        cache()->forget("product:{$product->id}:recommendations");
    }
}

When you actually need more than this

This approach breaks down once "related" needs to account for things the scoring rules can't express directly — personalization per user, seasonal trends, or genuinely learned associations between products that aren't captured by category and tags. That's the point where a real recommendation system (collaborative filtering, embeddings, a hosted ML service) earns its complexity. For the common case of "show related products on a product page," weighted scoring in SQL gets you there with no new infrastructure and no model to retrain.