时间戳

首页 > 解决方案 > Laravel query scope has against dynamic value

mysql - Laravel query scope has against dynamic value

问题描述

I'm struggling to come up with the right mix of builder vs raw SQL that addresses my need. I have a setting that allows a user to configure X number of TournamentQuizmasters be required for X number of Teams. I need to do this calculation on a MySQL level for efficiency, so I can quickly query all eligible teams. I have other query scopes in place to determine what an eligible team is (e.g. withEnoughPlayers()).

This code doesn't work, but I think it communicates my intentions.

// A collection of Teams
class TeamSet extends Model {
    public function scopeWithEnoughQuizmastersBasedOnTeamCount(Builder $q, Tournament $tournament)
    {
        // Pull only the groups that bring enough quizmasters.  The required number
        // of quizmasters is based off of the number of teams they're bringing.
        return $q->whereHas('group', function (Builder $q) use ($tournament) {
                $q->has(
                    'tournamentQuizmasters',
                    '>=',
                    DB::raw('FLOOR(('.$tournament->settings->quizmastersToRequireByTeamCount().' * '.$q->selectRaw('COUNT(*)')->teams()->withEnoughPlayers($tournament)->toSql().') / '.$tournament->settings->teamCountToRequireQuizmastersBy().')')
                );
        });
    }
}

Running this query I get Call to undefined method Illuminate\Database\Eloquent\Builder::teams() which I believe is because of the selectRaw() proceeding it. I also don't think I can actually call ->teams() the way I am here, but I'm a bit blind from how I should be altering this to obtain the results I'm looking for.

How can I perform this dynamic expression while utilizing query builder so I can utilize my scopes?

I'm using Laravel 5.7

Models & Relations

class TeamSet {
    public function group() : BelongsTo
    {
        return $this->belongsTo(Group::class);
    }
    public function teams() : HasMany
    {
        return $this->hasMany(Team::class)
    }
}

class Team {
    public function teamSet() : BelongsTo
    {
        return $this->belongsTo(TeamSet::class)
    }
}

class Group {

    public function tournamentQuizmasters() : HasMany
    {
        return $this->hasMany(TournamentQuizmaster::class);
    }

    public function teams() : HasManyThrough
    {
        return $this->hasManyThrough(Team::class, TeamSet::class);
    }
}

class Tournament {
    public function teamSets() : HasMany
    {
        return $this->hasMany(TeamSet::class);
    }

    public function teams() : HasManyThrough
    {
        return $this->hasManyThrough(Team::class, TeamSet::class);
    }
}

class TournamentQuizmaster {
    public function group() : BelongsTo
    {
        return $this->belongsTo(Group::class);
    }

    public function tournament() : BelongsTo
    {
        return $this->belongsTo(Tournament::class);
    }
}

Description of models:

$tournament->settings

This is stored as json in the database, but cast to an object so I have a reasonable place to keep default values. Here's a snippet of that class:

use Illuminate\Support\Fluent;

class Settings extends Fluent
{
    const DEFAULT_MAXIMUM_PLAYERS_PER_TEAM = 5;
    const DEFAULT_MINIMUM_PLAYERS_PER_TEAM = 3;

    public function __construct($attributes = [])
    {
        if ($attributes !== null) {
            parent::__construct($attributes);
        }
    }

    public function shouldRequireQuizmasters() : bool
    {
        return $this->get('requireQuizmasters', 'none') != 'none';
    }

    public function shouldRequireQuizmastersByGroup() : bool
    {
        return $this->get('requireQuizmasters', null) == 'group';
    }

    public function quizmastersToRequireByGroup() : int
    {
        return $this->get('quizmastersToRequireByGroup', 1);
    }

    public function setQuizmastersToRequireByGroup(int $quizmasterCount)
    {
        $this->quizmastersToRequireByGroup = $quizmasterCount;
    }

    public function shouldRequireQuizmastersByTeamCount() : bool
    {
        return $this->get('requireQuizmasters', null) == 'team_count';
    }

    public function quizmastersToRequireByTeamCount() : int
    {
        return $this->get('quizmastersToRequireByTeamCount', 1);
    }

The json stored in the database ends up looking like:

{"collectShirtSizes":"1","collectQuizmasterPreferences":"1","allowsGuestPlayers":true,"minimumPlayersPerTeam":3,"maximumPlayersPerTeam":6,"requireQuizmasters":"group","quizmastersToRequireByGroup":1,"quizmastersToRequireByTeamCount":1,"teamCountToRequireQuizmastersBy":2}

标签： mysqllaravel

解决方案

在下面的文章中，我已经能够使用 subSelect 技术做一些类似的事情。 https://reinink.ca/articles/dynamic-relationships-in-laravel-using-subqueries

基于此，您可以获得添加到您的组查询中的有足够球员的球队的数量，并将其与锦标赛测验大师计数进行比较。我希望我足够理解您的查询。可能是这样的。

/// In Group
public function scopeHasRequiredAmount(Builder $builder, Tournament $tournament)
{
    return $builder->withCount('tournamentQuizmasters')
        ->teamsWithEnoughPlayersCount()
        ->havingRaw(
            'tournamentQuizmasters_count > FLOOR((' . $tournament->settings->quizmastersToRequireByTeamCount() .
            '* teams_with_enough_players_count ) /'
            . $tournament->settings->teamCountToRequireQuizmastersBy() . ')'
        );
}

/// adds count of teams with enough players to query
public function scopeTeamsWithEnoughPlayersCount(Builder $builder, Tournament $tournament)
{
    return $builder->addSubSelect('teams_with_enough_players_count', Team::selectRaw('count(*)')
        ->whereColumn('groups.id', 'teams.group_id')
        ->withEnoughPlayers($tournament)
    );
}

/// In Teamset
function scopeWithEnoughQuizmastersBasedOnTeamCount(Builder $builder, $tournament)
{
    return $builder->whereHas('group', function (Builder $q) use ($tournament) {
        $q->hasRequiredAmount($tournament);
    });

}

推荐阅读