时间戳

首页 > 解决方案 > 如何使用 WHERE LIKE （或等效）搜索 Laravel 集合

php - 如何使用 WHERE LIKE （或等效）搜索 Laravel 集合

问题描述

我有一个设备预订登记/日历系统，我目前正在使用 Laravel。

设备可以根据需要直接预订给员工，也可以安排在未来的日期。

例如，笔记本电脑 1 可能有三个预订，但笔记本电脑一次只能预订给一个人。

系统启动并运行，用户可以搜索当前/预定的预订。

但是我无法让用户搜索员工姓名，因为我需要拆分查询。

如您所见，我首先从第 7-14 行获取所有设备。

然后我将每个设备的所有预订附加到$bookings一个名为“可用预订”的新密钥中的集合中。

我想知道如何搜索 staff_name 字段的可用预订键，因为我已经将雄辩的查询作为集合返回。

public function searchCurrentBookings(Request $request) {
      
        // Making sure the user entered a keyword.
        if ($request->has('query')) {

           //Get all devices
            $bookings = devices::where('device_status', '!=', 'decommisioned')
                ->where(function ($query) {
                $query->Where('devices.device_id', 'like', '%' . request('query') . '%')
                      ->orWhere('device_serial', 'like', '%' . request('query') . '%')
                      ->orWhere('model_name_fk', 'like', '%' . request('query') . '%')
                      ->orWhere('device_status', 'like', '%' . request('query') . '%'); 
                 })
             ->get();

                //Append all of the bookings for each device to collection
                foreach($bookings as $booking) {
                    $booking->available_bookings = bookings::leftJoin('staff','staff.staff_id','bookings.checked_out_to')
                        ->where('device_id', '=',$booking->device_id)
                        ->WhereIn('bookings.status', ['scheduled', 'checked_out'])
                }

                //How can I search the available bookings in this collection?
                $collection = $bookings->available_bookings->Where('staff_name', 'like', '%' . request('query') . '%')
            
            // If there are results return them, if none, return the error message.
            return $bookings->count() ? $bookings : $error;
        }
    }

这是$bookings对象的示例

[
  {
    "device_id": "Laptop 1",
    "device_serial": "63YNERQN",
    "model_name_fk": "Dell Latitude 3310",
    "device_status": "unavailable",
    "available_bookings": [
      {
        "id": 45,
        "device_id": "Laptop 1",
        "date_from": "1-May-2021",
        "date_to": "5-May-2021",
        "status": "checked_out",
        "checked_out_to": 1,
        "updated_at": "2021-05-27T11:52:13.000000Z",
        "staff_id": 1,
        "staff_name": "Jaden Bird"
      },
      {
        "id": 46,
        "device_id": "Laptop 1",
        "date_from": "6-May-2021",
        "date_to": "8-May-2021",
        "status": "scheduled",
        "checked_out_to": 2,
        "updated_at": "2021-05-27T11:52:13.000000Z",
        "staff_id": 2,
        "staff_name": "Lilo Berry"
      },
      {
        "id": 47,
        "device_id": "Laptop 1",
        "date_from": "17-May-2021",
        "date_to": "27-May-2021",
        "status": "scheduled",
        "checked_out_to": 4,
        "updated_at": "2021-05-27T11:52:13.000000Z",
        "staff_id": 4,
        "staff_name": "Barbora Forester"
      }
    ]
  },
  {
    "device_id": "Laptop 3",
    "device_serial": "PNX9N8R8",
    "model_name_fk": "Dell Latitude 7490",
    "device_status": "unavailable",
    "available_bookings": [
      {
        "id": 48,
        "device_id": "Laptop 3",
        "date_from": "5-May-2021",
        "date_to": "8-May-2021",
        "status": "checked_out",
        "checked_out_to": 3,
        "updated_at": "2021-05-27T11:52:13.000000Z",
        "staff_id": 3,
        "staff_name": "Kaiden Ojeda"
      }
    ]
  },
  {
    "device_id": "Laptop 4",
    "device_serial": "GTUEDDDH",
    "model_name_fk": "Dell Latitude 5300",
    "device_status": "available",
    "available_bookings": [
      {
        "id": 50,
        "device_id": "Laptop 4",
        "date_from": "30-May-2021",
        "date_to": "30-May-2021",
        "status": "scheduled",
        "checked_out_to": 6,
        "updated_at": "2021-05-27T11:52:13.000000Z",
        "staff_id": 6,
        "staff_name": "Luka Evans"
      }
    ]
  },
  {
    "device_id": "Projector",
    "device_serial": "Projector",
    "model_name_fk": "Epson EH-TW550",
    "device_status": "available",
    "available_bookings": []
  }
]

下面的系统示例图像。我想在所有预订中搜索员工姓名或仅在活动预订中搜索（以更容易者为准）。 预订日历示例

数据库结构如下： 数据库结构

楷模：

设备：

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Devices extends Model
{
    #use HasFactory;

    protected $table = 'devices';
    /**
     * @var string $primaryKey Name of the primary key
     */
    protected $primaryKey = 'device_id';
    /**
     * @var bool $incrementing The PK does not increment
     */
    public $incrementing = false;
    /**
     * @var string $keyType Primary key is a string (i.e. not an integer)
     */
    protected $keyType = 'string';
    /**
     * @var array $fillable The attributes that are mass assignable.
     */
    protected $fillable = [
        'device_id',
        'device_serial',
        'model_name_fk',
        'device_status'
    ];

    public function bookings() {
        $this->hasMany(Bookings::class, 'device_id', 'device_id');
      }
}

预订：

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Bookings extends Model
{
 #use HasFactory;

 protected $table = 'bookings';
 /**
  * @var string $primaryKey Name of the primary key
  */
 protected $primaryKey = 'id';
 /**
  * @var bool $incrementing The PK does not increment
  */
 public $incrementing = true;
 /**
  * @var string $keyType Primary key is a string (i.e. not an integer)
  */
 protected $keyType = 'integer';
 /**
  * @var array $fillable The attributes that are mass assignable.
  */
 protected $fillable = [
     'device_id',
     'date_from',
     'date_to',
     'status',
     'checked_out_to'
 ];
    public function staff() {
        $this->belongsTo(Staff::class, 'checked_out_to', 'staff_id');
      }
}

职员：

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Staff extends Model
{
    protected $table = 'staff';
    /**
     * @var string $primaryKey Name of the primary key
     */
    protected $primaryKey = 'staff_id';
    /**
     * @var bool $incrementing The PK does not increment
     */
    public $incrementing = true;
    /**
     * @var string $keyType Primary key is a string (i.e. not an integer)
     */
    protected $keyType = 'integer';
    /**
     * @var array $fillable The attributes that are mass assignable.
     */
    protected $fillable = [
        'staff_id',
        'staff_name'
    ];
}

标签： phplaravelsearchcollectionsbuilder

解决方案

希望这可以帮助你。

在您的设备模型类文件中添加关系。

public function bookings() {
  return $this->hasMany(Booking::class, 'device_id', 'device_id');
}

在您的 Booking 模型类文件中添加关系。

public function staff() {
  return $this->belongsTo(Staff::class, 'checked_out_to', 'staff_id');
}

现在在您的控制器文件中，您可以使用以下内容过滤关系数据。

$search = $request->search_name;
$bookings = Device::with('bookings', 'bookings.staff')->whereHas('bookings.staff', function($q) use($search) {
                return $q->where('staff_name', 'like', '%' . $search .'%');
        })->get();

get()在调用函数之前，在 Booking 表上添加您需要的所有其他查询条件。

注意：我希望我的表格关系正确。如果不是，请根据您的映射进行更正。

编辑：我忘记在模型关系函数中添加返回语句。我在我的答案中更新了相同的内容。

推荐阅读