A client came with a complaint: the product page loaded in 4 seconds due to N+1 queries caused by review output. The average rating wasn't updated for days—cache wasn't invalidated. The review system was slowing down the catalog. We rewrote the module in 5 days: denormalized the rating, added caching, implemented automatic moderation. Conversion increased by 22%.
On one project, we found 47 copies of the query SELECT * FROM reviews WHERE product_id = ? on the catalog page. After denormalizing the rating and aggregating via Redis, load time dropped from 4.2s to 0.3s. LCP improved by 60%.
Why Implement a Review System?
Reviews are social proof that influence conversion more than product descriptions. According to Wikipedia, social proof significantly influences consumer behavior. The average rating and review count appear in Google snippets via structured data—giving a search advantage. An off-the-shelf review system saves 2–3 weeks of development. With over 10 years of experience and more than 50 successful eCommerce projects, we ensure high-quality review system development. We've completed over 50 eCommerce projects, so we know the typical issues: N+1 queries, incorrect rating denormalization, and photo upload duplicates. Our solution includes query optimization, caching, and secure media upload.
How We Eliminate N+1 Queries
We denormalize the rating: add rating_avg and rating_count fields to the products table. When a review is approved, we call recalculateRating(), which updates aggregates in a single query in under 10ms. We cache the result for an hour in Redis—the catalog loads without subqueries to reviews.
| Recalculation Strategy | Update Delay | DB Load | Consistency |
|---|---|---|---|
| Immediate (after each review) | Seconds | Medium | Full |
| Scheduled (cron) | Hours | Low | Eventual |
We recommend immediate: the cost is one extra UPDATE per review approval, but the rating is always fresh. Under high load, we switch to a queue via Laravel Horizon.
How Moderation Is Automated
Automatic moderation handles 80% of reviews without human intervention. Our service checks text against stop-words, analyzes rating, and user history. The remaining 20% go to an admin queue with quick actions: approve, reject, or mark as spam. This is 3x faster than manual review.
How to configure stop-words
Stop-words are defined in config/reviews.php as an array of phrases. By default, we block links and contact details. You can add your own patterns—regular expressions or whole phrases. Moderators can view hits in the admin panel.
Technical Implementation
We use PHP 8.2, Laravel 11, and PostgreSQL 15.
Data Model
CREATE TABLE reviews (
id BIGSERIAL PRIMARY KEY,
product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
order_item_id BIGINT REFERENCES order_items(id),
user_id BIGINT REFERENCES users(id),
guest_name VARCHAR(100),
rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
title VARCHAR(255),
body TEXT,
pros TEXT,
cons TEXT,
status VARCHAR(20) DEFAULT 'pending',
is_verified_purchase BOOLEAN DEFAULT FALSE,
helpful_count INT DEFAULT 0,
not_helpful_count INT DEFAULT 0,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE review_photos (
id BIGSERIAL PRIMARY KEY,
review_id BIGINT REFERENCES reviews(id) ON DELETE CASCADE,
url VARCHAR(500) NOT NULL,
sort_order SMALLINT DEFAULT 0
);
CREATE TABLE review_votes (
review_id BIGINT REFERENCES reviews(id) ON DELETE CASCADE,
user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
vote BOOLEAN NOT NULL,
PRIMARY KEY (review_id, user_id)
);
CREATE TABLE review_replies (
id BIGSERIAL PRIMARY KEY,
review_id BIGINT REFERENCES reviews(id) ON DELETE CASCADE,
user_id BIGINT REFERENCES users(id),
body TEXT NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
We implement three review access models:
| Model | Trust | Number of Reviews | Moderation |
|---|---|---|---|
| Buyers only | High | Low | Manual (minimal) |
| Authenticated users | Medium | Medium | Automatic |
| Guests | Low | High | Manual (all) |
We recommend authenticated users with a 'Verified Purchase' label for those who have a completed order. This balances trust and quantity.
API for Creating Reviews
public function store(Request $request, Product $product): JsonResponse
{
$request->validate([
'rating' => 'required|integer|between:1,5',
'body' => 'required|string|min:20|max:2000',
'title' => 'nullable|string|max:255',
'pros' => 'nullable|string|max:500',
'cons' => 'nullable|string|max:500',
'photos' => 'nullable|array|max:5',
'photos.*' => 'url',
]);
$isVerified = OrderItem::whereHas('order', fn($q) =>
$q->where('user_id', $request->user()->id)->where('status', 'completed')
)->where('product_id', $product->id)->exists();
$review = Review::create([
...$request->validated(),
'product_id' => $product->id,
'user_id' => $request->user()->id,
'is_verified_purchase' => $isVerified,
'status' => $this->needsModeration($request) ? 'pending' : 'approved',
]);
$product->recalculateRating();
return response()->json(new ReviewResource($review), 201);
}
Automatic Moderation
class ReviewModerationService
{
private array $stopWords = ['http', 'www.', 't.me/', 'whatsapp'];
public function needsModeration(string $text, User $user): bool
{
foreach ($this->stopWords as $word) {
if (str_contains(strtolower($text), $word)) return true;
}
return $user->reviews()->where('status', 'approved')->count() === 0;
}
}
Automatic rules: review from verified buyer with rating 4–5 and no stop-words → approved; first review of a new user → pending; text with URL, phone, or stop-words → pending or spam.
Recaculating Product Rating
public function recalculateRating(): void
{
$stats = $this->reviews()
->where('status', 'approved')
->selectRaw('COUNT(*) as count, AVG(rating) as avg, SUM(CASE WHEN rating = 5 THEN 1 ELSE 0 END) as five_star')
->first();
$this->update([
'rating_avg' => round($stats->avg, 2),
'rating_count' => $stats->count,
]);
Cache::forget("product:{$this->id}:rating");
}
The rating is stored denormalized in the products table for fast catalog sorting. After each new review or deletion, recalculateRating is called.
How Reviews Affect SEO
Reviews are exported as JSON-LD for Google. Stars in the snippet appear with at least 1 review. According to our data, this increases CTR by 15–30%. Example markup:
<script type="application/ld+json">
{
"@context": "https://schema.org",
"@type": "Product",
"name": "{{ product.name }}",
"aggregateRating": {
"@type": "AggregateRating",
"ratingValue": "{{ product.rating_avg }}",
"reviewCount": "{{ product.rating_count }}",
"bestRating": 5,
"worstRating": 1
},
"review": [
{% for review in product.topReviews %}
{
"@type": "Review",
"author": {
"@type": "Person",
"name": "{{ review.user_name }}"
},
"reviewRating": {
"@type": "Rating",
"ratingValue": "{{ review.rating }}"
},
"reviewBody": "{{ review.body }}",
"datePublished": "{{ review.created_at }}"
}
{% endfor %}
]
}
</script>
Sorting and Filtering Reviews
On the product page, reviews are sorted by date, rating, and helpfulness. A clickable star filter in the histogram helps customers find relevant reviews and reduces bounce rate.
Store Replies
Managers can reply to reviews from the admin panel with a 'Store reply' label. This increases trust and encourages more reviews.
What's Included
- API documentation and endpoint descriptions
- Administrator training on moderation
- Source code of the review module (Laravel 11, React 18)
- Free support for 30 days after delivery
- 1-year warranty on code
- 10 years of industry experience in eCommerce
Process
- Analysis — study current architecture, load, moderation requirements.
- Design — data model, API contracts, caching scheme.
- Implementation — backend (Laravel 11, PostgreSQL), frontend (React 18, TypeScript).
- Testing — load testing (up to 1000 RPM), moderation verification.
- Deployment — Docker containerization, CI/CD setup, monitoring.
Timeline and Cost
Basic implementation takes 4–7 business days. Timeline increases if integration with existing CRM or custom moderation scenarios are needed. Cost is calculated individually and typically starts from $2,500 for a basic implementation. Our ecommerce review system development includes automatic moderation and product rating system integration. The product rating system is enhanced with Schema.org SEO markup. We specialize in ecommerce review system development with SEO-friendly features. Contact us for a consultation — we'll calculate exact timelines for your project. Order development and get a review system that boosts conversion and improves SEO.