How can I search a Laravel collection using WHERE LIKE (or equivalent)
I have an equipment bookings register/calendar system that I am currently working on using Laravel.
Equipment can be booked out directly to staff as needed or scheduled for future dates.
For example Laptop 1 might have three bookings but the laptop can only be booked out to one person at a time.
The system is up and running and the user can search for current/scheduled bookings.
But I am having trouble allowing the user to search for the staff name as I need to split the query up.
As you can see I am first getting all of the devices from lines 7-14.
Then I am appending all of the bookings from each device to the $bookings collection in a new key called “available bookings”.
I am wondering how I can search the available bookings key for the staff_name field as I have already returned the eloquent queries as collections.
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;
}
}
Here is an example of the $bookings object
[
{
"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": []
}
]
Example image of the system below.
I would like to search for staff name on all bookings or just the active booking (whichever is easier).
DB Structure below:
Models:
Devices:
<?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');
}
}
Bookings:
<?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');
}
}
Staff:
<?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'
];
}
Hopefully, this might help you out.
In your Device model class file add relation.
public function bookings() {
return $this->hasMany(Booking::class, 'device_id', 'device_id');
}
In your Booking model class file add relation.
public function staff() {
return $this->belongsTo(Staff::class, 'checked_out_to', 'staff_id');
}
Now in your controller file, you can filter the relation data using the below.
$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();
Add all other query conditions that you need on the Booking table before you call the get() function.
Note: I Hope I got the table relations correct. If not please correct it as per your mappings.
Edit: I forgot to add return statements to the model relation functions. I have updated the same in my answer.