FuelPHP Forums

cd0nc

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

Syntaxlb

Hi,

What contains the variable $start_date_dayBegin ?

Syntaxlb

Maybe use query() instead of find(), an example here : http://fuelphp.com/docs/packages/orm/crud.html

// include multiple conditions on the relation
Model_Article::query()
->related('author', array('where' => array(array('active', '=', 1), array('status', '=', 'X'))));

cd0nc

Shift model contains

id

employee_id

start_time

end_time

Employee model has

id

first_name

last_name

Harro

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()

    ->related('shifts')
->where(\DB::expr('(SELECT COUNT(*) FROM `shifts` `a` WHERE `a`.`employee_id` = `t0`.`id`)'), '=', 0)
    ->where('shifts.start_time', '>', $start_date_dayBegin)
    ->where('shifts.start_time', '<', $start_date_dayEnd)
    ->get();

You might have to move the where's on time to the subquery and remove the related.

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion