Love Fuel?    Donate

FuelPHP Forums

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
  • Hi, 

    What contains the variable $start_date_dayBegin ?
  • Maybe use query() instead of find(), an example here :

    // include multiple conditions on the relation
    'author', array('where' => array(array('active', '=', 1), array('status', '=', 'X'))));
  • 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)

        ->where('shifts.start_time', '>', $start_date_dayBegin)
        ->where('shifts.start_time', '<', $start_date_dayEnd)

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

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion