sql - Eloquent: Get pages based on user role -

i have user, role & page setup, many-to-many relationships , pivot tables setup in usual fashion (role_user, page_role), along eloquent methods attach model pivot tables.

my idea allow user have many roles, , page can accessed many roles.

however i'd return collection have users details , pages they're allowed access.

the closest have got is:

return user::find( auth::user()->id )->with('roles.pages')->first()->roles; 

now returns each role user has, , each page role can access. correct, have duplication on pages part.

how go getting list of pages user able access no duplication?

cheers

read answer on track: hasmanythrough one-to-many relationship

only setup need adjust query - join 2 pivot tables (and make sure represent real data, ie no rows referencing non-existing models):

// user model  // accessor can access relation: $user->pages; public function getpagesattribute() {     if ( ! array_key_exists('pages', $this->relations)) $this->loadpages();      return $this->getrelation('pages'); }  // here load pages , set collection relation protected function loadpages() {     $pages = page::join('page_role pr', 'pr.page_id', '=', 'pages.id')            ->join('role_user ru', 'ru.role_id', '=', 'pr.role_id')            ->where('ru.user_id', $this->id)            ->distinct()            ->get(['pages.*', 'user_id']);      $hasmany = new illuminate\database\eloquent\relations\hasmany(page::query(), $this, 'user_id', 'id');      $hasmany->matchmany(array($this), $pages, 'pages');      return $this; } 

one more thing: hardcoded tables , columns names sake of simplicity, in real life suggest rely on relationships , getters, like: $relation->gettable(), $relation->getforeignkey() etc.

---

now suggestion code:

return user::find( // 2. query same user      auth::user()->id  // 1. query user , id    )->with('roles.pages')      ->first() // 3. query user (or same, luckily..)      ->roles; 

1. use auth::id() instead of auth::user()->id (for laravel ver 4.1.25+) avoid redundant query
2. find() , first() methods execute query, returned user id = auth::user()->id , moment later fetch one, comes first() users table..
3. you don't need use user::whatever authenticated user, use auth::user() instead.

so code suggested solution this:

auth::user()->pages; // collection of page models unique entries