laravel：如何基于用户选择下拉列表过滤查询生成器

5q4ezhmt 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(306)

设计图像：

**问题1:**我想通过从下拉列表中选择年份（例如2018年）和下拉列表中选择类别（例如业余爱好者）进行过滤。然后系统将根据用户选择进行查询。我不知道如何做到这一点。
**问题2:**我的路线指向reportingcontroller，控制器将调用函数countrychart和category chart。因此，如果我能够过滤国家部分，它将如何影响类别部分。

索引.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');

报告控制器：

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

     }

mysql php laravel laravel-5

来源：https://stackoverflow.com/questions/51258848/laravel-how-to-filter-query-builder-base-on-user-select-dropdown

1条答案

按热度按时间

mnemlml81#

你可以这样做

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;
}

在视图中

<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')

赞(0）回复(0）举报 2021-06-21

我来回答

laravel：如何基于用户选择下拉列表过滤查询生成器

1条答案

相关问题

热门标签

最新问答