Thread scopeBetweenOnly does not omit threads with more members

[fritz-c]

The scopeBetweenOnly scope for Threads does not exclude threads that include more participants than those given in the argument.

laravel-messenger/src/Models/Thread.php

Lines 216 to 226 in 765aa2d

	public function scopeBetweenOnly(Builder $query, array $participants)
	{
	$participantTable = Models::table('participants');
	return $query->whereHas('participants', function (Builder $builder) use ($participants, $participantTable) {
	return $builder->whereIn('user_id', $participants)
	->groupBy($participantTable . '.thread_id')
	->select($participantTable . '.thread_id')
	->havingRaw('COUNT(' . $participantTable . '.thread_id)=?', [count($participants)]);
	});
	}

The whereIn('user_id', $participants) part narrows it down so each grouped result will have at most two entries, the cause of the bug.

I extended the class and wrote my own method, which omits some of the table-name/model-name-fetching aspects of the source code, but could be adapted back to the style of the original source. I had also included soft-deleted participants for my own purposes; you can omit that as appropriate:

public function scopeBetweenOnlyIncludingTrashed(Builder $query, array $participants)
{
    return $query
        ->joinSub(
            MessengerParticipant::select('thread_id')
                ->whereIn('user_id', $participants)
                ->withTrashed()
                ->groupBy('thread_id')
                ->havingRaw('COUNT(*)=?', [count($participants)]),
            'party',
            'party.thread_id', '=', 'messenger_threads.id'
        )
        ->whereHas('participants', function (Builder $builder) use ($participants) {
            /** @var Builder<MessengerParticipant> $builder */
            return $builder
                ->whereColumn('thread_id', '=', 'party.thread_id')
                ->withTrashed()
                ->groupBy('thread_id')
                ->select('thread_id')
                ->havingRaw('COUNT(*)=?', [count($participants)]);
        });
}

The SQL query I modeled this after is as follows:

SELECT *
FROM messenger_threads
INNER JOIN (
	SELECT thread_id
  FROM messenger_participants
  WHERE user_id IN (1429, 1507)
  GROUP BY thread_id
  HAVING COUNT(*)=2
) party ON messenger_threads.id = party.thread_id
WHERE EXISTS (
  SELECT thread_id
  FROM messenger_participants
  WHERE party.thread_id = messenger_participants.thread_id
  GROUP BY thread_id
  HAVING COUNT(*)=2
)

Again, that would need adaptation to fit the source code, but I just leave it here as an extra resource to see the approach in use.