Databases
StackOverflow2013
Postsdbo
POHow to retrieve full data of record by its foreign key in laravel?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to retrieve full data of record by its foreign key in laravel?
    primarykey
    data
    text
    <p>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". </p> <p>Here are my tables:</p> <p><strong>USERS</strong> </p> <pre><code>ID | NAME | </code></pre> <p><strong>BOOKS</strong></p> <pre><code>ID | PAGES | NUMBER_OF_CHAPTERS </code></pre> <p><strong>BOOKSHELF</strong></p> <pre><code>ID | USER_ID (foreign key to `users.id`) | </code></pre> <p><strong>BOOKSHELF_BOOKS</strong></p> <pre><code>ID | BOOKSHELF_ID (foreign key to `bookshelf.id`) | BOOKS_ID (foreign key to `books.id`) </code></pre> <p>In my Eloquent Models, a <code>bookshelf</code> hasMany books and <code>bookshelf_books</code> belongsTo a <code>bookshelf</code>and I have set those up in my models. </p> <p>What happens is users create a "bookshelf" and add books to it from the list of books. </p> <p>I'm at the point where I need to render the bookshelf with the user's books.</p> <p>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.</p> <p>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. <code>[{"book_id":1, "pages":364, "number_of_chapters":14},{"book_id":2, "pages":211, "number_of_chapters":9}]</code>. </p> <p>I've been scratching my head all day trying to figure out how to take this "join" one step further in Laravel/Eloquent ORM. </p> <p>Would love any help with this, thanks!!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USbcmcfc
    1. USuser2551866
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to retrieve full data of record by its foreign key in laravel?
      1. USChris M
      1. VTFavorite
    2. VO
      singulars
      1. POHow to retrieve full data of record by its foreign key in laravel?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to retrieve full data of record by its foreign key in laravel?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThat 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();`
      singulars
      1. POHow to retrieve full data of record by its foreign key in laravel?
      1. USAlbert Casadessús
    2. COyes 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
      singulars
      1. POHow to retrieve full data of record by its foreign key in laravel?
      1. USuser2551866
    3. COI can not try it but: `$books = Book::join('BOOKSHELF_BOOKS', 'BOOKS.ID', '=', 'BOOKSHELF_BOOKS.BOOKS_ID')->join(....//rest of the joins `
      singulars
      1. POHow to retrieve full data of record by its foreign key in laravel?
      1. USAlbert Casadessús
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload