文章/答案/技术大牛

发布

社区首页 >问答首页 >重构雄辩的数据库查询

问重构雄辩的数据库查询
EN

Stack Overflow用户

提问于 2021-03-04 20:24:34

回答 2查看 51关注 0票数 0

目前，我基于用户输入选择的4个下拉列表查询我的数据库。一次只能选择一个输入。这一切都很好，但是我正在看它，我认为必须有一种方法来重构它，而不是有一堆if / elseif语句。

我的表格看起来像这样

<form class="form" action="{{ route('topic-filter', $topic->id) }}" method="POST" class="topic__filters">
    @csrf
    <input type="hidden" name="division_id" value="{{ $topic->id }}">

    <div class="topic-filters__item">
        <label class="form__label" for="country">Country</label>
        <x-country-select />
    </div>

    <div class="topic-filters__item">
        <label class="form__label" for="region">Region</label>
        <x-region-select />
    </div>

    <div class="topic-filters__item">
        <label class="form__label" for="county">County</label>
        <x-county-select />
    </div>

    <div class="topic-filters__item">
        <label class="form__label" for="constituency">Constituency</label>
        <x-constituency />
    </div>
</form>

我的Controller方法如下所示

public function filterTopicResults(Request $request)
    {

        $request->validate([
            'division_id' => 'required|integer',
        ]);

        $division_id = $request->get('division_id');
        $country = $request->get('country');
        $county = $request->get('county');
        $region = $request->get('region');
        $constituency = $request->get('constituency');

        if ($country) {
            $votes = DB::table('mp_votes')
                ->join('mps', 'mp_votes.mp_id', '=', 'mps.id')
                ->join('topics', 'mp_votes.division_id', '=', 'topics.id')
                ->select('topics.*', 'mp_votes.mp_aye_vote', 'mp_votes.mp_noe_vote', 'mp_votes.mp_no_vote_recorded')
                ->where('topics.id', $division_id)
                ->where('mps.country', $country)
                ->get();
        } elseif ($county) {
            $votes = DB::table('mp_votes')
                ->join('mps', 'mp_votes.mp_id', '=', 'mps.id')
                ->join('topics', 'mp_votes.division_id', '=', 'topics.id')
                ->select('topics.*', 'mp_votes.mp_aye_vote', 'mp_votes.mp_noe_vote', 'mp_votes.mp_no_vote_recorded')
                ->where('topics.id', $division_id)
                ->where('mps.county', $county)
                ->get();
        } elseif ($region) {
            $votes = DB::table('mp_votes')
                ->join('mps', 'mp_votes.mp_id', '=', 'mps.id')
                ->join('topics', 'mp_votes.division_id', '=', 'topics.id')
                ->select('topics.*', 'mp_votes.mp_aye_vote', 'mp_votes.mp_noe_vote', 'mp_votes.mp_no_vote_recorded')
                ->where('topics.id', $division_id)
                ->where('mps.region', $region)
                ->get();
        } elseif ($constituency) {
            $votes = DB::table('mp_votes')
                ->join('mps', 'mp_votes.mp_id', '=', 'mps.id')
                ->join('topics', 'mp_votes.division_id', '=', 'topics.id')
                ->select('topics.*', 'mps.name', 'mp_votes.mp_aye_vote', 'mp_votes.mp_noe_vote', 'mp_votes.mp_no_vote_recorded')
                ->where('topics.id', $division_id)
                ->where('mps.constituency', $constituency)
                ->get();
        }

        $topic = Topic::withSum('userVotes', 'user_aye_count')
            ->withSum('userVotes', 'user_noe_count')
            ->where('id', $division_id)
            ->first();

        $mpVotes = [
            'total' => ($votes->where('mp_aye_vote', 1)->count() + $votes->where('mp_noe_vote', 1)->count()),
            'ayes' => $votes->where('mp_aye_vote', 1)->count(),
            'noes' => $votes->where('mp_noe_vote', 1)->count(),
            'no_vote' => $votes->where('mp_no_vote_recorded', 1)->count()
        ];

       

        return view('topics.topic', compact([
            'topic',
            'votes',
            'mpVotes',
        ]));
    }

laravel

eloquent

laravel-8

回答 2

Stack Overflow用户

回答已采纳

发布于 2021-03-05 09:49:43

你可以试试：

when，

首先验证输入，以确保只有一个筛选器选择了
使用

$request->validate([
   'division_id' => 'required|integer',
]);
$division_id = $request->get('division_id');
$country = $request->get('country');
$county = $request->get('county');
$region = $request->get('region');
$constituency = $request->get('constituency');
$votes = DB::table('mp_votes')
    ->join('mps', 'mp_votes.mp_id', '=', 'mps.id')
    ->join('topics', 'mp_votes.division_id', '=', 'topics.id')
    ->select('topics.*', 'mp_votes.mp_aye_vote', 'mp_votes.mp_noe_vote', 'mp_votes.mp_no_vote_recorded')
    ->where('topics.id', $division_id)
    ->when($country, function ($query, $country) { $query->where('mps.country', $country); })
    ->when($county, function ($query, $county) { $query->where('mps.county', $county); })
    ->when($region, function ($query, $region) { $query->where('mps.region', $region); })
    ->when($constituency, function ($query, $constituency) { $query->where('mps.constituency', $constituency); })
    ->get();

// ...

票数 1

Stack Overflow用户

发布于 2021-03-04 21:19:10

就这么做吧：

public function filterTopicResults(Request $request)
{

    $request->validate([
        'division_id' => 'required|integer',
    ]);

    $division_id = $request->get('division_id');
    $country = $request->get('country');
    $county = $request->get('county');
    $region = $request->get('region');
    $constituency = $request->get('constituency');

    if ($country) {
        $field = 'mps.country';
        $value = $country;
    } elseif ($county) {
        $field = 'mps.county';
        $value = $county;
    } elseif ($region) {
        $field = 'mps.region';
        $value = $region;
    } elseif ($constituency) {
        $field = 'mps.constituency';
        $value = $constituency;
    }
    $votes = DB::table('mp_votes')
        ->join('mps', 'mp_votes.mp_id', '=', 'mps.id')
        ->join('topics', 'mp_votes.division_id', '=', 'topics.id')
        ->select('topics.*', 'mp_votes.mp_aye_vote', 'mp_votes.mp_noe_vote', 'mp_votes.mp_no_vote_recorded')
        ->where('topics.id', $division_id)
        ->where($field, $value)
        ->get();

    $topic = Topic::withSum('userVotes', 'user_aye_count')
        ->withSum('userVotes', 'user_noe_count')
        ->where('id', $division_id)
        ->first();

    $mpVotes = [
        'total' => ($votes->where('mp_aye_vote', 1)->count() + $votes->where('mp_noe_vote', 1)->count()),
        'ayes' => $votes->where('mp_aye_vote', 1)->count(),
        'noes' => $votes->where('mp_noe_vote', 1)->count(),
        'no_vote' => $votes->where('mp_no_vote_recorded', 1)->count()
    ];



    return view('topics.topic', compact([
        'topic',
        'votes',
        'mpVotes',
    ]));
}

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/66482500

复制

相似问题

问重构雄辩的数据库查询
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问重构雄辩的数据库查询EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问重构雄辩的数据库查询
EN