Laravel 4.2 Sort by Eloquent Relationship Problems

I'm working with a datatable that I'm outputting from various relationships.

Most of the data comes from a meters table that has a Meter model, but some of it is pulled from other tables via relationships. For instance, I'm having an issue with sorting by the calibrations table.

The datatable has sortable columns that work just fine. The columns that sort based on other relationships have the joins in place so that they sort without any query errors.

All the sorting and joins work except for one, last_calibration_date.

There is no column called last_calibration_date. In fact, each meter could have multiple calibrations.

In the Meter model I grab the last_calibration_date from the calibrations table via the calibration_date column this way:

public function getLastCalibrationDateAttribute()
{
    if (isset($this->relations['calibrations']) && $this->relations['calibrations']->count())
        return $this->relations['calibrations']->sortBy('calibration_date', SORT_REGULAR, true)->first()->calibration_date->format('Y-m-d');
}

This works superbly when I'm not sorting by the last_calibration_date column, but returns a sql error if you try to sort by it without a join.

Here's my attempt at the join:

if ($sort == 'last_calibration_date')
{
    $query->join('calibrations', 'calibrations.meter_id', '=', 'meters.id');
    $sort = 'calibrations.calibration_date';
}

While this doesn't return an error it also doesn't return the actual last_calibration_date.

Just a little more info, the calibrations table is set up like so

calibrations
- id
- calibration_date
- next_calibration_date
- meter_id

So, as was said previously, any meter may have multiple calibrations.

Any ideas on how I could replicate my Meter method in my join? Or maybe another way of sorting by last_calibration_date?

Answers


Alrighty, well, I seem to have solved my problem without quite understanding why.

if ($sort == 'last_calibration_date')
{
     $query->select('meters.*');
     $query->join('calibrations as calibration', 'calibration.meter_id', '=', 'meters.id');
     $sort = 'calibration.calibration_date';
}

Adding that $query->select('meters.*'); has solved it. Again, not sure why. My understanding is that is selecting a particular table's columns, not a model's relationships.

Anyways, it's working now.


Need Your Help

Posting multiple radio button values to MySQL using "foreach"

php mysql

I have adjusted my code slightly but I am still having difficulty posting it to the table. Would someone please provide me with an example of the foreach array?

Servicestack - OrmLite query by date on SQLite32 database does not return any results

sqlite datetime servicestack ormlite-servicestack

I ran into something strange today by query-ing an 32 bits SQLite embedded database using ServiceStack ORMLite (Testing on Windows 2012 Server, .NET Framework 4.0).