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 : 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'))));
  • Shift model contains
    id
    employee_id
    start_time
    end_time

    Employee model has
    id
    first_name
    last_name
  • 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.

Howdy, Stranger!

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

In this Discussion