Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Find empty records on relation
I currently have two ORM related tables, employee and shift. I'm trying to create a query which allows me to find all employees who didn't have a shift record for a given date.
The shift needs to be referenced by a $start_date much like the query below
$shifts = \Model_Employee::find('all', array(
'related' => array('shifts'),
'where' => array(
array('shifts.start_time', '>', $start_date_dayBegin),
array('shifts.start_time', '<', $start_date_dayEnd),
I just can't fathom how to do query so the join shows just employees for a day with no shift record
What contains the variable $start_date_dayBegin ?
Maybe use query() instead of find(), an example here :
// include multiple conditions on the relation
Shift model contains
Employee model has
You need a subquery for that. Problem is that ORM currently doesn't have the functionality to generate embedded sub-querys. So you need something like this (from the top of my head):
$shifts = \Model_Employee::query()
->where(\DB::expr('(SELECT COUNT(*) FROM `shifts` `a` WHERE `a`.`employee_id` = `t0`.`id`)'), '=', 0)
'shifts.start_time', '>', $start_date_dayBegin
'shifts.start_time', '<', $start_date_dayEnd
You might have to move the where's on time to the subquery and remove the related.
Add a Comment
It looks like you're new here. If you want to get involved, click one of these buttons!
Apply for Membership
↳ Job Board
↳ Installation & Setup
↳ Tips and Tutorials
↳ Code share
In this Discussion