Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

MarioMacedo's avatar

Eager load relation with parameters

In my Machine model I have the following relations


public function readings()
{
    return $this->hasMany(Reading::class)->orderByDesc('date');
}


public function nearestReadingFrom($date)
{
    $date = Carbon::createFromFormat('d-m-Y', $date)->format('Y-m-d');

    return $this->hasMany(Reading::class)
        ->where('date', '>=', $this->first_reading_date)
        ->orderBy(DB::raw('ABS(DATEDIFF(date, "' . $date . '"))'))
        ->limit(1);
}

If I want to eager load the readings relation I can do the following and it works fine


Machine::select('location_id', 'id', 'code_id', 'first_reading_date', 'first_reading_in', 'first_reading_out', 'code_id')->with([
    'readings' => function ($query) {
        $query->select('machine_id', 'date', 'in', 'out');
    }
]);

However I don't know how to eager load the relation nearestReadingFrom($date) because of the parameter date.

0 likes
6 replies
tykus's avatar

Relations do not take parameters, you would need to query an existing relation:

// Machine.php
public function reading()
{
    return $this->hasOne(Reading::class)->orderBy('date');
}

This relation is rather meaningless on its own (it will return the first reading for the given machine); but the real power comes from querying the relation, where we can determine which reading should be first:

$date = Carbon::createFromFormat('d-m-Y', $date)->format('Y-m-d');

Machine::with(['reading' => function ($query) use ($date) {
    $query->where('date', '>=', $date);
}])->find($machineId);

Now you will have a Machine instance which has a reading property containing the nearest reading from $date

2 likes
MarioMacedo's avatar

I'm understanding your suggestion but there is only one catch. For the purposes of the platform I'm developing the nearest reading from a certain date must be the one which module of the difference between the dates is smaller.

For example, if I have the following dates on the database (Y-m-d):

2018-01-5

2018-01-10

And my reference date is 2018-01-02 the nearest date is 2018-01-05

if it is 2018-01-05 the nearest date is 2018-01-05

and if it is 2018-01-07 the nearest date is 2018-01-05

tykus's avatar

Ok, I didn't understand that.

I think with the following adaptation, it should still work:

public function reading()
{
    return $this->hasOne(Reading::class)->where('date', '>', $this->first_reading_date);
}

$date = Carbon::createFromFormat('d-m-Y', $date)->format('Y-m-d');

Machine::with(['reading' => function ($query) use ($date) {
    $query->orderByRaw("ABS(DATEDIFF(date, '{$date}'))");
}])->find($machineId);
tiagotavares's avatar

Hi,

To my knowledge is not possible to pass a parameter to a relation, as referenced on the other comment.

I think the logic you are looking for is loading the relationship (reading) and query the proper one based on the date you have available:

Machine model

public function firstReading()
{
    return $this->hasOne(Reading::class)
        ->where('date', '>', $this->first_reading_date);
}

Reading model

public function scopeNearestTo($query, $date) {
    return $query->orderByRaw("ABS(DATEDIFF(date, '{$date}'))");
}

Controller/Usage

$date = Carbon::createFromFormat('d-m-Y', $date)->format('Y-m-d');

Machine::select('location_id', 'id', 'code_id', 'first_reading_date', 'first_reading_in', 'first_reading_out', 'code_id')->with([
    'firstReading' => function ($query) use($date) {
        $query->nearestTo($date);
    }
]);

Let me know if there are any typos. And if this is what you expect.

**Edit -> Quite similar to the previous answer.

MarioMacedo's avatar
MarioMacedo
OP
7 years ago
Best Answer
Level 13

Just to let all you know what was my solution, I just saved in the session the parameter I wanted to pass to the relation before making the query, so when eager loading, the relations are able to get the variable from the session.

Not the most elegant solution, but it has to be this way in order to satisfy this project requirements and constraints.

Please or to participate in this conversation.