How can I add a group by to a hasMany relationship in laravel 5.3?

I have an elquent model named Conversation.

In that model I define a hasMany relationship on an eloquent model CallParticipant like so:

public function participants(){
    return $this->hasMany('App\Models\Purecloud\Analytics\CallParticipant', 'conversationId', 'conversationId');
}

Now, CallParticipants can be system processes, customers, or agents. I need a list of all the CallParticipants that are considered "agents" so I defined another relation like this:

public function agents(){
        return $this->hasMany('App\Models\Purecloud\Analytics\CallParticipant', 'conversationId', 'conversationId')
            ->whereIn('partPurpose', ['agent','user']);
}

Now, an "agent" can be a "participant" multiple times on a "conversation" that is to say there can be multiple rows for the same agent just with a different participantId, like this:

+----------------+---------------+--------------+-------------+
| conversationId | participantId | partUserName | partPurpose |
+----------------+---------------+--------------+-------------+
|              1 |           100 | Alex         | agent       |
|              1 |           101 | Mary         | agent       |
|              1 |           102 | Alex         | agent       | <-- I want to exlcude this
+----------------+---------------+--------------+-------------+

I need to remove these sortof-duplicates so that the relationship only returns one row for each partUserName (one row for Mary, and one row for Alex).

I tried to do this by adding a groupBy like this:

public function agents(){
    return $this->hasMany('App\Models\Purecloud\Analytics\CallParticipant', 'conversationId', 'conversationId')
        ->whereIn('partPurpose', ['agent','user'])
        ->groupBy('partUserName');
}

But this produces the error:

SQLSTATE[42000]: Syntax error or access violation: 1055 'analytics.callParticipants.participantId' isn't in GROUP BY

Is there any way I can limit the relationship to only rows with unique partUserName

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2vsMAEu
via IFTTT