php - 拉维尔 : How to Filter Query Builder base on user select dropdown

Question

设计图片:

问题 1:我想通过从下拉年份(例如 2018 年)中选择并选择下拉类别(例如业余爱好者)来进行过滤。然后系统将根据用户选择进行查询。我不确定如何实现这一目标。

问题 2:我的路由指向 ReportingController， Controller 将调用函数 countryChart 和 Category chart。因此，如果我能够过滤国家部分，它将如何影响类别部分。

index.html:

 <select id="countryChart" class="form-control col-md-7 col-xs-12">
       <option value="all">All Time</option>
       @foreach($competition as $competition)
       <option value="{{$competition->competition_id}}">{{$competition->competition_year}}</option>
       @endforeach
  </select> 

      <!-- Display Chart -->
      <div id="chart-div" style = ""></div>

      @piechart('IMDB', 'chart-div')

路线:

 Route::get('reporting', 'ReportingController@index');

报告 Controller :

public function index() {
    countryChart();
    categoryChart()

      $competition = competition::orderBy('competition_year','ASC')->get();
      $category = category::orderBy('category_id','ASC')->get();

      return view('reporting.index',compact('competition','category'));
    }

public function countryChart(){
    $country = DB::table('participate_company')

    ->join('company', 'company.company_id', '=', 'participate_company.company_id')
    ->join('country', 'country.country_id', '=', 'company.country_id')
    ->join('competition', 'competition.competition_id', '=', 'participate_company.competition_id')
    ->join('category', 'category.category_id', '=', 'participate_company.category_id')
    ->select('company.country_id','country.country_name', DB::raw('COUNT(company.country_id) as total'))

    //competition id = 3 is year = 2018
    ->where('competition.competition_id','=','3')

    //category_id = 1 is category_name = Amature
    ->where('participate_company.category_id','=','1')
    ->groupBy('company.country_id')
    ->groupBy('country.country_name')
    ->get();

     // Chart logic display chart logic - not put because it will be long 
     // I am using http://lavacharts.com


     }

Answer 1

你可以这样实现

public function index(Request $request) {

      $competitionId = isset($request->competition_id) ? $request->competition_id : 3 ; //default 3
      $categoryId = isset($request->category_id) ? $request->category_id : 1 ; //default 1

      $countryChart = countryChart($competitionId, $categoryId);
      //categoryChart() similarly you can do it for this 

      //changes to plural for better readability because it has more than one record
      $competitions = competition::orderBy('competition_year','ASC')->get();
      $categories = category::orderBy('category_id','ASC')->get();

      return view('reporting.index',compact('competitions','categories', 'competitionId','categoryId', 'countryChart'));  //use countryChart in your view to draw 
}

国家图表功能

public function countryChart($competitionId, $categoryId){

    $query = DB::table('participate_company')
                ->join('company', 'company.company_id', '=', 'participate_company.company_id')
                ->join('country', 'country.country_id', '=', 'company.country_id')
                ->join('competition', 'competition.competition_id', '=', 'participate_company.competition_id')
                ->join('category', 'category.category_id', '=', 'participate_company.category_id')
                ->select('company.country_id','country.country_name', DB::raw('COUNT(company.country_id) as total'));

    if($competitionId != 'all'){  //
        $query->where('competition.competition_id','=', $competitionId);
    }

    if($categoryId != 'all'){
        $query->where('participate_company.category_id','=', $categoryId);
    }

    $countryChart = $query->groupBy('company.country_id')
                          ->groupBy('country.country_name')
                          ->get();

    return $countryChart;
}

在 View 中

<form method='get'>
    <select id="countryChart" name="competition_id" class="form-control col-md-7 col-xs-12" onchange="this.form.submit()">
       <option value="all">All Time</option>
       @foreach($competitions as $competition)
           <option {{ ($competitionId == $competition->competition_id) ? "selected":"") }} value="{{$competition->competition_id}}">
             {{$competition->competition_year}}
           </option>
       @endforeach
    </select> 
</form>

<!-- Display Chart -->
<div id="chart-div" style = ""></div>
{{$countryChart}} // you have this now use it in your chart
@piechart('IMDB', 'chart-div')