CODEWITHSUNDEEP
phplaravel-4relationshipeager-loading
Gareth Daine
Gareth Daine

Reputation: 4186

How can I orderBy() a column in an eager loaded relationship in Laravel 4

How would I orderBy a relationships column in a query?

For example I have an $order variable that contains something like 'title.asc', it's then exploded and sent to the query in the model via the controller and I use $order[0] and $order[1] respectively but if the column is on the eager loaded relationships table, how could I use this?

public function scopeEvents($query, $order, $course, $supplier, $city, $venue, $eventStatus, $tutor)
{
    $date = date('Y-m-d');

    $query->where('active', 1)
        ->with('course', 'venue', 'supplier', 'eventType', 'eventStatus', 'tutor', 'delegates')
        ->where('start_date', '>=', $date)
        ->orderBy($order[0], $order[1]);

    if ( ! empty($course[0]))
    {
       $query = $query->whereIn('course_id', $course);
    }

    if ( ! empty($supplier[0]))
    {
        $query = $query->whereIn('supplier_id', $supplier);
    }

    if ( ! empty($venue[0]))
    {
        $query->whereIn('venue_id', $venue);
    }

    if ( ! empty($event_status[0]))
    {
        $query->whereIn('event_status_id', $eventStatus);
    }

    if ( ! empty($tutor[0]))
    {
        $query->whereIn('tutor_id', $tutor);
    }
}

At the moment I just get a column not found, which is to be expected.

Upvotes: 1

Views: 252

Answers (1)

Gareth Daine
Gareth Daine

Reputation: 4186

In order to reduce load times while also being able to order by related table columns, I had to combine eager loading with joins, and use the joins for ordering and the eager loading to display data in the view. Seems to work well.

public function scopeEvents($query, $order, $course, $supplier, $city, $venue, $eventStatus, $tutor)
{
    $date = date('Y-m-d');

    $query->where('active', 1)
        ->with('course', 'venue', 'supplier', 'eventType', 'eventStatus', 'tutor', 'delegates')
        ->leftJoin('courses', 'courses.id', '=', 'events.course_id')
        ->leftJoin('suppliers', 'suppliers.id', '=', 'events.supplier_id')
        ->leftJoin('venues', 'venues.id', '=', 'events.venue_id')
        ->leftJoin('event_types', 'event_types.id', '=', 'events.event_type_id')
        ->leftJoin('event_statuses', 'event_statuses.id', '=', 'events.event_status_id')
        ->leftJoin('tutors', 'tutors.id', '=', 'events.tutor_id')
        ->select('events.*', DB::raw("concat(tutors.first_name,' ',tutors.last_name) as tname"), 'event_statuses.status', 'event_types.type', 'venues.name as vname', 'suppliers.name as sname', 'courses.title', 'venues.city')
        ->where('start_date', '>=', $date)
        ->orderBy($order[0], $order[1]);

    if ( ! empty($course[0]))
    {
       $query = $query->whereIn('course_id', $course);
    }

    if ( ! empty($supplier[0]))
    {
        $query = $query->whereIn('supplier_id', $supplier);
    }

    if ( ! empty($venue[0]))
    {
        $query->whereIn('venue_id', $venue);
    }

    if ( ! empty($event_status[0]))
    {
        $query->whereIn('event_status_id', $eventStatus);
    }

    if ( ! empty($tutor[0]))
    {
        $query->whereIn('tutor_id', $tutor);
    }
}

Upvotes: 1

Related Questions