将过滤器附加到 Laravel 中集合的 sql 结果

Question

因此，使用纯 PHP 和 MySQL，例如我可以过滤查询。$sql = 选择*来自用户；然后我有下拉菜单，国家省和地区。如果用户未选择任何内容，请单击获取报告显示所有用户。如果用户从 drop 中选择任何一个，例如。country 然后应该根据所选国家/地区缩小结果范围。用户还可以在所有下拉菜单上实现相同的功能。所以我正在尝试使用 Laravel 进行存档，我将向您展示我的代码。我已经选择并加入了表格，它带回了我的结果。如果设置了 dropdwon 值，我试图在结果中添加一个位置，但它没有像我期望的那样给我结果$results = DB::table('people')            ->leftJoin('contacts', 'people.id', '=', 'contacts.person_id')            ->leftJoin('provinces', 'contacts.province_id', '=', 'provinces.id')            ->leftJoin('nationalities', 'people.nationality_id', '=', 'nationalities.id')            ->leftJoin('districts', 'contacts.district_id', '=', 'districts.id')            ->select('people.*', 'contacts.*', 'provinces.name AS province_name',                'nationalities.name AS nationality', 'districts.name AS district_name');        if (request()->has('nationality_id')) {            $nationality_id = request('nationality_id');            $results->where('people.nationality', '=', $nationality_id)->get();        }        return view('reports.index', compact('results', 'nationalities', 'provinces'));我希望如果我选择下拉列表并喜欢省，它会使用所选省的选定值过滤集合。

Answer 1

您应该编辑此代码：

 if (request()->has('nationality_id')) {

            $nationality_id = request('nationality_id');

            $results =   $results->where('people.nationality', '=', $nationality_id)->get();

        }

要这样：

if ($nationality_id = request('nationality_id')) {

        $results = $results->where('people.nationality', '=', $nationality_id);

    }

$results = $results->get()

Answer 2

试试这个也许对你有帮助

if (request()->has('nationality_id')) {

   $nationality_id = request('nationality_id');

   $results = DB::table('people')

        ->leftJoin('contacts', 'people.id', '=', 'contacts.person_id')

        ->leftJoin('provinces', 'contacts.province_id', '=', 'provinces.id')

        ->leftJoin('nationalities', 'people.nationality_id', '=', 'nationalities.id')

        ->leftJoin('districts', 'contacts.district_id', '=', 'districts.id')

        ->select('people.*', 'contacts.*', 'provinces.name AS province_name',

            'nationalities.name AS nationality', 'districts.name AS district_name')

        ->where('people.nationality', '=', $nationality_id)->get();

}else{

  $results = DB::table('people')

        ->leftJoin('contacts', 'people.id', '=', 'contacts.person_id')

        ->leftJoin('provinces', 'contacts.province_id', '=', 'provinces.id')

        ->leftJoin('nationalities', 'people.nationality_id', '=', 'nationalities.id')

        ->leftJoin('districts', 'contacts.district_id', '=', 'districts.id')

        ->select('people.*', 'contacts.*', 'provinces.name AS province_name',

            'nationalities.name AS nationality', 'districts.name AS district_name')

        ->get();

}

Answer 3

你应该重写$results变量：

//...

if (request()->has('nationality_id')) {

    $nationality_id = request('nationality_id');

    $results = $results->where('people.nationality', '=', $nationality_id)->get();

}

//...