Where clause on Pivot table of BelongsToMany relationship

Have you analysed the underlying query that's being executed?

Something like

dd(Auth::user()->team()->toSql());

Are you trying to get the current team for the user based on the user.current_team_id, then bypass the pivot table -- its a belongsTo relationship directly between User and Team

@premsaurav I get the following query:

"select * from `teams` inner join `team_user` on `teams`.`id` = `team_user`.`team_id` where `team_user`.`user_id` = ? and `team_user`.`team_id` = ?"

@tykus_ikus It's a belongsToMany relationship on both ends.

I already have a working teams() relationship.

But now I want to query only the currently selected team based on users.current_team_id, indeed.

Yeah, so you have a current_team_id field on your users tables, right? The pivot is superfluous if you are simply trying to get the user's current team because you have the foreign key on the users table already.

It doesn't look like your where clause is being reflected in the SQL you posted - there is nothing about current_team_id in there.

@jlmmns Did you spot the problem?

Notice team_user.team_id = ?", This isn't what you expect, I will leave it to you to understand why this happened. instead of where use whereRaw.

@tykus_ikus I don't see a problem with this approach, if its convenient, i can have as many relationship with little tweaks ???

@premsaurav I don't see the benefit of going through the pivot table if you already have the foreign key on your users table. Perhaps I am missing something in the schema that means the pivot is useful for some reason?

As far as the question marks in teh SQL above... are these not the placeholders for the query bindings?

@tykus_ikus

I don't see the benefit of going through the pivot table if you already have the foreign key on your users table. Perhaps I am missing something in the schema that means the pivot is useful for some reason?

I will try to explain with an example, its possible that a user can belong to many team and team can belong to many user, but a user can be the captain of only one team, a foreign key for team on user table. (just an example, can be done with extra pivot fields as well). Now if i want to find all the users in the team for which I am the captain, such a query could be useful. May be there are better examples, perhaps, products, categories, product's main catergory or so...

As far as the question marks in teh SQL above... are these not the placeholders for the query bindings?

Yes, they are.

Fair enough, your example is valid however let's deal with what we have in front of us:

1. that's not what the OP asked for
2. that's not how the relationship is named
3. that's not what the foreign key is describes

@premsaurav @tykus_ikus I want this additional team relationship eagerloaded, because a User's username is team-specific.

That's why I put the username field on the pivot table.
And because I eagerload the team relationship, I will still always have access to a User's username.

Any thoughts if this is actually a good way to tackle this?

One more thing:
The team relationship still returns an array.
Is there any way to only return the first result on that relationship?

public function team()
{
    return $this->belongsToMany('App\Team', 'team_user', 'user_id', 'team_id')
        ->whereRaw('team_user.team_id = ?', [ $this->current_team_id ])
        ->withPivot([ 'username', 'username_updated_at' ])
        ->withTimestamps()
        ->first();  //  <-- Something like this???
}

I would like to do this:

Auth::user()->team  // App\Team
Auth::user()->pivot->username

In short, I need a hasOne relationship, but with the pivot fields as well.
I don't know how to properly achieve this.

Any thoughts if this is actually a good way to tackle this?

Question to ask, later in time, would you be able to tell what this function does by looking at it, or if you show this to some other dev, would he be able to tell ? If yes, then you are probably good to go.

Having listened to the requirement, i would have rather used an accessor and table joins.

@premsaurav Thanks for your insight!

Could you please give a brief example of the accessor and table joins method?

Sure, let me work out a tested case ...

@premsaurav Much appreciated! Thank you.

Something like this...

//User.php

public function getTeamAttribute () {

       return Team::join('team_user','team_user.team_id','=', 'team.id')->where('team.id', $this->current_team_id)->first();
  }

// then can be accessed as following

Auth::user()->team;
Auth::user()->team->username;

@premsaurav That seems to work!

But in every view, this query runs around 30 times?!
Is there any way I can limit this, kind of like eagerloading?

I was afraid that would happen, you could use cache to avoid having it run multiple time. Can you show the view, are you calling it on different users ?

@premsaurav On a team overview page, it gets called on multiple listed users, yes.

But it also gets called multiple times in every view, because I have to use this relationship in a lot of places.
On the team overview page, that same query gets called 41 times.

For this reason, I was hoping I could use a relationship. :)

I don't really want to rely on caching for this, to be honest.

Hmmm, discard the previous one, will try to figure out using relations, if possible.

@premsaurav Many thanks for your effort!

The resulting usage was excellent though. :)

Second thought. You could use belongsToMany to eager load all the teams and use accessor to filter collection with id same as current_team_id. AFK so unable to show some code.

@premsaurav That's probably what I'm going to use.

On further inspection, your previous solution has one major downside:

Auth::user()->team->username   // always the first User's username

yes aware of that :( thats a shit code, am ashamed. discard, discard, discard :(

Hmmm...

    return $this->belongsToMany('App\Team')
        ->wherePivot('team_id', $this->current_team_id)
        ->withPivot([ 'username', 'username_updated_at' ])
        ->withTimestamps();

@tykus_ikus That returns an empty collection array, if I try it out.

Without the wherePivot, I do get the associated Team.

You;re sure that the User object has a current_team_id? It's hard to know without the schema/data...

Can you run it again with the wherePivot() and give us the query with bindings?

@tykus_ikus Sorry for the delay.

Apparently it IS working.
However, it still returns a Collection array, instead of the Team object itself.
Because of the belongsToMany relationship, I guess.

So I would have to create yet another helper method to select the first result?

Yesterday was talking something like this, plus there should not be any additional query load because of this,

    // Get the teams,  Can be eager loaded
    public function teams() {
        return $this->belongsToMany(Team::class)
            ->withPivot('username');
    }

// get current team
public function getCurrentTeamAttribute() {

        foreach ($this->teams as $team) {
            if($team->id === $this->current_team_id) {
                return $team;
            }
        }

        return null;
    }

@premsaurav Yes, I already went with that approach. :)

Although I made it look a bit more tidy, with a collection filter:

// Teams relationship (eager loaded)
public function teams()
{
    return $this->belongsToMany('App\Team', 'team_user', 'user_id', 'team_id')
        ->withPivot([ 'joined', 'username', 'username_updated_at' ])
        ->withTimestamps();
}

// Current Team
public function getTeamAttribute()
{
    return $this->teams->where('id', $this->current_team_id)->first();
}