HasMany relationship with a where condition for the pivot table

Hello, This may be very simple and I just do not know how to search for the answer but I cannot find a solution for the following scenario: I have a PhotoAlbum table and model with id and name as fields. I have a pivot table photo_album_user which has only 2 fields: photo_album_id and user_id. In the PhotoAlbum model I added the relationship:

public function user()
{
    return $this->belongsToMany(User::class);
}

What I need is to get all photo albums that are allocated to a certain user.

How can this be done? Considering they thought of the pivot table and the relationship they must have thought on a solution to take into account we will need to get entries by filtering using the pivot table.

I can do a left join between the 2 tables with a raw query but I would like to be as politically correct as possible.

$albums = DB::table('photo_album')
	->leftJoin('photo_album_user','photo_album.id','=','photo_album_user.photo_album_id')
	->where('photo_album_user.user_id','5')
	->get();

Thank you.

JabatoForever

2 years ago

Best Answer

Level 2

@gabriel27 as you said what you need is to get all photo albums that are allocated to a certain user. If u need to get all the photo album belonging to a certain User just do the inverse of the relation in the User model is the same as getting all the users belonging to a photo album

public function photoAlbums()
{
    return $this->belongsToMany(PhotoAlbum::class);
}

then get the users photos

User::find(5)->photoAlbums;

also if the relation is many to many be sure to follow Laravel convention and name your relations methods plurals. If i need to apply a condition on the pivot table

$id = 5;
PhotoAlbum::whereHas('users', function (Builder $query) use ($id){
        $query->where('photo_album_user.[some_column]',$id);
})->get();

But you don't need it in this case since u set the relation in the model and you don't have any additional info on the pivot table

Nakov

2 years ago

Level 73

Based on your tables it sounds like you have a many to many relationship.. which means one user can have many photo albums and one photo album can belong to many users.. so you just need the same method in your user model:

public function photoAlbums()
{
    return $this->belongsToMany(PhotoAlbum::class);
}

and then User::with('photoAlbums')->find(1); will give you the user with ID=1 and their photo albums.

https://laravel.com/docs/10.x/eloquent-relationships#many-to-many

acobrerosf

2 years ago

Level 5

The relationship should be BelongsToMany as it is a Many to Many relationship. Then you would just load the albums like:

$user = User::with('photoAlbums')->find(5);
$user->photoAlbums;

You can also add fields to the pivot table in case you need to and even filter results with specific values for those fields.

gabriel27

2 years ago

Level 1

Thank you to all 3 for your excellent answers. @jabatoforever after reading your answer I realized I need to stop working as I could not see this.

Again thank you very much guys.

1 like

Please or to participate in this conversation.