10 months ago

Querying related models

Posted 10 months ago by nouveaucode

Hi Guys,

I am seeking your help with using the eloquent engine when querying models/relationships for an organisation that has very clearly defined workgroup structures, positions, users and appointments of which user is to fulfil a given position.

One of the key features of this relationship is that the appointment of a user to a position is defined over a given date range (i.e. User X is related to Position Y between validFrom and validTo).

I have the following 4 models, with the relevant foreign key & date columns shown in brackets:

HRWorkgroup, HRPosition (hr_workgroup_id, level), HRAppointment (hr_position_id, user_id, valid_from_date, valid_to_date), User

where the following relationships exist:

HRWorkgroup: have many HRPositions HRPositions: have many HRAppointments HRAppointments: have one User & have one HRPosition

For the HRWorkgroup model how would I return all of the User models that are appointed to its related positions for a given date?

class HRWorkgroup extends Model
    public function users($someDate) {
        return User::with('hr_appointments', function ($query) use ($someDate) {
            $query->where('valid_from', '<=', $someDate)->where('valid_to', '>=', $someDate);

how would I return a single User model that is appointed to the position within the workgroup (the position that has the lowest value in the HRPosition "level" column) for a given date?

Kind Regards Jordan

Please sign in or create an account to participate in this conversation.