Laravel : How to Filter Query Builder base on user select dropdown

Design Image:

Question 1: I want to filter by choosing from dropdown year (Example 2018) and choosing dropdown category (example Amateur). System then will query base on user selection. I am not sure how to achieve this.

Question 2: My route point into ReportingController, and the controller will call function countryChart and Category chart. So if I capable of filtering the country section, how it will effect for category section.

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=""></option>
       @endforeach
  </select> 

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

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

Route:

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

ReportingController:

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


     }

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2JbiV5x
via IFTTT