Laravel Eloquent query siblings in same table with eager loading

I have a parent table called patients which has a one-to-many relationship with a child table called notes. (i.e. One patient can have several notes). If given a note, I would like to find other notes for the same patient. Notes are related to patients by a fk called patient_id.

In SQL, I’d do this:

SELECT * FROM notes WHERE patient_id={note.patient_id} AND id <> {note.id}

In Eloquent, I have this:

class Note extends Model
{

    public function otherEncounterNotes()
    {
        return $this->hasMany('AppNote', 'patient_id', 'patient_id')->where('id', '<>',$this->id);
    }
...

In my database, the patient with id=1 has two notes with ids 1 and 2, so if I look for the siblings of note id 1, I should get note id 2.

When I use find(), it works as expected, but when I use where(), it returns the original note instead of the sibling. Any ideas?

>>> Note::find(1)->otherEncounterNotes->pluck('id')                                                                                                                                                                             
=> IlluminateSupportCollection {#5542
     all: [
       2,
     ],
   }

>>> Note::where('id',1)->with('otherEncounterNotes')->pluck('id')                                                                                                                                                               
=> IlluminateSupportCollection {#5526
     all: [
       1,
     ],
   }

Answer

Given a Note id, you could obtain the results you want by using the relationship with the Patient model.

$note_id = 1;

// "Pretty" syntax, but it's 3 queries
$sibling_notes = Note::find($note_id)                                      // Query 1
                      ->patient                                            // Query 2
                      ->notes()->where('id', '<>', $note_id)->pluck('id'); // Query 3

Or using a subquery

$note_id = 1;

// A bit messier, 1 query + 1 subquery
$sibling_notes = Note::where('id', '<>', $note_id)
    ->where('patient_id', function ($subquery) use ($note_id) {
        $subquery->select('patient_id')->from('notes')->where('id', $note_id)->limit(1);
    })
    ->pluck('id');

// PHP >= 7.4
Note::where('id', '<>', $note_id)
    ->where('patient_id', fn($q) => $q->select('patient_id')->from('notes')->where('id', $note_id)->limit(1))
    ->pluck('id');

The later, you could turn into a query scope

# Note model
public function scopeSiblingsOf($query, $note_id)
{
    return $query->where('id', '<>', $note_id)
                 ->where('patient_id', function ($subquery) use ($note_id) {
                     $subquery->select('patient_id')
                              ->from('notes')
                              ->where('id', $note_id)
                              ->limit(1);
                 });
}

# Usage
Note::siblingsOf(1)->pluck('id');