Code Bug Fix: Laravel – How to use my Query to represent 1 as Male and 2 as Female in Chart

Original Source Link

In my Larave-5.8, I am trying to generate bar chart using:

public function report()
{
    $userCompany = Auth::user()->company_id;
    $userId = Auth::user()->id;   

     $gender_datas = HrEmployee::selectRaw('count(gender_id) as count,gender_id')->where('company_id', $userCompany)->groupBy('gender_id')->get();
     $data=array();
     foreach ($gender_datas as $result) {
         $data[$result->gender_id]=(int)$result->count;
     }

    return view('report-default')
            ->with('data', $data);     
}

View

<div id="piechart_3d" style="width: 650px; height: 450px;"></div>


<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>

<script type="text/javascript">
  google.charts.load("current", {packages:["corechart"]});
  google.charts.setOnLoadCallback(drawChart);
  function drawChart() {

   var record={!! json_encode($data) !!};
   console.log(record);
   // Create our data table.
   var data = new google.visualization.DataTable();
    data.addColumn('string', 'Gender');
   data.addColumn('number', 'Total_Signup');
   for(var k in record){
        var v = record[k];

         data.addRow([k,v]);
      console.log(v);
      }
    var options = {
      title: 'Gender Classification',
      is3D: true,
    };
    var chart = new google.visualization.PieChart(document.getElementById('piechart_3d'));
    chart.draw(data, options);
  }
</script>    

From the table table, gender_id is an integer field. 1=Male and 2=Female.

In my Chart it is displaying 1 and 2.

I want it to display 1 as male and 2 as female.

How do I use my Query to represent 1 as Male and 2 as Female

How do I achieve this?

Thank you

Off the cuff option:

You could use a case statement in the selectRaw query.

selectRaw("count(gender_id) as count,CASE WHEN gender_id = 1 THEN 'Male' WHEN 
gender_id = 2 THEN 'Female' ELSE 'Unknown' END as gender")

This solution assumes that only 1 or 2 value exist for gender.

$userCompany = Auth::user()->company_id;

return HrEmployee::where('company_id', $userCompany)
        ->groupBy('gender_id')
        ->get([DB::raw('IF(gender_id = "1", "MALE", "FEMALE") as gender, count(gender_id) as total')])
        ->pluck('total', 'gender');
{
  "MALE": 13,
  "FEMALE": 7
}

Tagged :

Leave a Reply

Your email address will not be published. Required fields are marked *