Add a query with a join
I am trying to get some options. I have laptop assignment table which contains all laptops assigned to employee.
We have two separate tables for laptop and employee.
Now when i am adding a laptop assignment, i only want laptop who meets the following conditions to be showin the dropdown
1. Laptop should not have entry in laptop assignment
2. if it exist in laptop assignment table, pick the latest entry and see if the returned date shall is non-null and date is less than todays date
i have tried adding query parameter, tried query builder, but for some reason nothing seems to work
Select::make('laptop_id')
->label('Laptop')
->options(
Laptop::all()
->pluck('asset_id', 'asset_id')
)
->searchable(), Need some help
->searchable(), Need some help
2 Replies
etc?
technically this is the query which will fetch me the result, how do i include it in options
SELECT laptops.*
FROM laptops
LEFT JOIN (
SELECT laptop_id, MAX(assigned_at) AS latest_assignment
FROM laptop_assignments
GROUP BY laptop_id
) AS latest_assignment ON laptops.asset_id = latest_assignment.asset_id
LEFT JOIN laptop_assignments AS la ON la.asset_id = laptops.asset_id AND la.assigned_at = latest_assignment.latest_assignment
WHERE la.asset_id IS NULL
OR (la.returned_at IS NOT NULL AND la.returned_at < CURDATE());
Group any help on this?
Just for reference for others, this was solved using below
Select::make('laptop_id')
->label('Laptop')
->options(
Laptop::query()
->leftJoin('laptop_assignments as la', function ($join) {
$join->on('laptops.asset_id', '=', 'la.laptop_id')
->whereRaw('la.assigned_at = (SELECT MAX(assigned_at) FROM laptop_assignments WHERE laptop_id = laptops.asset_id)');
})
->whereNull('la.laptop_id')
->orWhere(function ($query) {
$query->whereNotNull('la.returned_at')
->where('la.returned_at', '<', today());
})
->pluck('asset_id', 'asset_id')
->toArray()
)
->searchable(),
->searchable(),