laravel - Laravel whereBetween(query) Does not return the correct data. How can query based on latest report of each project?

Question

Hi,I have create a query using laravel whereBetween to filter the data based on the latest report of each project but it does not return the correct data. For example, when i choose the range between (-50 ~ 0) it will return the data with the variance match variance in all the report not based on the variance of latest report of each project. I have already query based on the latest report. May i know is there any wrong for my query request? Any help will be appreciate. Thank you....How can return the correct data? Vue js is being used as front end. How can make it works?
A customer can have many project. A project can have many report. I wanna to filter the project list based on the active project of variance column in latest report. The variance column(double type) consist the negative value.

Below is the function inside the project model

public function report()
    {
        return $this->hasMany(Report::class, 'project_id', 'id');
    }

   //retrieve the latest report
   public function latest()
    {
        return $this->hasOne(Report::class)->orderBy('year','DESC')->orderBy('month','DESC');
    }

Below is the function inside the Report model

 public function project()
    {
        return $this->hasOne(Project::class, 'id', 'project_id');
    }
  

Below is the function inside the project controller

public function index(Request $request)
    {
        $project = $this->_getData($request);<br/>
        return response()->json($project);<br/>
    }
 
private function _getData(Request $request)
{

    if ($request->query('customer', false)) {
        $project->where('customer_id', $request->query('customer'));
    }
    //it return the result that based on all variance column in all report of each project not based on the variance column of latest report.
    if ($request->query('range') != '') {
    $project->whereHas('latest',function ($query) use ($request){
        $range = explode('~',$request->query('range'));
        $query->whereBetween('variance', [$range[0],$range[1]);
    });
    }

    return $project->orderBy('project_name')->paginate(5);
}

标签： laravelvue.js