27488

Committed 17 Jul 2026 11:23AM UTC coverage: 72.341% (+0.01%) from 72.33%

Build # 27488

Build Type

push

circleci

Committed by

web-flow

Commit Message

Merge pull request #1095 from Freegle/fix/email-post-no-location-warning-9865

fix(9865): stop posts going live with no location; fix false "outside UK" scam warning

Coverage Stats

13096 of 17270 branches covered (75.83%)

Branch coverage included in aggregate %.

63 of 65 new or added lines in 4 files covered. (96.92%)

87 existing lines in 6 files now uncovered.

136704 of 189804 relevant lines covered (72.02%)

37.86 hits per line

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

94.85

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

<?php

namespace App\Services;

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

class MessageSpatialService
{
    // V1: MessageCollection::RECENTPOSTS = "Midnight 31 days ago"
    private const RECENT_DAYS = 31;
    private const SRID = 3857;

    private SpatialAdminService $spatialAdmin;

    public function __construct(SpatialAdminService $spatialAdmin)
    {
        $this->spatialAdmin = $spatialAdmin;
    }

    public function updateSpatialIndex(bool $dryRun = false): array
    {
        $stats = [
            'upserted_recent' => $this->upsertRecentMessages($dryRun),
            'outcomes_updated' => $this->updateOutcomesAndPromises($dryRun),
            'removed_deleted' => $this->removeDeletedMessages($dryRun),
            'removed_old' => $this->removeOldMessages($dryRun),
            'removed_non_approved' => $this->removeNonApprovedMessages($dryRun),
        ];

        $total = array_sum($stats);
        $stats['total'] = $total;

        Log::info("MessageSpatialIndex: " . ($dryRun ? 'would update ' : 'updated ') . "{$total} entries", $stats);

        return $stats;
    }

    private function upsertRecentMessages(bool $dryRun = false): int
    {
        $cutoff = date('Y-m-d', strtotime('Midnight ' . self::RECENT_DAYS . ' days ago'));

        $msgs = DB::table('messages')
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')
            ->join('users', 'users.id', '=', 'messages.fromuser')
            ->leftJoin('messages_spatial', 'messages_spatial.msgid', '=', 'messages_groups.msgid')
            ->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages.id')
            ->where('messages_groups.arrival', '>=', $cutoff)
            ->whereNotNull('messages.lat')
            ->whereNotNull('messages.lng')
            ->whereNull('messages.deleted')
            ->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)
            // The membership itself must be live. Rippling's "removed on origin removal" (and
            // group-leave retraction) sets messages_groups.deleted=1 while leaving
            // collection=Approved and messages.deleted NULL, so without this a removed copy with
            // a recent arrival is indexed straight back into browse. (Whatever set that arrival —
            // e.g. autorepost — should not have touched a dead membership either; see
            // AutoRepostService::getCandidates.)
            ->where('messages_groups.deleted', 0)
            ->whereNull('users.deleted')
            ->where(function ($q) {
                // Include messages with no outcome, or Taken/Received (same as V1: outcome IS NULL OR outcome IN ('Taken','Received'))
                $q->whereNull('messages_outcomes.outcome')
                    ->orWhereIn('messages_outcomes.outcome', [Message::OUTCOME_TAKEN, Message::OUTCOME_RECEIVED]);
            })
            ->where(function ($q) {
                $q->whereNull('messages_spatial.msgid')
                    ->orWhereRaw('ST_X(messages_spatial.point) != messages.lng')
                    ->orWhereRaw('ST_Y(messages_spatial.point) != messages.lat')
                    ->orWhereNull('messages_spatial.groupid')
                    ->orWhereRaw('messages_spatial.groupid != messages_groups.groupid')
                    ->orWhereRaw('messages_groups.arrival != messages_spatial.arrival');
            })
            ->select(
                'messages.id',
                'messages.lat',
                'messages.lng',
                'messages_groups.groupid',
                'messages_groups.arrival',
                'messages_groups.msgtype',
            )
            ->distinct()
            ->get();

        $count = 0;
        foreach ($msgs as $msg) {
            if (!$dryRun) {
                // Coordinates come from DB, not user input — safe to embed in WKT.
                $wkt = "POINT({$msg->lng} {$msg->lat})";
                $srid = self::SRID;

                DB::statement(
                    "INSERT INTO messages_spatial (msgid, point, groupid, msgtype, arrival)
                     VALUES (?, ST_GeomFromText('$wkt', $srid), ?, ?, ?)
                     ON DUPLICATE KEY UPDATE
                       point = ST_GeomFromText('$wkt', $srid),
                       groupid = ?,
                       msgtype = ?,
                       arrival = ?",
                    [$msg->id, $msg->groupid, $msg->msgtype, $msg->arrival,
                     $msg->groupid, $msg->msgtype, $msg->arrival]
                );
            }
            $count++;
        }

        return $count;
    }

    private function updateOutcomesAndPromises(bool $dryRun = false): int
    {
        $msgs = DB::table('messages_spatial')
            ->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages_spatial.msgid')
            ->leftJoin('messages_promises', 'messages_promises.msgid', '=', 'messages_spatial.msgid')
            ->select(
                'messages_spatial.id',
                'messages_spatial.msgid',
                'messages_spatial.successful',
                'messages_spatial.promised',
                'messages_outcomes.outcome',
                'messages_promises.promisedat',
            )
            ->orderByDesc('messages_outcomes.timestamp')
            ->get();

        $count = 0;
        $deletedMsgids = [];
        foreach ($msgs as $msg) {
            if ($msg->outcome === Message::OUTCOME_WITHDRAWN || $msg->outcome === Message::OUTCOME_EXPIRED) {
                if (!$dryRun) {
                    DB::table('messages_spatial')->where('id', $msg->id)->delete();
                    $deletedMsgids[] = $msg->msgid;
                }
                $count++;
            } elseif ($msg->outcome === Message::OUTCOME_TAKEN || $msg->outcome === Message::OUTCOME_RECEIVED) {
                if (!$msg->successful) {
                    if (!$dryRun) {
                        DB::table('messages_spatial')->where('id', $msg->id)->update(['successful' => 1]);
                    }
                    $count++;
                }
            } elseif ($msg->successful) {
                if (!$dryRun) {
                    DB::table('messages_spatial')->where('id', $msg->id)->update(['successful' => 0]);
                }
                $count++;
            }

            if ($msg->promised && !$msg->promisedat) {
                if (!$dryRun) {
                    DB::table('messages_spatial')->where('id', $msg->id)->update(['promised' => 0]);
                }
                $count++;
            } elseif (!$msg->promised && $msg->promisedat) {
                if (!$dryRun) {
                    DB::table('messages_spatial')->where('id', $msg->id)->update(['promised' => 1]);
                }
                $count++;
            }
        }

        if (!empty($deletedMsgids)) {
            $this->spatialAdmin->removeItems('messages', $deletedMsgids);
        }

        return $count;
    }

    private function removeDeletedMessages(bool $dryRun = false): int
    {
        $rows = DB::table('messages_spatial')
            ->join('messages', 'messages_spatial.msgid', '=', 'messages.id')
            ->leftJoin('users', 'users.id', '=', 'messages.fromuser')
            ->where(function ($q) {
                $q->whereNull('messages.fromuser')
                    ->orWhereNotNull('messages.deleted')
                    ->orWhereNotNull('users.deleted');
            })
            ->select('messages_spatial.id', 'messages_spatial.msgid')
            ->get();

        if ($rows->isEmpty()) {
            return 0;
        }

        if (!$dryRun) {
            DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();
            $this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());
        }

        return $rows->count();
    }

    private function removeOldMessages(bool $dryRun = false): int
    {
        $cutoff = date('Y-m-d', strtotime('Midnight ' . self::RECENT_DAYS . ' days ago'));

        $rows = DB::table('messages_spatial')
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages_spatial.msgid')
            ->where('messages_groups.arrival', '<', $cutoff)
            ->select('messages_spatial.id', 'messages_spatial.msgid')
            ->get();

        if ($rows->isEmpty()) {
            return 0;
        }

        if (!$dryRun) {
            DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();
            $this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());
        }

        return $rows->count();
    }

    private function removeNonApprovedMessages(bool $dryRun = false): int
    {
        // Join on BOTH msgid AND groupid: messages_spatial holds one row per post (unique
        // msgid) for a specific group, so a spatial row must only be dropped when the
        // messages_groups row for ITS OWN group is non-approved. Joining on msgid alone
        // would let a rippled-in Pending row on another group (#6) delete the origin post's
        // approved spatial row, flickering it out of browse every spatial-index run.
        //
        // Also drop it when its own membership is soft-deleted (deleted=1): rippling's "removed
        // on origin removal" leaves collection=Approved but sets deleted=1, so the collection
        // check alone would leave a removed copy in browse until it ages out. upsertRecentMessages
        // runs before this pass, so a message still live on ANOTHER group has already had its
        // spatial row re-pointed to that group and is not caught here.
        $rows = DB::table('messages_spatial')
            ->join('messages_groups', function ($join) {
                $join->on('messages_groups.msgid', '=', 'messages_spatial.msgid')
                    ->on('messages_groups.groupid', '=', 'messages_spatial.groupid');
            })
            ->where(function ($q) {
                $q->where('messages_groups.collection', '!=', MessageGroup::COLLECTION_APPROVED)
                    ->orWhere('messages_groups.deleted', 1);
            })
            ->select('messages_spatial.id', 'messages_spatial.msgid')
            ->get();

        if ($rows->isEmpty()) {
            return 0;
        }

        if (!$dryRun) {
            DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();
            $this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());
        }

        return $rows->count();
    }

    /**
     * Add a single just-approved message to the spatial index immediately, so it
     * appears in browse/search without waiting for the every-5-minute reconciler.
     *
     * No-op unless the message is Approved, has a location, and has no outcome —
     * messages_spatial backs the public browse/map, so Pending/Spam/Rejected
     * messages must never be added here. Safe to call inside the same transaction
     * that set the collection to Approved (it reads its own uncommitted write).
     */
    public function addApprovedMessage(int $msgid): void
    {
        $msg = DB::table('messages')
            ->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')
            ->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages.id')
            ->where('messages.id', $msgid)
            ->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)
            ->where('messages_groups.deleted', 0)
            ->whereNull('messages.deleted')
            ->whereNotNull('messages.lat')
            ->whereNotNull('messages.lng')
            ->whereNull('messages_outcomes.id')
            ->orderByDesc('messages_groups.arrival')
            ->select(
                'messages.id',
                'messages.lat',
                'messages.lng',
                DB::raw('messages.type as msgtype'),
                'messages_groups.groupid',
                'messages_groups.arrival',
            )
            ->first();

        if (!$msg) {
            return;
        }

        // Coordinates come from the DB, not user input — safe to embed in WKT.
        $wkt  = "POINT({$msg->lng} {$msg->lat})";
        $srid = self::SRID;

        DB::statement(
            "INSERT INTO messages_spatial (msgid, point, groupid, msgtype, arrival)
             VALUES (?, ST_GeomFromText('$wkt', $srid), ?, ?, ?)
             ON DUPLICATE KEY UPDATE
               point = ST_GeomFromText('$wkt', $srid),
               groupid = ?,
               msgtype = ?,
               arrival = ?",
            [$msg->id, $msg->groupid, $msg->msgtype, $msg->arrival,
             $msg->groupid, $msg->msgtype, $msg->arrival]
        );
    }
}

1	<?php
2
3	namespace App\Services;
4
5	use App\Models\Message;
6	use App\Models\MessageGroup;
7	use Illuminate\Support\Facades\DB;
8	use Illuminate\Support\Facades\Log;
9
10	class MessageSpatialService
11	{
12	// V1: MessageCollection::RECENTPOSTS = "Midnight 31 days ago"
13	private const RECENT_DAYS = 31;
14	private const SRID = 3857;
15
16	private SpatialAdminService $spatialAdmin;
17
18	public function __construct(SpatialAdminService $spatialAdmin)	20✔
19	{
20	$this->spatialAdmin = $spatialAdmin;	20✔
21	}
22
23	public function updateSpatialIndex(bool $dryRun = false): array	14✔
24	{
25	$stats = [	14✔
26	'upserted_recent' => $this->upsertRecentMessages($dryRun),	14✔
27	'outcomes_updated' => $this->updateOutcomesAndPromises($dryRun),	14✔
28	'removed_deleted' => $this->removeDeletedMessages($dryRun),	14✔
29	'removed_old' => $this->removeOldMessages($dryRun),	14✔
30	'removed_non_approved' => $this->removeNonApprovedMessages($dryRun),	14✔
31	];	14✔
32
33	$total = array_sum($stats);	14✔
34	$stats['total'] = $total;	14✔
35
36	Log::info("MessageSpatialIndex: " . ($dryRun ? 'would update ' : 'updated ') . "{$total} entries", $stats);	14✔
37
38	return $stats;	14✔
39	}
40
41	private function upsertRecentMessages(bool $dryRun = false): int	14✔
42	{
43	$cutoff = date('Y-m-d', strtotime('Midnight ' . self::RECENT_DAYS . ' days ago'));	14✔
44
45	$msgs = DB::table('messages')	14✔
46	->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')	14✔
47	->join('users', 'users.id', '=', 'messages.fromuser')	14✔
48	->leftJoin('messages_spatial', 'messages_spatial.msgid', '=', 'messages_groups.msgid')	14✔
49	->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages.id')	14✔
50	->where('messages_groups.arrival', '>=', $cutoff)	14✔
51	->whereNotNull('messages.lat')	14✔
52	->whereNotNull('messages.lng')	14✔
53	->whereNull('messages.deleted')	14✔
54	->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)	14✔
55	// The membership itself must be live. Rippling's "removed on origin removal" (and	14✔
56	// group-leave retraction) sets messages_groups.deleted=1 while leaving	14✔
57	// collection=Approved and messages.deleted NULL, so without this a removed copy with	14✔
58	// a recent arrival is indexed straight back into browse. (Whatever set that arrival —	14✔
59	// e.g. autorepost — should not have touched a dead membership either; see	14✔
60	// AutoRepostService::getCandidates.)	14✔
61	->where('messages_groups.deleted', 0)	14✔
62	->whereNull('users.deleted')	14✔
63	->where(function ($q) {	14✔
64	// Include messages with no outcome, or Taken/Received (same as V1: outcome IS NULL OR outcome IN ('Taken','Received'))
65	$q->whereNull('messages_outcomes.outcome')	14✔
66	->orWhereIn('messages_outcomes.outcome', [Message::OUTCOME_TAKEN, Message::OUTCOME_RECEIVED]);	14✔
67	})	14✔
68	->where(function ($q) {	14✔
69	$q->whereNull('messages_spatial.msgid')	14✔
70	->orWhereRaw('ST_X(messages_spatial.point) != messages.lng')	14✔
71	->orWhereRaw('ST_Y(messages_spatial.point) != messages.lat')	14✔
72	->orWhereNull('messages_spatial.groupid')	14✔
73	->orWhereRaw('messages_spatial.groupid != messages_groups.groupid')	14✔
74	->orWhereRaw('messages_groups.arrival != messages_spatial.arrival');	14✔
75	})	14✔
76	->select(	14✔
77	'messages.id',	14✔
78	'messages.lat',	14✔
79	'messages.lng',	14✔
80	'messages_groups.groupid',	14✔
81	'messages_groups.arrival',	14✔
82	'messages_groups.msgtype',	14✔
83	)	14✔
84	->distinct()	14✔
85	->get();	14✔
86
87	$count = 0;	14✔
88	foreach ($msgs as $msg) {	14✔
89	if (!$dryRun) {	14✔
90	// Coordinates come from DB, not user input — safe to embed in WKT.
91	$wkt = "POINT({$msg->lng} {$msg->lat})";	14✔
92	$srid = self::SRID;	14✔
93
94	DB::statement(	14✔
95	"INSERT INTO messages_spatial (msgid, point, groupid, msgtype, arrival)	14✔
96	VALUES (?, ST_GeomFromText('$wkt', $srid), ?, ?, ?)	14✔
97	ON DUPLICATE KEY UPDATE
98	point = ST_GeomFromText('$wkt', $srid),	14✔
99	groupid = ?,
100	msgtype = ?,
101	arrival = ?",	14✔
102	[$msg->id, $msg->groupid, $msg->msgtype, $msg->arrival,	14✔
103	$msg->groupid, $msg->msgtype, $msg->arrival]	14✔
104	);	14✔
105	}
106	$count++;	14✔
107	}
108
109	return $count;	14✔
110	}
111
112	private function updateOutcomesAndPromises(bool $dryRun = false): int	14✔
113	{
114	$msgs = DB::table('messages_spatial')	14✔
115	->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages_spatial.msgid')	14✔
116	->leftJoin('messages_promises', 'messages_promises.msgid', '=', 'messages_spatial.msgid')	14✔
117	->select(	14✔
118	'messages_spatial.id',	14✔
119	'messages_spatial.msgid',	14✔
120	'messages_spatial.successful',	14✔
121	'messages_spatial.promised',	14✔
122	'messages_outcomes.outcome',	14✔
123	'messages_promises.promisedat',	14✔
124	)	14✔
125	->orderByDesc('messages_outcomes.timestamp')	14✔
126	->get();	14✔
127
128	$count = 0;	14✔
129	$deletedMsgids = [];	14✔
130	foreach ($msgs as $msg) {	14✔
131	if ($msg->outcome === Message::OUTCOME_WITHDRAWN \|\| $msg->outcome === Message::OUTCOME_EXPIRED) {	14✔
132	if (!$dryRun) {	3✔
133	DB::table('messages_spatial')->where('id', $msg->id)->delete();	3✔
134	$deletedMsgids[] = $msg->msgid;	3✔
135	}
136	$count++;	3✔
137	} elseif ($msg->outcome === Message::OUTCOME_TAKEN \|\| $msg->outcome === Message::OUTCOME_RECEIVED) {	14✔
138	if (!$msg->successful) {	14✔
139	if (!$dryRun) {	14✔
140	DB::table('messages_spatial')->where('id', $msg->id)->update(['successful' => 1]);	14✔
141	}
142	$count++;	14✔
143	}
144	} elseif ($msg->successful) {	14✔
145	if (!$dryRun) {	×
146	DB::table('messages_spatial')->where('id', $msg->id)->update(['successful' => 0]);	×
147	}
148	$count++;	×
149	}
150
151	if ($msg->promised && !$msg->promisedat) {	14✔
152	if (!$dryRun) {	×
153	DB::table('messages_spatial')->where('id', $msg->id)->update(['promised' => 0]);	×
154	}
155	$count++;	×
156	} elseif (!$msg->promised && $msg->promisedat) {	14✔
157	if (!$dryRun) {	×
158	DB::table('messages_spatial')->where('id', $msg->id)->update(['promised' => 1]);	×
159	}
160	$count++;	×
161	}
162	}
163
164	if (!empty($deletedMsgids)) {	14✔
165	$this->spatialAdmin->removeItems('messages', $deletedMsgids);	3✔
166	}
167
168	return $count;	14✔
169	}
170
171	private function removeDeletedMessages(bool $dryRun = false): int	14✔
172	{
173	$rows = DB::table('messages_spatial')	14✔
174	->join('messages', 'messages_spatial.msgid', '=', 'messages.id')	14✔
175	->leftJoin('users', 'users.id', '=', 'messages.fromuser')	14✔
176	->where(function ($q) {	14✔
177	$q->whereNull('messages.fromuser')	14✔
178	->orWhereNotNull('messages.deleted')	14✔
179	->orWhereNotNull('users.deleted');	14✔
180	})	14✔
181	->select('messages_spatial.id', 'messages_spatial.msgid')	14✔
182	->get();	14✔
183
184	if ($rows->isEmpty()) {	14✔
185	return 0;	12✔
186	}
187
188	if (!$dryRun) {	2✔
189	DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();	2✔
190	$this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());	2✔
191	}
192
193	return $rows->count();	2✔
194	}
195
196	private function removeOldMessages(bool $dryRun = false): int	14✔
197	{
198	$cutoff = date('Y-m-d', strtotime('Midnight ' . self::RECENT_DAYS . ' days ago'));	14✔
199
200	$rows = DB::table('messages_spatial')	14✔
201	->join('messages_groups', 'messages_groups.msgid', '=', 'messages_spatial.msgid')	14✔
202	->where('messages_groups.arrival', '<', $cutoff)	14✔
203	->select('messages_spatial.id', 'messages_spatial.msgid')	14✔
204	->get();	14✔
205
206	if ($rows->isEmpty()) {	14✔
207	return 0;	13✔
208	}
209
210	if (!$dryRun) {	1✔
211	DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();	1✔
212	$this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());	1✔
213	}
214
215	return $rows->count();	1✔
216	}
217
218	private function removeNonApprovedMessages(bool $dryRun = false): int	14✔
219	{
220	// Join on BOTH msgid AND groupid: messages_spatial holds one row per post (unique
221	// msgid) for a specific group, so a spatial row must only be dropped when the
222	// messages_groups row for ITS OWN group is non-approved. Joining on msgid alone
223	// would let a rippled-in Pending row on another group (#6) delete the origin post's
224	// approved spatial row, flickering it out of browse every spatial-index run.
225	//
226	// Also drop it when its own membership is soft-deleted (deleted=1): rippling's "removed
227	// on origin removal" leaves collection=Approved but sets deleted=1, so the collection
228	// check alone would leave a removed copy in browse until it ages out. upsertRecentMessages
229	// runs before this pass, so a message still live on ANOTHER group has already had its
230	// spatial row re-pointed to that group and is not caught here.
231	$rows = DB::table('messages_spatial')	14✔
232	->join('messages_groups', function ($join) {	14✔
233	$join->on('messages_groups.msgid', '=', 'messages_spatial.msgid')	14✔
234	->on('messages_groups.groupid', '=', 'messages_spatial.groupid');	14✔
235	})	14✔
236	->where(function ($q) {	14✔
237	$q->where('messages_groups.collection', '!=', MessageGroup::COLLECTION_APPROVED)	14✔
238	->orWhere('messages_groups.deleted', 1);	14✔
239	})	14✔
240	->select('messages_spatial.id', 'messages_spatial.msgid')	14✔
241	->get();	14✔
242
243	if ($rows->isEmpty()) {	14✔
244	return 0;	12✔
245	}
246
247	if (!$dryRun) {	2✔
248	DB::table('messages_spatial')->whereIn('id', $rows->pluck('id'))->delete();	2✔
249	$this->spatialAdmin->removeItems('messages', $rows->pluck('msgid')->all());	2✔
250	}
251
252	return $rows->count();	2✔
253	}
254
255	/**
256	* Add a single just-approved message to the spatial index immediately, so it
257	* appears in browse/search without waiting for the every-5-minute reconciler.
258	*
259	* No-op unless the message is Approved, has a location, and has no outcome —
260	* messages_spatial backs the public browse/map, so Pending/Spam/Rejected
261	* messages must never be added here. Safe to call inside the same transaction
262	* that set the collection to Approved (it reads its own uncommitted write).
263	*/
264	public function addApprovedMessage(int $msgid): void	6✔
265	{
266	$msg = DB::table('messages')	6✔
267	->join('messages_groups', 'messages_groups.msgid', '=', 'messages.id')	6✔
268	->leftJoin('messages_outcomes', 'messages_outcomes.msgid', '=', 'messages.id')	6✔
269	->where('messages.id', $msgid)	6✔
270	->where('messages_groups.collection', MessageGroup::COLLECTION_APPROVED)	6✔
271	->where('messages_groups.deleted', 0)	6✔
272	->whereNull('messages.deleted')	6✔
273	->whereNotNull('messages.lat')	6✔
274	->whereNotNull('messages.lng')	6✔
275	->whereNull('messages_outcomes.id')	6✔
276	->orderByDesc('messages_groups.arrival')	6✔
277	->select(	6✔
278	'messages.id',	6✔
279	'messages.lat',	6✔
280	'messages.lng',	6✔
281	DB::raw('messages.type as msgtype'),	6✔
282	'messages_groups.groupid',	6✔
283	'messages_groups.arrival',	6✔
284	)	6✔
285	->first();	6✔
286
287	if (!$msg) {	6✔
UNCOV 288	return;	×
289	}
290
291	// Coordinates come from the DB, not user input — safe to embed in WKT.
292	$wkt = "POINT({$msg->lng} {$msg->lat})";	6✔
293	$srid = self::SRID;	6✔
294
295	DB::statement(	6✔
296	"INSERT INTO messages_spatial (msgid, point, groupid, msgtype, arrival)	6✔
297	VALUES (?, ST_GeomFromText('$wkt', $srid), ?, ?, ?)	6✔
298	ON DUPLICATE KEY UPDATE
299	point = ST_GeomFromText('$wkt', $srid),	6✔
300	groupid = ?,
301	msgtype = ?,
302	arrival = ?",	6✔
303	[$msg->id, $msg->groupid, $msg->msgtype, $msg->arrival,	6✔
304	$msg->groupid, $msg->msgtype, $msg->arrival]	6✔
305	);	6✔
306	}
307	}

Freegle / Iznik / 27488

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