CODEWITHSUNDEEP
laraveleloquentrelationship
Bartłomiej Sobieszek
Bartłomiej Sobieszek

Reputation: 2800

Eloquent relation with subquery

I am having a problem with simplyfing following eloquent relationship. It has a kind of nested query that uses a FieldRole model which I consider a bad pattern to follow. If I would have a raw query, then it should be evaluated as a one query using a join on field roles and it's name, but here it's doing two queries that probably are not cache'able by eloquent ORM.

class Team extends Model {
    // ...

    public function goalkeepers() {
        return $this->belongsToMany(
            SoccerPlayer::class,
            'team_has_players',
            'id_teams',
            'id_soccer_players'
        )->where(
            'id_field_role',
            FieldRole::where(
                'name',
                'Goalkeeper'
            )
            ->first()
            ->getKey()
        );
    }
}

The second query is exectured there

FieldRole::where(
                    'name',
                    'Goalkeeper'
                )
                ->first()
                ->getKey()

Is there a way to make it as a one query relationship?

Upvotes: 0

Views: 3795

Answers (1)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

You can use a JOIN:

public function goalkeepers() {
    return $this->belongsToMany(
        SoccerPlayer::class,
        'team_has_players',
        'id_teams',
        'id_soccer_players'
    )->join('field_roles', function($join) {
        $join->on('team_has_players.id_field_role', 'field_roles.id')
            ->where('field_roles.name', 'Goalkeeper');
    });
}

Upvotes: 2

Related Questions