[asking] possible to support pivot table?

[rick20]

I often found cases where I need to retrieve the last record from many-to-many relationship. For example in a users-subscriptions-podcasts case, I need to retrieve the last subscribed podcasts for a given user. I would like to lazy load it via $user->lastSubscribedPodcast or from eager load User::with('lastSubscribedPodcast')->get()

I've tried some combinations from belongsTo(), hasOne(), hasOneThrough(), and your belongsToThrough() but with no luck.

Do you have any idea to support this case?

Btw, thank you for your cool package!

[rick20]

the closest approach is using the addSelect() in laravel-6 as explained in https://reinink.ca/articles/dynamic-relationships-in-laravel-using-subqueries

basically it's adding a virtual foreign key then load the related models based on the virtual foreign key. However, as Jonathan said, the lazy load won't work using this method.

it also works only on one-to-many relationship.

[staudenmeir]

It's not possible with this package.

You can use a HasOneThrough relationship if you define a pivot model (adjust accordingly):

class PodcastUser extends Pivot
{
    protected $table = 'podcast_user';
}

class User extends Model
{
    public function lastSubscribedPodcast()
    {
        return $this->hasOneThrough(
            Podcast::class, PodcastUser::class, 'user_id', 'id', 'id', 'podcast_id'
        )->latest('...');
    }
}

You can also use one of my other packages: https://github.com/staudenmeir/eloquent-has-many-deep#hasonedeep

class User extends Model
{
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

    public function lastSubscribedPodcast()
    {
        return $this->hasOneDeep(Podcast::class, ['podcast_user'])
            ->latest('...');
    }
}

[rick20]

hi @staudenmeir many thanks for the examples. I've tried your solutions, it works fine but I see a problem. It loads all intermediate rows when eager load the relation.

What I want to achieve is using a group by to retrieve the last subscribed podcast as explained in this article https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Perhaps something like this:

public function lastSubscribedPodcast()
{
    return $this->belongsToOne(Podcast::class, 'user_podcast');
}

where the performJoin() will create a group by subquery then join it with the pivot table to get the rest columns

this is just an idea after looking at the source code

[staudenmeir]

You can optimize the eager loading queries with one of my other packages (only works with the HasOneThrough relationship): https://github.com/staudenmeir/eloquent-eager-limit

[rick20]

You are awesome! this is what I'm looking for 😄 I'll give it a shot very soon... Thank you!