const offset = (page - 1) * limit; let sql = ` SELECT ci.*, c.name as category_name, c.type as category_type, MATCH(ci.title, ci.description) AGAINST(? IN BOOLEAN MODE) as relevance FROM content_items ci JOIN categories c ON ci.category_id = c.id WHERE 1=1 `; const params = [query + '*']; // For boolean full-text search // Add filters if (categoryId) // Include subcategories sql += ` AND ci.category_id IN ( WITH RECURSIVE category_tree AS ( SELECT id FROM categories WHERE id = ? UNION ALL SELECT c.id FROM categories c INNER JOIN category_tree ct ON c.parent_id = ct.id ) SELECT id FROM category_tree )`; params.push(categoryId); if (mediaType) sql += ` AND ci.media_type = ?`; params.push(mediaType); if (minRating > 0) sql += ` AND ci.rating >= ?`; params.push(minRating); if (maxRating < 10) sql += ` AND ci.rating <= ?`; params.push(maxRating); if (startDate) sql += ` AND ci.release_date >= ?`; params.push(startDate); if (endDate) sql += ` AND ci.release_date <= ?`; params.push(endDate); // Add sorting if (sortBy === 'relevance' && query) sql += ` ORDER BY relevance $sortOrder`; else if (sortBy === 'rating') sql += ` ORDER BY ci.rating $sortOrder`; else if (sortBy === 'date') sql += ` ORDER BY ci.release_date $sortOrder`; else if (sortBy === 'title') sql += ` ORDER BY ci.title $sortOrder`; // Add pagination sql += ` LIMIT ? OFFSET ?`; params.push(limit, offset); // Get total count let countSql = ` SELECT COUNT(*) as total FROM content_items ci JOIN categories c ON ci.category_id = c.id WHERE 1=1 `; // Apply same filters to count query (simplified for brevity) const [results, total] = await Promise.all([ db.query(sql, params), db.query(countSql, countParams) ]); return items: results, pagination: page, limit, total: total[0].total, totalPages: Math.ceil(total[0].total / limit) , filters: categoryId, mediaType, minRating, maxRating ;
-- Categories table CREATE TABLE categories ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, type ENUM('entertainment', 'media') DEFAULT 'entertainment', parent_id INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (parent_id) REFERENCES categories(id) ); -- Content items table CREATE TABLE content_items ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, description TEXT, category_id INT NOT NULL, media_type ENUM('movie', 'tv_show', 'music', 'game', 'podcast', 'article', 'video') NOT NULL, release_date DATE, rating DECIMAL(3,1), duration_minutes INT, url VARCHAR(500), thumbnail_url VARCHAR(500), metadata JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (category_id) REFERENCES categories(id), INDEX idx_category_media (category_id, media_type), FULLTEXT INDEX ft_search (title, description) ); Searching for- porn collection in-All Categorie...
const handleRatingChange = (type, value) => setFilters(prev => ( ...prev, minRating: type === 'min' ? value : prev.minRating, maxRating: type === 'max' ? value : prev.maxRating )); ; const offset = (page - 1) * limit; let sql = ` SELECT ci
useEffect(() => performSearch(); , [searchQuery, selectedCategory, selectedMediaType, filters, pagination.page]); OFFSET
.results-grid grid-template-columns: 1fr;
// API Routes const express = require('express'); const router = express.Router(); const searchService = new SearchService();