时间戳

首页 > 解决方案 > 将 where 子句分配给变量，以便可以根据条件使用 in

laravel - 将 where 子句分配给变量，以便可以根据条件使用 in

问题描述

我在下面放置了以下代码，我希望以 laravel 的方式编写。请帮我解决一下这个。

我已经这样写了，但它不起作用。

if (isset($request->search)) {
            $WHERE = ' WHERE project_name LIKE "%'.$request->search.'%" OR email LIKE "%'.$request->search.'%"';
        } else {
            $WHERE = NULL;
        }
        if(isset($request->offset) && isset($request->limit)){
            $sql = DB::select('SELECT id, project_name, email, phone, company, price FROM records "'.$WHERE.'" ORDER BY id LIMIT "'.$request->offset.'","'.$request->limit.'"');
        }else{
            $sql = DB::select('SELECT id, project_name, email, phone, company, price FROM records ORDER BY id');
        }

下面是我想用 Laravel 方式重写的实际 php 代码。请在这件事上给予我帮助。

if (isset($_GET['search'])) {
            $WHERE = ' WHERE vehicle_id LIKE "%'.$_GET['search'].'%" ';
        } else {
            $WHERE = NULL;
        }

        if(isset($_GET['offset']) && isset($_GET['limit'])){
            $sql = "SELECT vehicle_id, deviceImei, locationAddress, ward_no FROM gps_locations ".$WHERE." ORDER BY sl_no LIMIT ".$_GET['offset'].",".$_GET['limit']."";
        }else{
            $sql = "SELECT vehicle_id, deviceImei, locationAddress, ward_no FROM gps_locations ORDER BY sl_no";
        }

        $result = $conn->query($sql);
        if ($result->num_rows > 0) {
            // output data of each row
            while($row = $result->fetch_assoc()) {
                $a_json_row["Vehicle_Id"] = $row["vehicle_id"];
                $a_json_row["DeviceImei"] = $row["deviceImei"];
                $a_json_row["LocationAddress"] = $row["locationAddress"];
                $a_json_row["WardNo"] = $row["ward_no"];
                array_push($temp, $a_json_row);
                //echo "id: " . $row["user_id"]. " - Name: " . $row["user_name"]. "<br>";
            }
        }
        //return print_r($temp);
        $sql2 = "SELECT count(*) as total FROM gps_locations ".$WHERE."";
        $result2 = $conn->query($sql2);
        $data = $result2->fetch_assoc();

标签： laravel

解决方案

在 laravel 中，您可以使用 Query Builder 轻松完成此操作。就像您编写的 php 代码一样。

PHP

if (isset($request->search)) {
    $WHERE = ' WHERE project_name LIKE "%'.$request->search.'%" OR email LIKE "%'.$request->search.'%"';
} else {
    $WHERE = NULL;
}
if(isset($request->offset) && isset($request->limit)){
    $sql = DB::select('SELECT id, project_name, email, phone, company, price FROM records "'.$WHERE.'" ORDER BY id LIMIT "'.$request->offset.'","'.$request->limit.'"');
}else{
    $sql = DB::select('SELECT id, project_name, email, phone, company, price FROM records ORDER BY id');
}

在 Laravel 中

$qb = DB->table("records")->select("id", "project_name", "phone", "company", "price");

if (isset($request->search)) {
    $qb = $qb->where("project_name", "LIKE", "%".$request->search."%")
             ->orWhere("email", "LIKE", "%". $request->search."%");
}

 $result = $qb->orderBy("id", "ASC")->get(); // it would return laravel collection. so just dd() and see the structure.

//....
//....

foreach($result as $row){
    dd($row->id); // it will print the id.
}

在这里您可以找到有关如何开始在 laravel 中构建查询的指南https://laravel.com/docs/5.8/queries#selects

如果您想在多个查询构建器之间共享条件，请遵循此。

$whereData = [];
$orWhereData = [];

if (isset($request->search)) {
    $whereData = [
            ["project_name", "LIKE", "%".$request->search."%"]
    ];

    $orWhereData = [
            ["email", "LIKE", "%".$request->search."%"]
    ];
}

 $result = DB->table("records")
                ->select("id", "project_name", "phone", "company", "price")
                ->where($whereData)
                ->orWhere($orWhereData)
                ->orderBy("id", "ASC")
                ->get(); // it would return laravel collection. so just dd() and see the structure.

//....
//....

foreach($result as $row){
    dd($row->id); // it will print the id.
}


// for the gps_locations.

$total = DB::table("gps_locations")
                ->where($whereData)
                ->orWhere($orWhereData)
                ->count();

推荐阅读