App\Services\Releases\ReleaseBrowseService::getBrowseCount()

Complexity

Conditions	13
Paths	465

Size

Total Lines	98
Code Lines	52

Duplication

Lines	0
Ratio	0 %

Importance

Changes	3
Bugs	0	Features	0

<?php
    public function getBrowseCount(array $cat, int $maxAge = -1, array $excludedCats = [], int|string $groupName = ''): int
    {
        $maxResults = (int) config('nntmux.max_pager_results', 500000);
        $cacheExpiry = (int) config('nntmux.cache_expiry_short', 5);

        // Build a unique cache key for this specific query
        $cacheKey = 'browse_count_'.md5(serialize($cat).$maxAge.serialize($excludedCats).$groupName);

        // Check cache first - use longer cache time for count queries since they're expensive
        $count = Cache::get($cacheKey);
        if ($count !== null) {
            return (int) $count;
        }

        // Build optimized count query - avoid JOINs when possible
        $conditions = ['r.passwordstatus '.$this->showPasswords()];

        // Add category conditions
        $catQuery = Category::getCategorySearch($cat);
        $catQuery = preg_replace('/^(WHERE|AND)\s+/i', '', trim($catQuery));
        if (! empty($catQuery) && $catQuery !== '1=1') {
            $conditions[] = $catQuery;
        }

        if ($maxAge > 0) {
            $conditions[] = 'r.postdate > NOW() - INTERVAL '.$maxAge.' DAY';
        }

        if (! empty($excludedCats)) {
            $conditions[] = 'r.categories_id NOT IN ('.implode(',', array_map('intval', $excludedCats)).')';
        }

        // Only add group filter if specified - this requires a JOIN
        $needsGroupJoin = (int) $groupName !== -1;
        if ($needsGroupJoin) {
            $conditions[] = sprintf('g.name = %s', escapeString($groupName));
        }

        $whereSql = 'WHERE '.implode(' AND ', $conditions);

        try {
            // For queries with maxResults limit, use sample-based counting
            if ($maxResults > 0) {
                // Quick check using a small sample (1000 rows) to determine if we should
                // just return maxResults or do a full count
                $sampleLimit = min(1000, $maxResults);

                if ($needsGroupJoin) {
                    // Need JOIN for group filtering
                    $sampleQuery = sprintf(
                        'SELECT r.id FROM releases r INNER JOIN usenet_groups g ON g.id = r.groups_id %s ORDER BY r.id DESC LIMIT %d',
                        $whereSql,
                        $sampleLimit
                    );
                } else {
                    // No JOIN needed - much faster query on releases table only
                    $sampleQuery = sprintf(
                        'SELECT r.id FROM releases r %s ORDER BY r.id DESC LIMIT %d',
                        $whereSql,
                        $sampleLimit
                    );
                }

                $sampleResult = DB::select($sampleQuery);
                $sampleCount = count($sampleResult);

                // If we got the full sample, assume there are more rows than maxResults
                if ($sampleCount >= $sampleLimit) {
                    Cache::put($cacheKey, $maxResults, now()->addMinutes($cacheExpiry * 2));

                    return $maxResults;
                }

                // Fewer than sample limit - this is the actual count
                $count = $sampleCount;
            } else {
                // No max limit set, need full count
                if ($needsGroupJoin) {
                    $query = sprintf(
                        'SELECT COUNT(r.id) AS count FROM releases r INNER JOIN usenet_groups g ON g.id = r.groups_id %s',
                        $whereSql
                    );
                } else {
                    // No JOIN needed - simple count on releases table
                    $query = sprintf('SELECT COUNT(r.id) AS count FROM releases r %s', $whereSql);
                }
                $result = DB::select($query);
                $count = isset($result[0]) ? (int) $result[0]->count : 0;
            }

            // Cache with longer expiry for count queries
            Cache::put($cacheKey, $count, now()->addMinutes($cacheExpiry * 2));

            return $count;
        } catch (\Exception $e) {
            Log::error('getBrowseCount failed', ['error' => $e->getMessage()]);

            return 0;
        }