Query all where list id has user id

问题:

I'm trying to get all the properties, in a watchlist(s) where the list has a user id.

The relationship is set up as follows.

Each watchlist is related to a user id. Each Property has a watchlist id.

I need all properties, in all the watchlists belonging to that user.

The watchlist gets the user_id automatically upon creation.

Here are my models

Watchlist

public function properties(){
      return $this->hasMany('App\WatchedProperties');
    }

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

WatchedProperties

public function watchlist(){
    return $this->belongsTo('App\Watchlist');
  }

Query Gets all books in every list disregarding user ids and list ids

$Watchlists = WatchedBooks::all();

Currently gets all books regardless of userid.

I need all books in all of the user's lists.

A user could have multiple lists List A List B

So something like All books from all lists where the list id is related to user id.

This is what the Watchlist DB looks like WatchlistDB

This is what the WatchedBooks DB looks like Books in watchlist

回答1:

Laravel has a beautiful solution for this: you can add a ->hasManyThrough relation to the user model. You can find more information about this type of relation in the Laravel documentation about Eloquent relationships.

The user model will look like this:

class User extends Model {

    [...]

    public function watchedBooks()
        return $this->hasManyThrough('App\WatchedBook', 'App\Watchlist');
    }

    [...]

}

Then you can get all the WatchedBooks, associated with the user, using

$user->watchedBooks;

or

$user->watchedBooks()->yourBuilderQueryHere()->get();

回答2:

whereHas allows you to query in relationships. More information on this can be found at https://laravel.com/docs/5.6/eloquent-relationships#querying-relationship-existence

$Watchlists = WatchedBooks::whereHas('watchlist', function($query) use ($user_id){
    $query->where('user_id', $user_id)
})->get();

The script above gets all WatchedBooks that are associated with a Watchlist that is owned by a user ($user_id).