jabshire
1 year ago

Multiple Joins with Pivot Tables

Posted 1 year ago by jabshire

I have several tables:

  • books (collection of books)
  • authors (collection of authors)
  • publishers (collection of publishers)
  • author_book (pivot table)
  • book_publisher (pivot table)

When I request from "book" table, it gives me the book data as well as the authors and publishers connected to the book. This works as planned.

I have one more table where users store their instance of the book:

  • user_library (contains "book_id")

When requesting the user's library book, I crossJoin() it with the "book" table. This works as intended, returning all the data from 'user_library' and 'books' data. However, this request is not returning the "authors" and "publishers" as they would return when I query "books" table alone. How can I cause it to pull the data from the respective pivot tables?

$book = Library::where('book_id', $id)
        ->where('user_id', $user_id)
        ->select('*', 'user_library.title as user_title')
        ->crossJoin('books')
        ->take(1)
        ->get();

Please sign in or create an account to participate in this conversation.