Product Aggregation from Multiple Suppliers on Website

Product Aggregation from Multiple Suppliers Implementation

Aggregation is not just merging lists. It's creating a single product card based on data from multiple suppliers while maintaining a connection to each source. Goal: the buyer sees one card, but behind it is an up-to-date selection from several offers with different prices, delivery times, and availability.

Difference Between Import and Aggregation

Import—saving "as is." Aggregation—building a storefront over raw data from multiple suppliers.

When aggregating, three tasks need solving:

1. Recognition—understand that SKU-447 at supplier A and ART-10023 at supplier B are the same product
2. Merging—choose whose attributes (name, photo, description) count as primary
3. Price showcase—show the buyer the best offer or give a choice

Data Schema for Aggregation

-- Master card (aggregated)
CREATE TABLE products (
    id              BIGSERIAL PRIMARY KEY,
    master_sku      VARCHAR(255) UNIQUE NOT NULL,
    name            TEXT NOT NULL,        -- from "main" supplier
    description     TEXT,
    attributes      JSONB DEFAULT '{}',
    category_id     INT REFERENCES categories(id),
    created_at      TIMESTAMP DEFAULT NOW(),
    updated_at      TIMESTAMP DEFAULT NOW()
);

-- Supplier offers for master card
CREATE TABLE product_offers (
    id              BIGSERIAL PRIMARY KEY,
    product_id      BIGINT NOT NULL REFERENCES products(id),
    supplier_id     INT NOT NULL REFERENCES suppliers(id),
    supplier_sku    VARCHAR(255) NOT NULL,
    price           NUMERIC(12,2) NOT NULL,
    stock           INT NOT NULL DEFAULT 0,
    lead_time_days  SMALLINT,             -- delivery time
    is_primary      BOOLEAN DEFAULT FALSE, -- content source for card
    last_synced_at  TIMESTAMP,
    UNIQUE(supplier_id, supplier_sku)
);

-- Indexes for fast best offer search
CREATE INDEX idx_offers_product_price ON product_offers(product_id, price)
    WHERE stock > 0;

Best Offer Selection Logic

Best offer is determined by configurable rules. Typical variant: minimum price among suppliers with stock.

class BestOfferResolver
{
    public function resolve(int $productId): ?ProductOffer
    {
        return ProductOffer::where('product_id', $productId)
            ->where('stock', '>', 0)
            ->orderByRaw('
                price * (1 + COALESCE(
                    (SELECT markup FROM suppliers WHERE id = supplier_id), 0
                ) / 100)
            ')
            ->orderBy('lead_time_days')
            ->first();
    }
}

More flexible approach—scoring via weighted criteria:

class WeightedOfferScorer
{
    // Weight settings from store config
    private float $priceWeight      = 0.60;
    private float $stockWeight      = 0.25;
    private float $leadTimeWeight   = 0.15;

    public function score(ProductOffer $offer, array $stats): float
    {
        // Normalization: best gets 1.0
        $priceScore    = $stats['min_price'] / max($offer->price, 0.01);
        $stockScore    = min($offer->stock / 100, 1.0);
        $leadScore     = $stats['max_lead'] > 0
            ? 1 - ($offer->lead_time_days / $stats['max_lead'])
            : 1.0;

        return $this->priceWeight    * $priceScore
             + $this->stockWeight    * $stockScore
             + $this->leadTimeWeight * $leadScore;
    }
}

Aggregate Showcase in API

API response for product card should include aggregated data:

class ProductResource extends JsonResource
{
    public function toArray($request): array
    {
        $bestOffer = $this->bestOffer;

        return [
            'id'           => $this->id,
            'name'         => $this->name,
            'description'  => $this->description,
            'attributes'   => $this->attributes,

            // Aggregated prices
            'price'        => $bestOffer?->price,
            'price_min'    => $this->offers->where('stock', '>', 0)->min('price'),
            'price_max'    => $this->offers->where('stock', '>', 0)->max('price'),
            'in_stock'     => $this->offers->where('stock', '>', 0)->count() > 0,
            'total_stock'  => $this->offers->sum('stock'),

            // List of offers (if store shows them explicitly)
            'offers'       => OfferResource::collection(
                $this->offers->where('stock', '>', 0)->sortBy('price')
            ),
        ];
    }
}

Updating Aggregation on Offer Changes

Aggregated metrics should update with every supplier offer change. Via Observer:

class ProductOfferObserver
{
    public function saved(ProductOffer $offer): void
    {
        // Recalculate aggregates in cache
        Cache::forget("product.{$offer->product_id}.best_offer");
        Cache::forget("product.{$offer->product_id}.price_range");

        // Update denormalized fields in products
        $this->recalculate($offer->product_id);
    }

    private function recalculate(int $productId): void
    {
        $agg = ProductOffer::where('product_id', $productId)
            ->where('stock', '>', 0)
            ->selectRaw('MIN(price) as min_price, MAX(price) as max_price, SUM(stock) as total_stock')
            ->first();

        Product::where('id', $productId)->update([
            'price_min'    => $agg->min_price,
            'price_max'    => $agg->max_price,
            'total_stock'  => $agg->total_stock,
            'updated_at'   => now(),
        ]);
    }
}

Displaying Multiple Offers on a Card

If business logic provides for supplier selection by buyer (like Yandex.Market):

// React component for offers list
const OfferList: React.FC<{ offers: Offer[] }> = ({ offers }) => {
  const sorted = [...offers].sort((a, b) => a.price - b.price);

  return (
    <div className="space-y-2">
      {sorted.map(offer => (
        <div key={offer.id} className="flex items-center justify-between border rounded p-3">
          <div>
            <span className="font-semibold">{formatPrice(offer.price)}</span>
            <span className="text-sm text-gray-500 ml-2">
              {offer.supplier.name}
            </span>
          </div>
          <div className="text-sm text-gray-500">
            {offer.stock > 0
              ? `in stock ${offer.stock} pcs.`
              : 'out of stock'}
            {offer.lead_time_days && ` · delivery ${offer.lead_time_days} days`}
          </div>
          <button
            onClick={() => addToCart(offer)}
            disabled={offer.stock === 0}
            className="btn-primary"
          >
            Buy
          </button>
        </div>
      ))}
    </div>
  );
};

Cache Invalidation and Elasticsearch

With large catalogs (50,000+ products) aggregates are often stored in Elasticsearch—this speeds up filtering by price, availability, supplier. On offer change, document must be reindexed:

ProductOffer::saved(function ($offer) {
    ReindexProductJob::dispatch($offer->product_id);
});

In Elasticsearch mapping, offers are stored as nested objects, allowing filtering by specific combinations of supplier attributes.

Timeline

• Data schema + merge logic + BestOfferResolver: 2 days
• Observer + aggregate denormalization: 1 day
• API resource with offers + frontend component: 1–2 days
• Elasticsearch integration: +2 days
• Configuring weight coefficients via admin panel: +1 day

Basic aggregation without search: 4–5 working days.