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

Question

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!!

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, Jul 26 '13 at 06:29
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, Jul 26 '13 at 06:44
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, Jul 26 '13 at 06:47

score 21 · Answer 1 · answered Jul 29 '13 at 07:41

21

Just eager load the relationship

Change

Bookshelf::find($id)->books

to

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

answered Jul 29 '13 at 07:41

Laurence

58,936
21
171
212

2

Hi, is there anything I need to set up in my model relations for this? aside from the normal relationship? – Gokigooooks Dec 01 '15 at 00:05

score 3 · Answer 2 · answered Aug 04 '13 at 18:27

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.

score 2 · Answer 3 · answered Jul 26 '13 at 12:18

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

score -1 · Answer 4 · edited Aug 21 '17 at 07:42

-1

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"

edited Aug 21 '17 at 07:42

simhumileco

31,877
16
137
115

answered Jul 26 '13 at 06:08

Raj Jagani

738
1
6
21

score -3 · Answer 5 · edited Aug 21 '17 at 08:45

-3

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.

edited Aug 21 '17 at 08:45

Paolo Forgia

6,572
8
46
58

answered Aug 04 '13 at 12:03

Vishnu Kant Maurya

15
7

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

5 Answers5

Linked