Include duplicate rows in a many to many relationship

Question

I have an Event class and a Question class. These form a many-to-many relationship. The pivot table has an additional asking column.

My database:

events (id)
questions (id)
event_question (id, event_id, question_id, asking)

My Event class:

class Event {
    public function questions() {
        return $this->belongsToMany(Question::class)
            ->withPivot('asking');
    }
}

Now for example, I may have duplicate Event-Question pairs in the pivot table, but the asking pivot column will be different.

id   |  event_id  |  question_id  |  asking
-----+------------+---------------+--------------
1    |  5         | 10            | 'purchaser'
2    |  5         | 10            | 'attendee'

I want Eloquent to fetch both rows, but instead it is only returning one of them.

dd($event->questions->toArray());

// Should be 2 rows but there is only 1
[
  [
    'id'          => 1,
    'event_id'    => 5,
    'question_id' => 10,
    'pivot'       => [
      'asking' => 'attendee', // asking 'buyer' is missing
    ],
  ],
];

Answer 1

The solution to this is pretty simple, I just need to specify which columns are distinct:

class Event {
    public function questions() {
        return $this->belongsToMany(Question::class)
            ->withPivot('asking')
            ->selectRaw('DISTINCT questions.id, event_question.asking, questions.*');
    }
}