How to order by the latest many to many relation

I am currently working on a application that offers courses - in this case it is called a sessions.

Session come packaged into sessions that can occur on one or more days There is a many to many relationship between a package and a session.

class Package extends Model
{

       public function sessions()
    {
        return $this->belongsToMany(Session::class, 'package_session', 'package_id', 'session_id');
    }

class Session extends Model
{
  
    public function packages()
    {
        return $this->belongsToMany(Package::class, 'package_session', 'session_id', 'package_id');
    }

The pivot table is package_session On the offerings page the pages are listed along with the related sessions. I am trying to sort the packages by order of the soonest sessions.start_date per package.

This almost gets me there but it returns the relation as a collection.

public function latestSession()
    {
        return $this->belongsToMany(Session::class, 'package_session')
            ->oldest('start_date');
    }

And this

return $this->belongsToMany(Session::class, 'package_session')
            ->oldest('start_date')->first();

throws an error: Call to undefined method App\Session::getRelated()

Is there a way to do this using eloquent?

sergiosrtd

3 years ago

Level 7

Try creating an pivot model for the package_session table and then apply the oldest("start_date") method https://laravel.com/docs/9.x/eloquent-relationships#defining-custom-intermediate-table-models

1 like

ctyler

3 years ago

Level 6

@sergiosrtd This was not working I was getting the same error.

piljac1

3 years ago

Level 28

Unfortunately, to my knowledge, Laravel doesn't yet support ofMany for many to many relationships (only one to many right now). It would have been perfect in your case because it would have retained normal relation usage (calling it as an attribute to get a value or as a method to get a builder). If you don't mind using a method instead of a relationship, it is really easy to do:

public function getLatestSession()
{
    return $this->sessions()->oldest('start_date')->first();
}

However, if you want to keep a relationship behavior, you would need to go custom and use a customized BelongsToMany instance with an overriden getResults method (idk if there would be other methods to override as well, haven't tested it myself).

1 like

ctyler

3 years ago

Level 6

I ended up solving this by sorting the the collection.

$query = Package::query();
            $query->whereHas('sessions', function (Builder $q) {
                $q->whereDate('start_date', '>', date('Y-m-d'));
            });
            $query->with('sessions');
            $query->where('active', true);
            $packages = $query->get();

            $sortedPackages = $packages->sortBy(function ($package, $key) {

                return $package['sessions'][0]->start_date;
            });

This will work because there is not a lot of packages available at any one time.

1 like

Please or to participate in this conversation.