I have this type of modal for search data from the database.

Image

I want to search the data if the user only types company name and CIN or *company name only or company state and company district user can choose any field. So I want to fetch the data only on selected fields.

Is there any simplest way to do this

I have coded multiple if-else statements.

My Code

 else if ($req->state && $req->district) {
            $data = tbl_company::query()
                ->where(
                    "state",
                    "LIKE",
                    "%{$req->state}%"
                )->where(
                    "district",
                    "LIKE",
                    "%{$req->district}%"
                )
                ->paginate(100);
        }

        // Filter using only state and city

        else if ($req->city && $req->district && $req->state == null) {
            $data = tbl_company::query()
                ->where(
                    "city",
                    "LIKE",
                    "%{$req->city}%"
                )->where(
                    "district",
                    "LIKE",
                    "%{$req->district}%"
                )
                ->paginate(100);
        }

        // company status only
        else if ($req->company_status && $req->city == null && $req->district == null && $req->state == null) {
            $data = tbl_company::query()
                ->where(
                    "company_status",
                    $req->company_status
                )
                ->paginate(100);
        }

CodePudding user response：

use Conditional Clauses

$data = tbl_company::query()->when($req->state && $req->district, function ($query, $req) {
        $query->where("state", "LIKE", "%{$req->state}%")
            ->where("district", "LIKE", "%{$req->district}%");
    })->when($req->city && $req->district && $req->state == null, function ($query, $req) {

        $query->where("city", "LIKE", "%{$req->city}%")
            ->where("district", "LIKE", "%{$req->district}%");
    })->paginate(100);

Updates use loop

$data = tbl_company::query()->where(function ($query)use($req){
    foreach ($req->only('state','district','city','company_status') as $filterField=>$filterFieldValue){
        if(!empty($filterFieldValue)&&is_array($filterFieldValue)){
            $query->wherein($filterField,$filterFieldValue);
        }elseif (!empty($filterFieldValue)){
            $query->where($filterField, "LIKE", "%{$filterFieldValue}%");
        }
    }
})->paginate(100);