v1.0.0.1

StocketBase

← Changes All versions

172 lines · 6.2 KB

<?php
declare(strict_types=1);
require_once __DIR__ . '/database.php';
require_once __DIR__ . '/pagination.php';
final class ProductSearch
{
public const SORT_OPTIONS = ['newest', 'oldest', 'price_asc', 'price_desc', 'rating'];
public static function sanitizeSort(string $sort): string
{
return in_array($sort, self::SORT_OPTIONS, true) ? $sort : 'newest';
}
public static function run(array $settings, array $filters, int $page, int $perPageDefault): array
{
$db = Database::site();
$query = trim((string) ($filters['q'] ?? ''));
$categoryId = (int) ($filters['category'] ?? 0);
$tagId = (int) ($filters['tag'] ?? 0);
$sort = self::sanitizeSort((string) ($filters['sort'] ?? 'newest'));
$conditions = ["p.status = 'published'"];
$bindings = [];
if ($query !== '') {
$matchTitle = ($settings['search_match_title'] ?? '1') === '1';
$matchExcerpt = ($settings['search_match_excerpt'] ?? '0') === '1';
$matchContent = ($settings['search_match_content'] ?? '0') === '1';
if (!$matchTitle && !$matchExcerpt && !$matchContent) {
$matchTitle = true;
}
$matchConds = [];
$likeValue = '%' . str_replace(['%', '_'], ['\\%', '\\_'], $query) . '%';
if ($matchTitle) {
$matchConds[] = 'p.title LIKE :q_title';
$bindings['q_title'] = $likeValue;
}
if ($matchExcerpt) {
$matchConds[] = 'p.excerpt LIKE :q_excerpt';
$bindings['q_excerpt'] = $likeValue;
}
if ($matchContent) {
$matchConds[] = 'p.content_blocks LIKE :q_content';
$bindings['q_content'] = $likeValue;
}
$conditions[] = '(' . implode(' OR ', $matchConds) . ')';
}
if ($categoryId > 0) {
$conditions[] = 'p.category_id = :category_id';
$bindings['category_id'] = $categoryId;
}
$joinSql = '';
if ($tagId > 0) {
$joinSql = ' JOIN product_tags pt ON pt.product_id = p.id';
$conditions[] = 'pt.tag_id = :tag_id';
$bindings['tag_id'] = $tagId;
}
$whereSql = implode(' AND ', $conditions);
$countStatement = $db->prepare("SELECT COUNT(*) FROM products p{$joinSql} WHERE {$whereSql}");
$countStatement->execute($bindings);
$total = (int) $countStatement->fetchColumn();
$isStatic = ($settings['product_loading_mode'] ?? 'scroll') === 'static';
$perPage = $isStatic ? max(1, $total) : max(1, $perPageDefault);
$page = $isStatic ? 1 : max(1, $page);
if ($sort === 'rating') {
return self::runRatingSort($db, $joinSql, $whereSql, $bindings, $total, $perPage, $page);
}
$orderSql = match ($sort) {
'oldest' => 'p.published_at ASC',
'price_asc' => 'p.price_cents ASC',
'price_desc' => 'p.price_cents DESC',
default => 'p.published_at DESC',
};
$totalPages = Pagination::totalPages($total, $perPage);
$offset = Pagination::offset($page, $perPage);
$statement = $db->prepare(
"SELECT p.*, c.name AS category_name, c.slug AS category_slug
FROM products p{$joinSql}
LEFT JOIN categories c ON c.id = p.category_id
WHERE {$whereSql}
ORDER BY {$orderSql}
LIMIT :limit OFFSET :offset"
);
foreach ($bindings as $key => $value) {
$statement->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
}
$statement->bindValue('limit', $perPage, PDO::PARAM_INT);
$statement->bindValue('offset', $offset, PDO::PARAM_INT);
$statement->execute();
return [
'products' => $statement->fetchAll(),
'total' => $total,
'totalPages' => $totalPages,
'page' => $page,
'perPage' => $perPage,
];
}
private static function runRatingSort(PDO $db, string $joinSql, string $whereSql, array $bindings, int $total, int $perPage, int $page): array
{
$statement = $db->prepare(
"SELECT p.*, c.name AS category_name, c.slug AS category_slug
FROM products p{$joinSql}
LEFT JOIN categories c ON c.id = p.category_id
WHERE {$whereSql}
ORDER BY p.published_at DESC"
);
foreach ($bindings as $key => $value) {
$statement->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
}
$statement->execute();
$allProducts = $statement->fetchAll();
$ratings = self::ratingsFor(array_column($allProducts, 'id'));
foreach ($allProducts as &$product) {
$product['_avg_rating'] = $ratings[(int) $product['id']] ?? 0.0;
}
unset($product);
usort($allProducts, static fn(array $a, array $b): int => $b['_avg_rating'] <=> $a['_avg_rating']);
$totalPages = Pagination::totalPages($total, $perPage);
$offset = Pagination::offset($page, $perPage);
return [
'products' => array_slice($allProducts, $offset, $perPage),
'total' => $total,
'totalPages' => $totalPages,
'page' => $page,
'perPage' => $perPage,
];
}
private static function ratingsFor(array $productIds): array
{
$productIds = array_values(array_unique(array_map('intval', $productIds)));
if (empty($productIds)) {
return [];
}
$placeholders = implode(',', array_fill(0, count($productIds), '?'));
$statement = Database::users()->prepare(
"SELECT product_id, AVG(rating) AS avg_rating FROM product_reviews
WHERE status = 'visible' AND rating IS NOT NULL AND product_id IN ({$placeholders})
GROUP BY product_id"
);
$statement->execute($productIds);
$ratings = [];
foreach ($statement->fetchAll() as $row) {
$ratings[(int) $row['product_id']] = (float) $row['avg_rating'];
}
return $ratings;
}
}