11342

Committed 08 May 2026 02:47PM UTC coverage: 68.97% (-3.8%) from 72.761%

Build # 11342

Build Type

push

circleci

Committed by

web-flow

Commit Message

Merge pull request #403 from Freegle/feature/exports-migration

feat(batch): migrate exports.php to users:process-exports

Coverage Stats

9127 of 10554 branches covered (86.48%)

Branch coverage included in aggregate %.

400 of 452 new or added lines in 2 files covered. (88.5%)

12172 existing lines in 167 files now uncovered.

100855 of 148909 relevant lines covered (67.73%)

19.62 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

96.08

/iznik-batch/app/Services/MessageSearchService.php

<?php

namespace App\Services;

use App\Models\MessageGroup;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class MessageSearchService
{
    // Common words excluded from the search index (matches V1 Search::$common).
    private array $common = [
        'the', 'old', 'new', 'please', 'thanks', 'with', 'offer', 'taken', 'wanted', 'received',
        'attachment', 'offered', 'and', 'freegle', 'freecycle', 'for', 'large', 'small', 'are',
        'but', 'not', 'you', 'all', 'any', 'can', 'her', 'was', 'one', 'our', 'out', 'day', 'get',
        'has', 'him', 'how', 'now', 'see', 'two', 'who', 'did', 'its', 'let', 'she', 'too', 'use',
        'plz', 'of', 'to', 'in', 'it', 'is', 'be', 'as', 'at', 'so', 'we', 'he', 'by', 'or', 'on',
        'do', 'if', 'me', 'my', 'up', 'an', 'go', 'no', 'us', 'am', 'working', 'broken', 'black',
        'white', 'grey', 'blue', 'green', 'red', 'yellow', 'brown', 'orange', 'pink', 'machine',
        'size', 'set', 'various', 'assorted', 'different', 'bits', 'ladies', 'gents', 'kids', 'nice',
        'brand', 'pack', 'soft', 'single', 'double', 'top', 'plastic', 'electric', 'unopened',
    ];

    /**
     * Remove search index entries for messages older than 30 days.
     * Mirrors V1 cron/message_deindex.php.
     *
     * @return int Number of messages deindexed.
     */
    public function deindexOldMessages(bool $dryRun = false): int
    {
        $date = now()->subDays(30)->format('Y-m-d');

        $msgids = DB::table('messages_groups')
            ->join('messages_index', 'messages_index.msgid', '=', 'messages_groups.msgid')
            ->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)
            ->where('messages_groups.arrival', '<', $date)
            ->distinct()
            ->pluck('messages_groups.msgid');

        $total = $msgids->count();
        Log::info("MessageSearch: " . ($dryRun ? "would deindex " : "deindexing ") . "{$total} messages");

        if ($dryRun) {
            return $total;
        }

        $done = 0;

        foreach ($msgids->chunk(500) as $chunk) {
            DB::table('messages_index')->whereIn('msgid', $chunk)->delete();
            $done += count($chunk);
            Log::info("MessageSearch: deindex ...{$done} / {$total}");
        }

        DB::table('words_cache')->delete();

        return $done;
    }

    /**
     * Add search index entries for recent approved messages not yet indexed.
     * Mirrors V1 cron/message_unindexed.php.
     *
     * @return int Number of messages indexed.
     */
    public function indexUnindexedMessages(bool $dryRun = false): int
    {
        $cutoff = now()->subDays(31)->startOfDay()->format('Y-m-d');

        $msgs = DB::table('messages_groups')
            ->join('messages', 'messages.id', '=', 'messages_groups.msgid')
            ->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)
            ->where('messages_groups.deleted', 0)
            ->where('messages_groups.arrival', '>=', $cutoff)
            ->whereNotIn('messages_groups.msgid', function ($q) {
                $q->select('msgid')->from('messages_index');
            })
            ->orderByDesc('messages_groups.arrival')
            ->select('messages_groups.msgid', 'messages.subject', 'messages_groups.arrival', 'messages_groups.groupid')
            ->get();

        $total = $msgs->count();
        Log::info("MessageSearch: " . ($dryRun ? "would index " : "indexing ") . "{$total} messages");

        if ($dryRun) {
            return $total;
        }

        $count = 0;

        foreach ($msgs as $msg) {
            $toadd = $msg->subject;

            [$type, $item] = $this->parseSubject($msg->subject);
            if ($item) {
                $toadd = $item;
            }

            $arrivalTimestamp = Carbon::parse($msg->arrival)->timestamp;

            $this->indexString($msg->msgid, $toadd, $arrivalTimestamp, $msg->groupid);

            $count++;
            Log::info("{$count} / {$total}");
        }

        DB::table('words_cache')->delete();

        return $count;
    }

    private function parseSubject(string $subject): array
    {
        $type = null;
        $item = null;
        $location = null;

        $p = strpos($subject, ':');

        if ($p !== false) {
            $startp = $p;
            $rest = trim(substr($subject, $p + 1));
            $p = strlen($rest) - 1;

            if (substr($rest, -1) === ')') {
                $count = 0;

                do {
                    $curr = substr($rest, $p, 1);

                    if ($curr === '(') {
                        $count--;
                    } elseif ($curr === ')') {
                        $count++;
                    }

                    $p--;
                } while ($count > 0 && $p > 0);

                if ($count === 0) {
                    $type = trim(substr($subject, 0, $startp));
                    $location = trim(substr($rest, $p + 2, strlen($rest) - $p - 3));
                    $item = trim(substr($rest, 0, $p));
                }
            }
        }

        return [$type, $item, $location];
    }

    private function getWords(string $string): array
    {
        $string = preg_replace('/[^a-z0-9]+/i', ' ', strtolower($string));
        $words = preg_split('/\s+/', $string);
        $words = array_diff($words, $this->common);

        $ret = [];
        foreach ($words as $word) {
            if (strlen($word) >= 2) {
                $ret[] = substr($word, 0, 10); // words column is varchar(10)
            }
        }

        return $ret;
    }

    private function getOrCreateWordId(string $word): ?int
    {
        $existing = DB::table('words')->where('word', $word)->value('id');

        if ($existing) {
            return (int) $existing;
        }

        DB::statement(
            "INSERT IGNORE INTO words (word, firstthree, soundex) VALUES (?, ?, SUBSTRING(SOUNDEX(?), 1, 10))",
            [$word, substr($word, 0, 3), $word]
        );

        return (int) DB::table('words')->where('word', $word)->value('id');
    }

    private function indexString(int $msgid, string $string, int $arrivalTimestamp, ?int $groupid): void
    {
        foreach ($this->getWords($string) as $word) {
            $wordId = $this->getOrCreateWordId($word);

            if (!$wordId) {
                continue;
            }

            DB::table('messages_index')->upsert(
                ['msgid' => $msgid, 'wordid' => $wordId, 'arrival' => -$arrivalTimestamp, 'groupid' => $groupid],
                ['msgid', 'wordid'],
                ['arrival']
            );

            DB::table('words')
                ->where('id', $wordId)
                ->update(['popularity' => DB::raw('-(SELECT COUNT(*) FROM messages_index WHERE wordid = ' . $wordId . ')')]);
        }
    }
}

1	<?php
2
3	namespace App\Services;
4
5	use App\Models\MessageGroup;
6	use Illuminate\Support\Carbon;
7	use Illuminate\Support\Facades\DB;
8	use Illuminate\Support\Facades\Log;
9
10	class MessageSearchService
11	{
12	// Common words excluded from the search index (matches V1 Search::$common).
13	private array $common = [
14	'the', 'old', 'new', 'please', 'thanks', 'with', 'offer', 'taken', 'wanted', 'received',
15	'attachment', 'offered', 'and', 'freegle', 'freecycle', 'for', 'large', 'small', 'are',
16	'but', 'not', 'you', 'all', 'any', 'can', 'her', 'was', 'one', 'our', 'out', 'day', 'get',
17	'has', 'him', 'how', 'now', 'see', 'two', 'who', 'did', 'its', 'let', 'she', 'too', 'use',
18	'plz', 'of', 'to', 'in', 'it', 'is', 'be', 'as', 'at', 'so', 'we', 'he', 'by', 'or', 'on',
19	'do', 'if', 'me', 'my', 'up', 'an', 'go', 'no', 'us', 'am', 'working', 'broken', 'black',
20	'white', 'grey', 'blue', 'green', 'red', 'yellow', 'brown', 'orange', 'pink', 'machine',
21	'size', 'set', 'various', 'assorted', 'different', 'bits', 'ladies', 'gents', 'kids', 'nice',
22	'brand', 'pack', 'soft', 'single', 'double', 'top', 'plastic', 'electric', 'unopened',
23	];
24
25	/**
26	* Remove search index entries for messages older than 30 days.
27	* Mirrors V1 cron/message_deindex.php.
28	*
29	* @return int Number of messages deindexed.
30	*/
31	public function deindexOldMessages(bool $dryRun = false): int	4✔
32	{
33	$date = now()->subDays(30)->format('Y-m-d');	4✔
34
35	$msgids = DB::table('messages_groups')	4✔
36	->join('messages_index', 'messages_index.msgid', '=', 'messages_groups.msgid')	4✔
37	->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)	4✔
38	->where('messages_groups.arrival', '<', $date)	4✔
39	->distinct()	4✔
40	->pluck('messages_groups.msgid');	4✔
41
42	$total = $msgids->count();	4✔
43	Log::info("MessageSearch: " . ($dryRun ? "would deindex " : "deindexing ") . "{$total} messages");	4✔
44
45	if ($dryRun) {	4✔
UNCOV 46	return $total;	×
47	}
48
49	$done = 0;	4✔
50
51	foreach ($msgids->chunk(500) as $chunk) {	4✔
52	DB::table('messages_index')->whereIn('msgid', $chunk)->delete();	1✔
53	$done += count($chunk);	1✔
54	Log::info("MessageSearch: deindex ...{$done} / {$total}");	1✔
55	}
56
57	DB::table('words_cache')->delete();	4✔
58
59	return $done;	4✔
60	}
61
62	/**
63	* Add search index entries for recent approved messages not yet indexed.
64	* Mirrors V1 cron/message_unindexed.php.
65	*
66	* @return int Number of messages indexed.
67	*/
68	public function indexUnindexedMessages(bool $dryRun = false): int	5✔
69	{
70	$cutoff = now()->subDays(31)->startOfDay()->format('Y-m-d');	5✔
71
72	$msgs = DB::table('messages_groups')	5✔
73	->join('messages', 'messages.id', '=', 'messages_groups.msgid')	5✔
74	->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)	5✔
75	->where('messages_groups.deleted', 0)	5✔
76	->where('messages_groups.arrival', '>=', $cutoff)	5✔
77	->whereNotIn('messages_groups.msgid', function ($q) {	5✔
78	$q->select('msgid')->from('messages_index');	5✔
79	})	5✔
80	->orderByDesc('messages_groups.arrival')	5✔
81	->select('messages_groups.msgid', 'messages.subject', 'messages_groups.arrival', 'messages_groups.groupid')	5✔
82	->get();	5✔
83
84	$total = $msgs->count();	5✔
85	Log::info("MessageSearch: " . ($dryRun ? "would index " : "indexing ") . "{$total} messages");	5✔
86
87	if ($dryRun) {	5✔
UNCOV 88	return $total;	×
89	}
90
91	$count = 0;	5✔
92
93	foreach ($msgs as $msg) {	5✔
94	$toadd = $msg->subject;	2✔
95
96	[$type, $item] = $this->parseSubject($msg->subject);	2✔
97	if ($item) {	2✔
98	$toadd = $item;	2✔
99	}
100
101	$arrivalTimestamp = Carbon::parse($msg->arrival)->timestamp;	2✔
102
103	$this->indexString($msg->msgid, $toadd, $arrivalTimestamp, $msg->groupid);	2✔
104
105	$count++;	2✔
106	Log::info("{$count} / {$total}");	2✔
107	}
108
109	DB::table('words_cache')->delete();	5✔
110
111	return $count;	5✔
112	}
113
114	private function parseSubject(string $subject): array	2✔
115	{
116	$type = null;	2✔
117	$item = null;	2✔
118	$location = null;	2✔
119
120	$p = strpos($subject, ':');	2✔
121
122	if ($p !== false) {	2✔
123	$startp = $p;	2✔
124	$rest = trim(substr($subject, $p + 1));	2✔
125	$p = strlen($rest) - 1;	2✔
126
127	if (substr($rest, -1) === ')') {	2✔
128	$count = 0;	2✔
129
130	do {
131	$curr = substr($rest, $p, 1);	2✔
132
133	if ($curr === '(') {	2✔
134	$count--;	2✔
135	} elseif ($curr === ')') {	2✔
136	$count++;	2✔
137	}
138
139	$p--;	2✔
140	} while ($count > 0 && $p > 0);	2✔
141
142	if ($count === 0) {	2✔
143	$type = trim(substr($subject, 0, $startp));	2✔
144	$location = trim(substr($rest, $p + 2, strlen($rest) - $p - 3));	2✔
145	$item = trim(substr($rest, 0, $p));	2✔
146	}
147	}
148	}
149
150	return [$type, $item, $location];	2✔
151	}
152
153	private function getWords(string $string): array	2✔
154	{
155	$string = preg_replace('/[^a-z0-9]+/i', ' ', strtolower($string));	2✔
156	$words = preg_split('/\s+/', $string);	2✔
157	$words = array_diff($words, $this->common);	2✔
158
159	$ret = [];	2✔
160	foreach ($words as $word) {	2✔
161	if (strlen($word) >= 2) {	2✔
162	$ret[] = substr($word, 0, 10); // words column is varchar(10)	2✔
163	}
164	}
165
166	return $ret;	2✔
167	}
168
169	private function getOrCreateWordId(string $word): ?int	2✔
170	{
171	$existing = DB::table('words')->where('word', $word)->value('id');	2✔
172
173	if ($existing) {	2✔
UNCOV 174	return (int) $existing;	×
175	}
176
177	DB::statement(	2✔
178	"INSERT IGNORE INTO words (word, firstthree, soundex) VALUES (?, ?, SUBSTRING(SOUNDEX(?), 1, 10))",	2✔
179	[$word, substr($word, 0, 3), $word]	2✔
180	);	2✔
181
182	return (int) DB::table('words')->where('word', $word)->value('id');	2✔
183	}
184
185	private function indexString(int $msgid, string $string, int $arrivalTimestamp, ?int $groupid): void	2✔
186	{
187	foreach ($this->getWords($string) as $word) {	2✔
188	$wordId = $this->getOrCreateWordId($word);	2✔
189
190	if (!$wordId) {	2✔
UNCOV 191	continue;	×
192	}
193
194	DB::table('messages_index')->upsert(	2✔
195	['msgid' => $msgid, 'wordid' => $wordId, 'arrival' => -$arrivalTimestamp, 'groupid' => $groupid],	2✔
196	['msgid', 'wordid'],	2✔
197	['arrival']	2✔
198	);	2✔
199
200	DB::table('words')	2✔
201	->where('id', $wordId)	2✔
202	->update(['popularity' => DB::raw('-(SELECT COUNT(*) FROM messages_index WHERE wordid = ' . $wordId . ')')]);	2✔
203	}
204	}
205	}

Freegle / Iznik / 11342

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous