Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Nested Many-to-many relationship
  • Hi there,

    I'm a beginner at PHP and MVC frameworks. I have 'users' and 'books' in a many-to-many relationship. A 'user' can 'subscribe' to 'books' and one 'book' may be 'subscribed' to by many 'users'.

    I have created the relationship by defining it in the separate models without creating an in-between model. Now I wish to use the values of the in-between model to relate them with 'highlights' and 'bookmarks'. So:

    1) A 'user' may have 'highlights' and 'bookmarks' in a certain 'book'.
    2) A 'book' may have the same 'highlights' and/or 'bookmarks' for different 'users'. (Even if the chance is small, it is still there.

    The database schema I'm aiming for is over here.

    What is the recommended course of action?

    Thanks for your time.
  • Then you'll need to split the many-to-many into two one-to-many's, and create a new model for the "through" table.

    Note that you can use both relation types at the same time, so you can use the many-to-many to get all books of a user (or all users subscribed to a book), and the one-to-many, to access the columns in the through table.

    Note also that ORM caches objects by key, so ever if you use both relations simultaneously, ORM will make sure you're looking at the same object in both resultsets.
  • I just need some more clarification, thanks for the help so far:

    1. I have a 'book' and 'user' model for now. 'books_users' table exists in the database only. Interaction with this table exists on an abstract level with its details defined in both the 'user' and 'book' model.

    2. From what I understand, if a many-to-many relationship exists with other attributes besides the foreign keys, a model has to be specifically defined.

    3. I now need the 'id' of the entries found in 'books_users' to connect the 'highlights' and 'bookmarks'. Which many-to-many did you mean when you advised to split it up?

    If I have the following models, would it be enough? :
    a. 'users'
    b. 'books'
    c. 'highlights'
    d. 'bookmarks'

    Thanks again.

  • Are highlights and bookmarks now different tables? From your first post I assumed those where fields in the through table.

    If these are tables, you have different relations:

    users has_many hightlights, hightlights belongs_to users
    books has_many hightlights, hightlights belongs_to nooks

    and the same for bookmarks.

    This is then totally unrelated to the many-to-many you have between books and users.
  • Yes, they are two different tables. I have posted a link to the database schema in the first post. From what I understand, they are related.

    The 'user' subscribes to a 'book' in which he then adds 'highlights' and/or 'bookmarks'. I think the schema image will make things clearer.
  • HarroHarro
    Accepted Answer
    Ah, sorry, missed that link.

    You related them to the trough table? Then you need to make that a model for your through table to, and you get the following relations:

    // the many-to-many relation can stay
    users has_many books, books has_many users

    // new one-to-many relations to the through table, requires a model for books_users
    users has_many books_users, books_users belongs_to users
    books has_many books_users, books_users belongs_to books

    // and the new relations
    books_users has_many bookmarks, bookmarks belongs_to books_users
    books_users has_many highlights, highlights belongs_to books_users

    SInce your through table (which is now a model) contains both the user and the book id, you can get your bookmarks and highlights directly from the two new relations:

    // find some book
    $book = Model_Books::query()->where($field,'=', $value)->get_one();

    // find it's bookmarks
    $bookmarks = Model_Bookmarks::query()
         ->related('booksusers')->where('booksusers.book_id', '=', $book->id)->get();

    Which will run faster then
    $bookmarks = Model_Bookmarks::query()
         ->related('booksusers')->related('booksusers.books')->where('booksusers.books.book_id', '=', $bookid)->get();

  • Thank you so much for the effort you put into answering questions on the forum.
  • You're welcome. Happy coding!

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion