How to retrieve full data of record by its foreign key in laravel?

16
votes

I've been trying to figure out the proper way to get all the data from a record via a foreign key. I have simple app where users can add books to their "bookshelf".

Here are my tables:

USERS 

ID   |   NAME   |

BOOKS

ID   |   PAGES   |   NUMBER_OF_CHAPTERS

BOOKSHELF

ID   |   USER_ID (foreign key to `users.id`)   |

BOOKSHELF_BOOKS

ID   |   BOOKSHELF_ID (foreign key to `bookshelf.id`)   | BOOKS_ID (foreign key to `books.id`)

In my Eloquent Models, a bookshelf hasMany books and bookshelf_books belongsTo a bookshelfand I have set those up in my models.

What happens is users create a "bookshelf" and add books to it from the list of books.

I'm at the point where I need to render the bookshelf with the user's books.

When I retrieve my bookshelf books with a call like Bookshelf::find($id)->books, the books that belong to that bookshelf return just fine..but only columns from the bookshelf table. So I get in return the bookshelf id, the user id, and the book id.

What I want to have returned is all the data of the book itself when i query for books in the bookshelf, not just it's id. E.G. [{"book_id":1, "pages":364, "number_of_chapters":14},{"book_id":2, "pages":211, "number_of_chapters":9}].

I've been scratching my head all day trying to figure out how to take this "join" one step further in Laravel/Eloquent ORM.

Would love any help with this, thanks!!

5
phpmysqllaravellaravel-4eloquent
That should work: DB::table('books') ->select('BOOKS.ID', 'BOOKS.PAGES','BOOKS.NUMBER_OF_CHAPTERS') ->join('BOOKSHELF_BOOKS', 'BOOKS.ID', '=', 'BOOKSHELF_BOOKS.BOOKS_ID') ->join('bookshelf', 'BOOKSHELF_BOOKS.BOOKSHELF_ID', '=', 'BOOKSHELF.ID') ->where('bookshelf.user_id', '=', $id_user) ->get(); – Albert Casadessús
yes this worked, nice job Albert. I can't help but think there must be a cleaner way to do it with laravel's ORM though, perhaps wishful thinking – user2551866
I can not try it but: $books = Book::join('BOOKSHELF_BOOKS', 'BOOKS.ID', '=', 'BOOKSHELF_BOOKS.BOOKS_ID')->join(....//rest of the joins – Albert Casadessús

5 Answers

21
votes

Just eager load the relationship

Change

Bookshelf::find($id)->books

to

Bookshelf::with('books')->find($id)->books
3
votes

What about just doing it manually, and building your own array.

  $bookshelf = Bookshelf::find($id)->books;
  $thebooks = array();

  foreach($bookshelf as $val){
  $book = Book::find($val->book_id);
  $thebooks[] = $book;
  }

Then just return $thebooks to the View. Which will be an array of book models.

2
votes

What you have should be correct.

$bookshelf = Bookshelf::find($id)->books;

To get your books, you would create a loop...

@foreach($bookshelf->books as $book)
   {{ $book->name }}
   {{ $book->title }}
@endforeach

To take this further, if you had an Author model which your books would belongTo(), you could even do something like..

@foreach($bookshelf->books as $book)
   {{ $book->name }}
   {{ $book->title }}
   {{ $book->author->name }}
@endforeach
-1
votes

Try this query

SELECT b.id, b.pages, b.number_of_chapters 
FROM bookshelf bs 
JOIN bookshelf_books bsb ON bsb.bookshelf_id = bs.id 
JOIN books b ON b.id = bsb.books_id 
WHERE bs.id = "Id of bookshelf"
-3
votes
SELECT * 
FROM BOOKSHELF_BOOKS 
JOIN BOOKSHELF ON BOOKSHELF_BOOKS.BOOKSHELF_ID = BOOKSHELF.ID 
JOIN USERS.ID = BOOKSHELF.USER_ID

Try this sql statement, it will work.