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

codestorm's avatar

Sort Relationship

Hello folks, i need your help again.

I want to sort a relationship. User can book courses and i want to sort the booked courses against the weekday of the course

here the facts

UserController

public function show($id)
    {
        $user = User::with(array('bookings.date' => function($query) {$query->orderBy('weekday');}), 'bookings', 'bookings.course', 'bookings.date', 'payments')->find($id);

        $dates = Dates::with('courses')->orderBy('weekday')->orderBy('start')->get();

        foreach($dates as $item)
        {
            $courselist[$item->id] = $item->courses->name . " (" . showWeekday($item->weekday) . " " . Carbon::parse($item->start)->format('H:i') . " - " . Carbon::parse($item->stop)->format('H:i') . "Uhr)";
        }

        return View::make('user.show', compact('user', 'courselist'));
    }

User Model

<?php

use Illuminate\Auth\UserTrait;
use Illuminate\Auth\UserInterface;
use Illuminate\Auth\Reminders\RemindableTrait;
use Illuminate\Auth\Reminders\RemindableInterface;

class User extends Eloquent implements UserInterface, RemindableInterface {

    use UserTrait, RemindableTrait;

    protected $table = 'users';

    protected $hidden = array('password', 'remember_token');

    protected $guarded = ['id', 'created_at', 'updated_at'];

    //Relationships User -> Roles
    public function roles() {

        return $this->belongsToMany('Role')->withTimestamps();
    }

    //User has Roles
    public function hasRole($name) {

        foreach($this->roles as $role) {

            if ($role->name == $name) return true;
        }

        return false;
    }

    //Relationships Bookings
    public function bookings()
    {
        return $this->hasMany('Booking');
    }

    //Relationship Payments
    public function payments()
    {
        return $this->hasMany('Payment');
    }

    //Validation Rules
    public static $rules = [
        'firstname' =>  'required',
        'lastname'  =>  'required',
        'class'     =>  array('required', 'regex:/[0-9]{1}[a-z]{1}/', 'max:2')
    ];


    //Custom Attribute Names
    public static $names = [
        'firstname' =>  'Vorname',
        'lastname'  =>  'Nachname',
        'class'     =>  'Klasse'
    ];

}

Booking Model

<?php

class Booking extends \Eloquent {

    protected $protected = ['id', 'created_at', 'updated_at'];

    //Relationship User
    public function user()
    {
        return $this->belongsTo('User');
    }

    //Relationship Course
    public function course()
    {
        return $this->belongsTo('Course', 'course_id');
    }

    //Relationship Course Dates
    public function date()
    {
        return $this->belongsTo('Dates', 'course_dates_id')->orderBy('weekday');
    }

    //Relationship Course
    public function season()
    {
        return $this->belongsTo('Season', 'season_id');
    }

    //Validation Rules
    public static $rules = [
        'course'    =>  'required',
        'start'     =>  'date_format:d.m.Y',
        'stop'      =>  'date_format:d.m.Y'
    ];


    //Custom Attribute Names
    public static $names = [
        'course'    =>  'Kurs',
        'start'     =>  'Von',
        'stop'      =>  'Bis'
    ];

    //Change Start Date Format GET
    public function getStartAttribute($value)
    {
        return Carbon::parse($value)->format('d.m.Y');
    }

    //Change Start Day Format SET
    public function setStartAttribute($value)
    {
        $this->attributes['start'] = Carbon::parse($value)->format('Y-m-d');
    }

    //Change Stop Date Format GET
    public function getStopAttribute($value)
    {
        return Carbon::parse($value)->format('d.m.Y');
    }

    //Change Stop Day Format SET
    public function setStopAttribute($value)
    {
        $this->attributes['stop'] = Carbon::parse($value)->format('Y-m-d');
    }
}

Course Model

<?php

class Course extends \Eloquent {

    protected $guarded = ['id', 'created_at', 'updated_at'];

    //Relationship Teacher
    public function teacher()
    {
        return $this->belongsTo('Teacher');
    }

    //Relationship User
    public function bookings()
    {
        return $this->hasMany('Booking');
    }

    //Relationship Dates
    public function dates()
    {
        return $this->hasMany('Dates');
    }

    //Accessor for Dates
    public function getWeekdayAttribute()
    {
        //???
    }

    //Validation Rules
    public static $rules = [
        'name'          =>  'required',
        'teacher_id'    =>  'required',
        'description'   =>  'required',
        'room'          =>  'required',
        'price'         =>  array('required', 'regex:/^\d*(\,\d{2})?$/'),
        'maxuser'       =>  'required|numeric',
        'target'        =>  'required'
    ];


    //Custom Attribute Names
    public static $names = [
        'name'          =>  'Kursname',
        'teacher_id'    =>  'Kursleiter',
        'description'   =>  'Beschreibung',
        'room'          =>  'Raum/Ort',
        'price'         =>  'Preis',
        'maxuser'       =>  'Max. Anzahl Teilnehmer',
        'target'        =>  'Zielgruppe'
    ];


    //Change Price Format Set
    public function setPriceAttribute($price)
    {
        $this->attributes['price'] = str_replace(',', '.', $price);
    }

    //Unserialize Target Get
    public function getTargetAttribute($target)
    {
        return unserialize($target);
    }

    //Serialize Target Set
    public function setTargetAttribute($target)
    {
        $this->attributes['target'] = serialize($target);
    }
}

How can I sort the bookings according to the weekdays of course times?

Thanks for your help

0 likes
3 replies
bobbybouwmann's avatar

I ended up creating a join before I found out that the Eloquence package will do what you want! But I can show you both ways of course ;)

Package way: https://github.com/jarektkaczyk/eloquence and this https://laracasts.com/discuss/channels/general-discussion/sorting-tables-with-relations

Join: (I will show you an example to get you started, I think you can handle the rest then)

// App\Http\Controllers\HoursController.php
public function index(Request $request)
{
    return Hour::with('user')->sortable($request)->get();
}


// App\Hour.php
public function scopeSortable($query, Request $request)
{
    if ($request->get('sortBy') && $request->get('direction'))
        return $query->join('users', 'users.id', '=', 'hours.user_id')
        ->orderBy($request->get('sortBy'), $request->get('direction'));

    return $query;
}

As you can see I made a query scope for the model and then pass in the request so I can check on which way I need to sort. Sometimes it's hard to sort on a table that both have the same column. I fixed it by using hours.created_at or users.created_at as sortBy parameter. The direction variable should hold asc or desc of course. If you have any more questions, let me know ;)

1 like

Please or to participate in this conversation.